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);
}
