This example is only called “Advanced” because it addresses multiple tables for paging. This will often be the case, but you can view a simple example that addresses one table here: Paging with Entity Framework (Simple)
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
/// <summary>
/// Complicated Paged results from multiple tables in EF Query
/// Yasgar Technology Group, Inc. - www.ytgi.com
/// </summary>
/// <param name="PageNumber"></param>
/// <param name="PageSize"></param>
/// <param name="SearchTerm"></param>
/// <param name="SearchFilter"></param>
/// <param name="SortExpression"></param>
/// <param name="SortOrder"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
public PagedResult<Models.SalesProduct> GetSalesDetailsByEF(int PageNumber,
int PageSize,
string SearchTerm,
string SearchFilter,
string SortExpression,
string SortOrder,
bool ActiveOnly)
{
try
{
int _skipRows = (PageNumber - 1) * PageSize; // if this is not the first call, need move forward
int _totalCount = 0; // placeholder for the total amount of records
using (EFModels.AWEntities _efEntities = new EFModels.AWEntities())
{
// Using var because this is returning an anonymous type
var _entityrows = (from sheader in _efEntities.SalesOrderHeaders
join sdetail in _efEntities.SalesOrderDetails on sheader.SalesOrderID equals sdetail.SalesOrderID
join product in _efEntities.Products on sdetail.ProductID equals product.ProductID
select new Models.SalesProduct()
{
OrderQuantity = sdetail.OrderQty,
ProductId = sdetail.ProductID,
ProductName = product.Name,
ProductNumber = product.ProductNumber,
ShipDate = sheader.ShipDate ?? DateTime.MinValue,
UnitPrice = sdetail.UnitPrice,
OnlineOrderFlag = sheader.OnlineOrderFlag
});
if (ActiveOnly)
{
// Showing how to use ActiveOnly without a boolean flag as an example
_entityrows = _entityrows.Where(er => er.ShipDate.Year > 2013);
}
if (!string.IsNullOrWhiteSpace(SearchFilter))
{
// This can be customized for each implementation
bool _onlineOrder;
bool.TryParse(SearchFilter, out _onlineOrder);
_entityrows = _entityrows.Where(f => f.OnlineOrderFlag == _onlineOrder);
}
if (!string.IsNullOrWhiteSpace(SearchTerm))
{
// This can be customized for each implementation
_entityrows = _entityrows.Where(f => f.ProductName.Contains(SearchTerm.Trim()));
}
// Getting count will execute a SELECT COUNT(*)
// Like to do this before adding sort criteria
_totalCount = _entityrows.Count();
if (!string.IsNullOrWhiteSpace(SortExpression))
{
bool IsSortDESC = false;
if (SortOrder.ToLower() == "asc") { IsSortDESC = true; }
_entityrows = _entityrows.OrderBy<Models.SalesProduct>(SortExpression, IsSortDESC);
}
_entityrows = _entityrows.Skip(_skipRows).Take(PageSize);
return new PagedResult<Models.SalesProduct>(_entityrows.ToList(), _totalCount);
}
}
catch (Exception)
{
throw;
}
}
If you’re viewing this as part of the Paging series, continue with that series here: Business Objects for Paging
