private static IQueryable FilterResponse(GridDataRequest request, IQueryable subset, GridDataResponse response)
{
var isDbQuery = subset.GetType().GetTypeInfo().IsGenericType&&
subset.GetType().GetInterfaces().Any(y => y == typeof(IListSource));
// Perform Searching
var searchLambda = new StringBuilder();
var searchParamArgs = new List <object>();
var searchValue = isDbQuery ? request.Search.Text : request.Search.Text.ToLowerInvariant();
switch (request.Search.Operator)
{
case CompareOperators.Auto:
var filter = string.Empty;
var values = new List <object>();
if (request.Columns.Any(x => x.Searchable))
{
filter = "(";
}
foreach (var column in request.Columns.Where(x => x.Searchable))
{
filter += string.Format(isDbQuery
? "{0}.Contains(@{1}) ||"
: "({0} != null && {0}.ToLowerInvariant().Contains(@{1})) ||", column.Name, values.Count);
values.Add(searchValue);
}
if (string.IsNullOrEmpty(filter) == false)
{
searchLambda.Append(filter.Remove(filter.Length - 3, 3) + ") &&");
searchParamArgs.AddRange(values);
}
break;
}
// Perform Filtering
foreach (var column in request.Columns.Where(x => x.Filter != null))
{
if (string.IsNullOrWhiteSpace(column.Filter.Text) && column.Filter.Argument == null)
{
continue; // TODO: Handle null?
}
column.Filter.HasFilter = true;
switch (column.Filter.Operator)
{
case CompareOperators.Equals:
case CompareOperators.NotEquals:
if (string.IsNullOrWhiteSpace(column.Filter.Text))
{
continue;
}
if (column.DataType == DataType.Date)
{
searchLambda.AppendFormat(
column.Filter.Operator == CompareOperators.Equals
? "({0} >= @{1} && {0} <= @{2}) &&"
: "({0} < @{1} || {0} > @{2}) &&", column.Name,
searchParamArgs.Count, searchParamArgs.Count + 1);
}
else
{
searchLambda.AppendFormat("{0} {2} @{1} &&", column.Name, searchParamArgs.Count,
GetSqlOperator(column.Filter.Operator));
}
if (column.DataType == DataType.Numeric)
{
searchParamArgs.Add(decimal.Parse(column.Filter.Text));
}
else if (column.DataType == DataType.DateTime || column.DataType == DataType.DateTimeUtc)
{
searchParamArgs.Add(DateTime.Parse(column.Filter.Text));
}
else if (column.DataType == DataType.Date)
{
searchParamArgs.Add(DateTime.Parse(column.Filter.Text).Date);
searchParamArgs.Add(DateTime.Parse(column.Filter.Text).Date.AddDays(1).AddMinutes(-1));
}
else if (column.DataType == DataType.Boolean)
{
searchParamArgs.Add(bool.Parse(column.Filter.Text));
}
else
{
searchParamArgs.Add(column.Filter.Text);
}
break;
case CompareOperators.Contains:
searchLambda.AppendFormat(
isDbQuery
? "{0}.Contains(@{1}) &&"
: "({0} != null && {0}.ToLowerInvariant().Contains(@{1})) &&", column.Name,
searchParamArgs.Count);
searchParamArgs.Add(column.Filter.Text.ToLowerInvariant());
break;
case CompareOperators.StartsWith:
searchLambda.AppendFormat(
isDbQuery
? "{0}.StartsWith(@{1}) &&"
: "({0} != null && {0}.ToLowerInvariant().StartsWith(@{1})) &&", column.Name,
searchParamArgs.Count);
searchParamArgs.Add(column.Filter.Text.ToLowerInvariant());
break;
case CompareOperators.EndsWith:
searchLambda.AppendFormat(
isDbQuery
? "{0}.EndsWith(@{1}) &&"
: "({0} != null && {0}.ToLowerInvariant().EndsWith(@{1})) &&", column.Name,
searchParamArgs.Count);
searchParamArgs.Add(column.Filter.Text.ToLowerInvariant());
break;
case CompareOperators.NotContains:
searchLambda.AppendFormat(
isDbQuery
? "{0}.Contains(@{1}) == false &&"
: "({0} != null && {0}.ToLowerInvariant().Contains(@{1}) == false) &&", column.Name,
searchParamArgs.Count);
searchParamArgs.Add(column.Filter.Text.ToLowerInvariant());
break;
case CompareOperators.NotStartsWith:
searchLambda.AppendFormat(
isDbQuery
? "{0}.StartsWith(@{1}) == false &&"
: "({0} != null && {0}.ToLowerInvariant().StartsWith(@{1}) == false) &&", column.Name,
searchParamArgs.Count);
searchParamArgs.Add(column.Filter.Text.ToLowerInvariant());
break;
case CompareOperators.NotEndsWith:
searchLambda.AppendFormat(
isDbQuery
? "{0}.EndsWith(@{1}) == false &&"
: "({0} != null && {0}.ToLowerInvariant().EndsWith(@{1}) == false) &&", column.Name,
searchParamArgs.Count);
searchParamArgs.Add(column.Filter.Text.ToLowerInvariant());
break;
case CompareOperators.Gte:
case CompareOperators.Gt:
case CompareOperators.Lte:
case CompareOperators.Lt:
searchLambda.AppendFormat("{0} {2} @{1} &&", column.Name, searchParamArgs.Count,
GetSqlOperator(column.Filter.Operator));
if (column.DataType == DataType.Numeric)
{
searchParamArgs.Add(decimal.Parse(column.Filter.Text));
}
else
{
searchParamArgs.Add(DateTime.Parse(column.Filter.Text));
}
break;
case CompareOperators.Multiple:
if (column.Filter.Argument == null || column.Filter.Argument.Length == 0)
{
continue;
}
var filterString = "(";
foreach (var filter in column.Filter.Argument)
{
filterString += string.Format(" {0} == @{1} ||", column.Name, searchParamArgs.Count);
searchParamArgs.Add(filter);
}
if (filterString.Length == 1)
{
continue;
}
searchLambda.AppendFormat(filterString.Remove(filterString.Length - 3, 3) + ") &&");
break;
case CompareOperators.Between:
if (column.Filter.Argument == null || column.Filter.Argument.Length == 0)
{
continue;
}
searchLambda.AppendFormat("(({0} >= @{1}) && ({0} <= @{2})) &&", column.Name,
searchParamArgs.Count, searchParamArgs.Count + 1);
if (column.DataType == DataType.Numeric)
{
searchParamArgs.Add(decimal.Parse(column.Filter.Text));
searchParamArgs.Add(decimal.Parse(column.Filter.Argument[0]));
}
else
{
searchParamArgs.Add(DateTime.Parse(column.Filter.Text));
searchParamArgs.Add(DateTime.Parse(column.Filter.Argument[0]));
}
break;
}
}
if (searchLambda.Length > 0)
{
subset = subset.Where(searchLambda.Remove(searchLambda.Length - 3, 3).ToString(),
searchParamArgs.ToArray());
if (subset != null)
{
response.FilteredRecordCount = subset.Count();
}
}
return(subset);
}
/// <summary>
/// Generates a GridDataResponse using the GridDataRequest and an IQueryable source,
/// like a DataSet in Entity Framework.
/// </summary>
/// <param name="request">The Tubular Grid Request.</param>
/// <param name="dataSource">The IQueryable source.</param>
/// <param name="preProcessSubset">The subset's process delegate.</param>
/// <returns>A grid response.</returns>
public static GridDataResponse CreateGridDataResponse(
this GridDataRequest request,
IQueryable dataSource,
ProcessResponseSubset?preProcessSubset = null)
{
if (request == null)
{
throw new ArgumentNullException(nameof(request));
}
if (request.Columns.Any() != true)
{
throw new ArgumentOutOfRangeException(nameof(request), "Missing column information");
}
var response = new GridDataResponse
{
Counter = request.Counter,
TotalRecordCount = dataSource.Count(),
FilteredRecordCount = dataSource.Count(),
};
var properties = dataSource.ElementType.ExtractProperties();
var columnMap = MapColumnsToProperties(request.Columns, properties);
var subset = FilterResponse(request, dataSource, response);
// Perform Sorting
var orderingExpression = request.Columns.Where(x => x.SortOrder > 0)
.OrderBy(x => x.SortOrder)
.Aggregate(string.Empty,
(current, column) =>
current +
(column.Name + " " + (column.SortDirection == SortDirection.Ascending ? "ASC" : "DESC") +
", "));
// Apply the sorting expression if supplied
subset = !string.IsNullOrWhiteSpace(orderingExpression)
? subset.OrderBy(orderingExpression.Substring(0, orderingExpression.Length - 2))
: subset.OrderBy($"{request.Columns.First().Name} ASC");
// Check aggregations before paging
// Should it aggregate before filtering too?
response.AggregationPayload = AggregateSubset(request.Columns, subset);
var pageSize = request.Take;
// Take with value -1 represents entire set
if (request.Take == -1)
{
response.TotalPages = 1;
response.CurrentPage = 1;
pageSize = subset.Count();
subset = subset.Skip(request.Skip);
}
else
{
var filteredCount = subset.Count();
var totalPages = (int)Math.Ceiling((double)filteredCount / pageSize);
if (totalPages > 0)
{
response.TotalPages = totalPages;
var currentPage = (request.Skip / pageSize) + 1;
response.CurrentPage = Math.Min(currentPage, totalPages);
if (request.Skip > 0)
{
var maxSkip = pageSize *
(response.CurrentPage == response.TotalPages
? response.CurrentPage - 1
: response.CurrentPage);
subset = subset.Skip(Math.Min(request.Skip, maxSkip));
}
}
else
{
response.TotalPages = 1;
response.CurrentPage = 1;
}
subset = subset.Take(pageSize);
}
// Generate the response data in a suitable format
if (preProcessSubset != null)
{
subset = preProcessSubset(subset);
}
response.Payload = CreateGridPayload(subset, columnMap, pageSize, request.TimezoneOffset);
return(response);
}
/// <summary>
/// Generates a GridDataReponse using the GridDataRequest and an IQueryable source,
/// like a DataSet in Entity Framework.
/// </summary>
/// <param name="request">The Tubular's grid request</param>
/// <param name="dataSource">The IQueryable source</param>
/// <param name="preProcessSubset">The subset's process delegate</param>
/// <returns></returns>
public static GridDataResponse CreateGridDataResponse(this GridDataRequest request, IQueryable dataSource,
ProcessResponseSubset preProcessSubset)
{
if (request == null)
{
throw new InvalidOperationException("The GridDataRequest is null");
}
var response = new GridDataResponse
{
Counter = request.Counter,
TotalRecordCount = dataSource.Count(),
FilteredRecordCount = dataSource.Count()
};
var properties = ExtractProperties(dataSource.ElementType);
var columnMap = MapColumnsToProperties(request.Columns, properties);
var subset = FilterResponse(request, dataSource, response);
// Perform Sorting
var orderingExpression = string.Empty;
foreach (var column in request.Columns.Where(x => x.SortOrder > 0).OrderBy(x => x.SortOrder))
{
orderingExpression += column.Name + " " +
(column.SortDirection == SortDirection.Ascending ? "ASC" : "DESC") + ", ";
}
// Apply the sorting expression if supplied
if (!string.IsNullOrWhiteSpace(orderingExpression))
{
subset = subset.OrderBy(orderingExpression.Substring(0, orderingExpression.Length - 2));
}
else
{
subset = subset.OrderBy(request.Columns.First().Name + " ASC");
}
// Check aggregations before paging
// Should it aggregate before filtering too?
response.AggregationPayload = new Dictionary <string, object>();
foreach (var column in request.Columns.Where(x => x.Aggregate != AggregationFunction.None))
{
switch (column.Aggregate)
{
case AggregationFunction.Sum:
// we try to select the column as decimal? and sum it
if (subset.ElementType.GetProperty(column.Name).PropertyType == typeof(double))
{
response.AggregationPayload.Add(column.Name,
subset.Select(column.Name).Cast <double?>().Sum());
}
else
{
response.AggregationPayload.Add(column.Name,
subset.Select(column.Name).Cast <decimal?>().Sum());
}
break;
case AggregationFunction.Average:
if (subset.ElementType.GetProperty(column.Name).PropertyType == typeof(double))
{
response.AggregationPayload.Add(column.Name,
subset.Select(column.Name).Cast <double?>().Average());
}
else
{
response.AggregationPayload.Add(column.Name,
subset.Select(column.Name).Cast <decimal?>().Average());
}
break;
case AggregationFunction.Max:
if (subset.ElementType.GetProperty(column.Name).PropertyType == typeof(double))
{
response.AggregationPayload.Add(column.Name,
subset.Select(column.Name).Cast <double?>().Max());
}
else
{
response.AggregationPayload.Add(column.Name,
subset.Select(column.Name).Cast <decimal?>().Max());
}
break;
case AggregationFunction.Min:
if (subset.ElementType.GetProperty(column.Name).PropertyType == typeof(double))
{
response.AggregationPayload.Add(column.Name,
subset.Select(column.Name).Cast <double?>().Min());
}
else
{
response.AggregationPayload.Add(column.Name,
subset.Select(column.Name).Cast <decimal?>().Min());
}
break;
case AggregationFunction.Count:
response.AggregationPayload.Add(column.Name, subset.Select(column.Name).Cast <string>().Count());
break;
case AggregationFunction.DistinctCount:
response.AggregationPayload.Add(column.Name,
subset.Select(column.Name).Cast <string>().Distinct().Count());
break;
default:
response.AggregationPayload.Add(column.Name, 0);
break;
}
}
var pageSize = request.Take;
// Take with value -1 represents entire set
if (request.Take == -1)
{
response.TotalPages = 1;
response.CurrentPage = 1;
pageSize = subset.Count();
subset = subset.Skip(request.Skip);
}
else
{
response.TotalPages = (response.FilteredRecordCount + pageSize - 1) / pageSize;
if (response.TotalPages > 0)
{
response.CurrentPage = 1 +
(int)
Math.Truncate((request.Skip / (float)response.FilteredRecordCount) *
response.TotalPages);
if (response.CurrentPage > response.TotalPages)
{
response.CurrentPage = response.TotalPages;
request.Skip = (response.CurrentPage - 1) * request.Take;
}
if (request.Skip < 0)
{
request.Skip = 0;
}
subset = subset.Skip(request.Skip);
}
subset = subset.Take(request.Take);
}
// Generate the response data in a suitable format
if (preProcessSubset != null)
{
subset = preProcessSubset(subset);
}
response.Payload = CreateGridPayload(subset, columnMap, pageSize, request.TimezoneOffset);
return(response);
}