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