/// <summary>
/// Given an IQueryable, such as a table from an EntityFramework context, and
/// an POCO similar to ag-grid's javascript filter model and sort model,
/// apply the filters and sorts and start-row and end-row to the IQueryable
/// using dynamic Linq. Return the modifying IQueryable. Note that it hasn't
/// been executed yet.
///
/// See documentation at http://ag-grid.com for more info. We are doing server-side
/// filter/sort.
/// </summary>
public static FilterSortResponse <T> ApplyFilterSort <T>(IQueryable <T> queryable, FilterSortModel filterSortModel, string defaultSortField = null, string defaultSortOrder = null)
{
IQueryable <T> result = queryable;
int totalCount = -1;
// Apply default sort if relevant
if ((filterSortModel == null) && !string.IsNullOrWhiteSpace(defaultSortField))
{
filterSortModel = new FilterSortModel {
SortModel = SortItem.NewSortItemList(defaultSortField, defaultSortOrder)
};
}
if (filterSortModel != null)
{
// Apply WHERE clause filters
if ((filterSortModel.FilterModel != null) && (filterSortModel.FilterModel.Count > 0))
{
foreach (KeyValuePair <String, FilterItem> kvp in filterSortModel.FilterModel)
{
result = AddWhereClauseChunk <T>(result, kvp.Key, kvp.Value);
}
}
// Figure out total count
totalCount = queryable.Count();
// Apply ORDER BY clauses
if (((filterSortModel.SortModel == null) || (filterSortModel.SortModel.Count == 0)) && !String.IsNullOrWhiteSpace(defaultSortField))
{
// Apply default sort if relevant
filterSortModel.SortModel = SortItem.NewSortItemList(defaultSortField, defaultSortOrder);
}
if ((filterSortModel.SortModel != null) && (filterSortModel.SortModel.Count > 0))
{
foreach (SortItem sortItem in filterSortModel.SortModel)
{
result = AddOrderByChunk <T>(result, sortItem);
}
}
// Apply start/end rows
if (filterSortModel.EndRow > 0)
{
int rowCount = filterSortModel.EndRow - filterSortModel.StartRow + 1;
result = result.Skip(filterSortModel.StartRow).Take(rowCount);
}
}
return(new FilterSortResponse <T> {
rows = result.ToList(), totalCount = totalCount
});
}
public static IQueryable <T> AddOrderByChunk <T>(IQueryable <T> item, SortItem sortItem)
{
// Skip generated field "lineNumber" because you can't sort on that
if ((item != null) && (sortItem != null) && !sortItem.ColId.ToUpper().Equals("LINENUMBER"))
{
// Sort order is "ASC" unless we explicitly match "DESC".
// Remember NOT to trust input data at all
string sortDirection = "ASC";
if (!String.IsNullOrWhiteSpace(sortItem.Sort))
{
if ("DESC".Equals(sortItem.Sort.Trim().ToUpper()))
{
sortDirection = "DESC";
}
}
item = item.OrderBy(sortItem.ColId + " " + sortDirection);
}
return(item);
}
