private IQueryable <TQuery> AddSorting(IQueryable <TQuery> query, TableRequestDTO request)
{
//TODO: support multiple column sorting??
//if (request.order.Count > 0 && request.order.First().column >= 0 && request.columns.Count > 0)
//{
//if (!string.IsNullOrWhiteSpace(request.sortField))
//{
if (!string.IsNullOrWhiteSpace(request.SortField))
{
request.SortField = request.SortField.Substring(0, 1).ToUpper() + request.SortField.Substring(1);
}
var orderBy = GetOrderBy(request.SortField);
//todo: consider an OnAfter method as a possible extension point
//orderBy = OnAfterGetOrderBy(orderBy, request.sortField);
if (orderBy != null)
{
bool inReverseOrder = (request.SortOrder?.ToLower() == "descend");
return(AddOrderByToQuery(query, orderBy, request.SortField, inReverseOrder));
}
//}
//}
// just return the original query unmodified since no sorting was produced above
return(query);
}
private IQueryable <TQuery> AddGlobalFiltering(IQueryable <TQuery> query, TableRequestDTO request)
{
ParameterExpression itemParam = Expression.Parameter(typeof(TQuery), "x");
Expression combinedExpr = null;
if (!string.IsNullOrWhiteSpace(request.GlobalFilter))
{
// Break the search string up by the break characters and add multiple filters if there are multiple
string[] globalSearchStrings = request.GlobalFilter.Split(this.FilterSeparatorCharacters, StringSplitOptions.RemoveEmptyEntries);
Expression combinedOrExpr = null;
for (int j = 0; j < globalSearchStrings.Length; j++)
{
string searchTerm = globalSearchStrings[j].Trim();
if (string.IsNullOrWhiteSpace(searchTerm))
{
continue;
}
var filter = GetGlobalFilter(searchTerm);
//todo: consider an OnAfter method as a possible extension point
//filter = OnAfterGetColumnFilter(filter, column.columnProp, searchTerm);
if (filter != null)
{
var filterExpression = BuildGlobalFilterExpression(filter, itemParam, searchTerm);
if (filterExpression != null)
{
// AND the predicate to the expression
combinedOrExpr = (combinedOrExpr == null) ? filterExpression : Expression.AndAlso(combinedOrExpr, filterExpression);
}
else // invalid search string
{
combinedOrExpr = Expression.Equal(Expression.Constant(1), Expression.Constant(0));
}
}
}
// AND the predicate to the expression
combinedExpr = (combinedExpr == null) ? combinedOrExpr : Expression.AndAlso(combinedExpr, combinedOrExpr);
}
// just return the original query unmodified if no filtering was produced above
if (combinedExpr == null)
{
return(query);
}
// apply the filter expression to the Where clause of the query
var combinedLambda = Expression.Lambda <Func <TQuery, bool> >(combinedExpr, itemParam);
return(query.Where(combinedLambda));
}
private IQueryable <TQuery> AddPaging(IQueryable <TQuery> query, TableRequestDTO request)
{
if (request.Page > 0 && request.PageLength > -1)
{
query = query.Skip(request.Page * request.PageLength);
}
if (request.PageLength > 0) //TODO: review -1 or 0? Does Take(0) make since in any scenario?
{
query = query.Take(request.PageLength);
}
return(query);
}
public virtual TableResponseDTO <TOut> ExecuteRequest(TableRequestDTO request)
{
var baseQuery = GetBaseQuery(request);
// get the total records if requested to do so
int totalCount = -1; //TODO: what is the correct default? null?
if (this.CountTotalRecords)
{
totalCount = baseQuery.Count();
}
// apply column filtering
var columnFilteredQuery = AddFiltering(baseQuery, request);
var filteredQuery = AddGlobalFiltering(columnFilteredQuery, request);
// get the total records if requested to do so
this.FilteredCount = -1; //TODO: what is the correct default? null?
if (this.CountFilteredRecords)
{
this.FilteredCount = filteredQuery.Count();
}
TOut sumResult = default(TOut);
if (this.SumRecords)
{
sumResult = this.BuildSumResult(filteredQuery);
}
// apply sorting and paging
var sortedQuery = AddSorting(filteredQuery, request);
var pagedQuery = AddPaging(sortedQuery, request);
// project the records into output form
var trasformedResults = TransformResults(pagedQuery);
var projectedData = trasformedResults.AsEnumerable().Cast <TOut>().ToArray();
var results = PostProcessResults(projectedData).ToArray();
// return the results in a response container
return(new TableResponseDTO <TOut>(results, totalCount, this.FilteredCount, sumResult, request.Echo));
}
public virtual List <TOut> ExecuteRequestForExport(TableRequestDTO request)
{
var baseQuery = GetBaseQuery(request);
// apply column filtering
var columnFilteredQuery = AddFiltering(baseQuery, request);
var filteredQuery = AddGlobalFiltering(columnFilteredQuery, request);
// apply sorting and paging
var sortedQuery = AddSorting(filteredQuery, request);
//var pagedQuery = AddPaging(sortedQuery, request);
// project the records into output form and execute
var results = TransformResults(sortedQuery).ToList();
this.FilteredCount = results.Count;
results = PostProcessResults(results).ToList();
return(results);
}
private IQueryable <TQuery> AddFiltering(IQueryable <TQuery> query, TableRequestDTO request)
{
ParameterExpression itemParam = Expression.Parameter(typeof(TQuery), "x");
Expression combinedExpr = null;
// add column-specific filter predicates to the expression with an AND
if (request.Filters != null)
{
for (int i = 0; i < request.Filters.Count; i++)
{
var column = request.Filters[i];
if (!string.IsNullOrWhiteSpace(column.Filter))
{
// Break the search string up by the break characters and add multiple filters if there are multiple
string[] searchStrings = column.Filter.Split(this.FilterSeparatorCharacters, StringSplitOptions.RemoveEmptyEntries);
Expression combinedOrExpr = null;
for (int j = 0; j < searchStrings.Length; j++)
{
string searchTerm = searchStrings[j].Trim();
if (string.IsNullOrWhiteSpace(searchTerm))
{
continue;
}
if (!string.IsNullOrWhiteSpace(column.ColumnProp))
{
column.ColumnProp = column.ColumnProp.Substring(0, 1).ToUpper() + column.ColumnProp.Substring(1);
}
var filter = GetColumnFilter(column.ColumnProp, searchTerm);
if (filter != null)
{
var filterExpression = BuildFilterExpression(filter, itemParam, column.ColumnProp, searchTerm);
if (filterExpression != null)
{
// OR the predicate to the expression
combinedOrExpr = (combinedOrExpr == null) ? filterExpression : Expression.Or(combinedOrExpr, filterExpression);
}
else // invalid search string
{
combinedOrExpr = Expression.Equal(Expression.Constant(1), Expression.Constant(0));
}
}
}
// AND the predicate to the expression
combinedExpr = (combinedExpr == null) ? combinedOrExpr : Expression.AndAlso(combinedExpr, combinedOrExpr);
}
}
}
// just return the original query unmodified if no filtering was produced above
if (combinedExpr == null)
{
return(query);
}
// apply the filter expression to the Where clause of the query
var combinedLambda = Expression.Lambda <Func <TQuery, bool> >(combinedExpr, itemParam);
return(query.Where(combinedLambda));
}
protected abstract IQueryable <TQuery> GetBaseQuery(TableRequestDTO request);
protected override IQueryable <ApplicationUser> GetBaseQuery(TableRequestDTO request)
{
return(_userManager.Users);
}
public TableResponseDTO <UserDTO> GetDataTableResponse(TableRequestDTO tableRequest)
{
return(new UserTableProvider(_userManager).ExecuteRequest(tableRequest));
}
public TableResponseDTO <UserDTO> GetDataTableResponse(TableRequestDTO tableRequestDTO)
{
return(_userService.GetDataTableResponse(tableRequestDTO));
}