Whenever you have a need to show a paged list of your items, it is a must that this is done in your repository in Entity Framework so that you’re not passing around more data than is necessary. There are too many examples of paging on the web which show paging at the UI level, especially terrible ones are showing doing a LINQ to SQL query with ToList() after it. Horrible!
DON’T EVER DO THIS!
var _result = dbContext.Customers.ToList().Skip(page).Take(pageSize);
The following example is only called “Simple” because it addresses one table for paging. This will often be the case, but you can view a more complicated query here: Paging with Entity Framework (Advanced)
The example below accepts all the common parameters and handles assembling a Linq query. It DOES NOT execute the query until the entire statement is built. There is a necessary evil call to get the count, once the WHERE clause criteria is completed. You’ll notice that this call is done before adding the OrderBy so as not to burden the Count query with sorting.
The OrderBy statement is not standard in Linq. It uses an extension method that you can take a look at here: OrderBy Extension for Linq Queries
You’ll also notice that this method returns a PagedResult object. This is just a custom DTO that brings back a strongly typed list and a total count. You can see the code for this object here. PagedResult for Paging
This LINQ code was pulled directly from the LookupsRep class in the YTG MVC Lookups and Paging Demo Download.
/// <summary>
/// Simple Paged results from multiple tables in EF Query
/// Yasgar Technology Group, Inc. - www.ytgi.com
/// </summary>
/// <param name="Page"></param>
/// <param name="PageSize"></param>
/// <param name="SearchTerm"></param>
/// <param name="SearchFilter"></param>
/// <param name="SortColumn"></param>
/// <param name="SortOrder"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
public async Task<PagedResult<LuCategories>> GetLuCategoriesByEFAsync(int Page,
int PageSize,
string SearchTerm,
string SearchFilter,
string SortColumn,
string SortOrder,
bool ActiveOnly)
{
try
{
int _skipRows = (Page - 1) * PageSize; // if this is not the first call, need move forward
int _totalCount = 0; // placeholder for the total amount of records
// Using var because this is returning an anonymous type
var _entityrows = (from item in LuContext.LuCategories.Include(a => a.LuItems)
select item);
if (ActiveOnly)
{
// Showing how to use ActiveOnly without a boolean flag as an example
_entityrows = _entityrows.Where(er => er.IsActive == true);
}
if (!string.IsNullOrWhiteSpace(SearchFilter))
{
// This can be customized for each implementation
_entityrows = _entityrows.Where(f => f.ShortName == SearchFilter);
}
if (!string.IsNullOrWhiteSpace(SearchTerm))
{
// This can be customized for each implementation
_entityrows = _entityrows.Where(f => f.Name.Contains(SearchTerm.Trim()));
}
// Getting count will execute a SELECT COUNT(*)
// Like to do this before adding sort criteria
_totalCount = _entityrows.Count();
if (!string.IsNullOrWhiteSpace(SortColumn))
{
bool IsSortDESC = false;
if (SortOrder.ToLower() == "desc") { IsSortDESC = true; }
_entityrows = _entityrows.OrderBy(SortColumn, IsSortDESC);
}
_entityrows = _entityrows.Skip(_skipRows).Take(PageSize);
return new PagedResult<Models.LuCategories>(await _entityrows.ToListAsync(), _totalCount);
}
catch (Exception)
{
throw;
}
}
If you’re viewing this as part of the Paging series, continue with that series here: Business Objects for Paging
