While writing codes in MVC we use different way to interact with the database.

Then we can use LINQ for doing it in the database it will make our code easy to read and make the manipulation easy and faster.

First we will create a DataContext by right clicking  to the project and then selecting LInq to Sql class

After doing this a context class being made which is used for interaction with the database.

Then we will write the operations using this data context class that has extension .dbml

 DataClasses1DataContext DataContextObj = new DataClasses1DataContext();

        public List<EmployeeModel> GetPlayers(int? page, int? limit, string sortBy, string direction, string searchString, out int total)
        {
            total = (from u in DataContextObj.EmployeeDetails select u).Count();
            var records = (from p in DataContextObj.EmployeeDetails
                           select new EmployeeModel
                           {
                               EmpId = p.EmpId,
                               FirstName = p.EmpFirstName,
                               MiddleName = p.EmpMiddleName,
                               LastName = p.EmpLastName
                           }).AsQueryable();

            if (!string.IsNullOrWhiteSpace(searchString))
            {
                records = records.Where(p => p.FirstName.Contains(searchString) || p.LastName.Contains(searchString));
            }

            if (!string.IsNullOrEmpty(sortBy) && !string.IsNullOrEmpty(direction))
            {
                if (direction.Trim().ToLower() == "asc")
                {
                    records = SortHelper.OrderBy(records, sortBy);
                }
                else
                {
                    records = SortHelper.OrderByDescending(records, sortBy);
                }
            }

            if (page.HasValue && limit.HasValue)
            {
                int start = (page.Value - 1) * limit.Value;
                records = records.Skip(start).Take(limit.Value);
            }

            return records.ToList();
        }