public PaginatedList <PersonnelWithDetail> GetAllPaginatedWithDetailBySearchFilter(PersonnelSearchFilter searchFilter) { PaginatedList <PersonnelWithDetail> resultList = new PaginatedList <PersonnelWithDetail>(new List <PersonnelWithDetail>(), 0, searchFilter.CurrentPage, searchFilter.PageSize, searchFilter.SortOn, searchFilter.SortDirection); using (AppDBContext dbContext = new AppDBContext(_config)) { var query = from p in dbContext.Personnel from h in dbContext.Hospital.Where(x => x.Id == p.HospitalId).DefaultIfEmpty() where h.IsDeleted == false select new PersonnelWithDetail() { Id = p.Id, Name = p.Name, LastName = p.LastName, TC = p.TC, HospitalId = p.HospitalId, Address = p.Address, Phone = p.Phone, UserName = p.UserName, Hospital_Name = h == null ? String.Empty : h.Name }; // filtering if (!string.IsNullOrEmpty(searchFilter.Filter_Name)) { query = query.Where(r => r.Name.Contains(searchFilter.Filter_Name)); } if (!string.IsNullOrEmpty(searchFilter.Filter_LastName)) { query = query.Where(r => r.LastName.Contains(searchFilter.Filter_LastName)); } if (searchFilter.Filter_HospitalId.HasValue) { query = query.Where(r => r.HospitalId == searchFilter.Filter_HospitalId.Value); } // asnotracking query = query.AsNoTracking(); //total count var totalCount = query.Count(); //sorting if (!string.IsNullOrEmpty(searchFilter.SortOn)) { // using System.Linq.Dynamic.Core; nuget paketi ve namespace eklenmelidir, dynamic order by yapmak icindir query = query.OrderBy(searchFilter.SortOn + " " + searchFilter.SortDirection.ToUpper()); } else { // deefault sıralama vermek gerekiyor yoksa skip metodu hata veriyor ef 6'da -- 28.10.2019 15:40 // https://stackoverflow.com/questions/3437178/the-method-skip-is-only-supported-for-sorted-input-in-linq-to-entities query = query.OrderBy(r => r.Id); } //paging query = query.Skip((searchFilter.CurrentPage - 1) * searchFilter.PageSize).Take(searchFilter.PageSize); resultList = new PaginatedList <PersonnelWithDetail>( query.ToList(), totalCount, searchFilter.CurrentPage, searchFilter.PageSize, searchFilter.SortOn, searchFilter.SortDirection ); } return(resultList); }