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