/// <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>
/// <returns></returns>
public static GridDataResponse CreateGridDataResponse(this GridDataRequest request, IQueryable dataSource)
{
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");
}
subset = subset.Skip(request.Skip);
var pageSize = request.Take;
// Take with value -1 represents entire set
if (request.Take == -1)
{
response.TotalPages = 1;
response.CurrentPage = 1;
pageSize = subset.Count();
}
else
{
subset = subset.Take(request.Take);
response.TotalPages = (response.FilteredRecordCount + pageSize - 1)/pageSize;
if (response.TotalPages > 0)
{
response.CurrentPage = 1 +
(int)
((request.Skip/(float) response.FilteredRecordCount)*response.TotalPages);
}
}
response.Payload = CreateGridPayload(subset, columnMap, pageSize, request.TimezoneOffset);
return response;
}
private static IQueryable FilterResponse(GridDataRequest request, IQueryable subset, GridDataResponse response)
{
// Perform Searching
var searchLambda = new StringBuilder();
var searchParamArgs = new List<object>();
switch (request.Search.Operator)
{
case CompareOperators.Auto:
foreach (var column in request.Columns.Where(x => x.Searchable))
{
searchLambda.AppendFormat("{0}.Contains(@{1}) ||", column.Name, searchParamArgs.Count);
searchParamArgs.Add(request.Search.Text);
}
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?
switch (column.Filter.Operator)
{
case CompareOperators.Equals:
case CompareOperators.NotEquals:
if (String.IsNullOrWhiteSpace(column.Filter.Text)) continue;
// TODO: This code is for use with Dynamic LINQ, but It needs EF
//if (column.DataType == DataType.Date.ToString().ToLower())
// searchLambda.AppendFormat("EntityFunctions.TruncateTime({0}) == @{1} &&", column.Name, searchParamArgs.Count);
//else
searchLambda.AppendFormat("{0} {2} @{1} &&", column.Name, searchParamArgs.Count, GetSqlOperator(column.Filter.Operator));
if (String.Equals(column.DataType, DataType.Numeric.ToString(),
StringComparison.CurrentCultureIgnoreCase))
{
searchParamArgs.Add(Decimal.Parse(column.Filter.Text));
}
else if (
String.Equals(column.DataType, DataType.DateTime.ToString(),
StringComparison.CurrentCultureIgnoreCase) ||
String.Equals(column.DataType, DataType.Date.ToString(), StringComparison.CurrentCultureIgnoreCase))
{
searchParamArgs.Add(DateTime.Parse(column.Filter.Text));
}
else if (String.Equals(column.DataType, DataType.Boolean.ToString(), StringComparison.CurrentCultureIgnoreCase))
{
searchParamArgs.Add(Boolean.Parse(column.Filter.Text));
}
else
{
searchParamArgs.Add(column.Filter.Text);
}
break;
case CompareOperators.Contains:
searchLambda.AppendFormat("{0}.Contains(@{1}) &&", column.Name, searchParamArgs.Count);
searchParamArgs.Add(column.Filter.Text);
break;
case CompareOperators.StartsWith:
searchLambda.AppendFormat("{0}.StartsWith(@{1}) &&", column.Name, searchParamArgs.Count);
searchParamArgs.Add(column.Filter.Text);
break;
case CompareOperators.EndsWith:
searchLambda.AppendFormat("{0}.EndsWith(@{1}) &&", column.Name, searchParamArgs.Count);
searchParamArgs.Add(column.Filter.Text);
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 (String.Equals(column.DataType, DataType.Numeric.ToString(), StringComparison.CurrentCultureIgnoreCase))
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 (String.Equals(column.DataType, DataType.Numeric.ToString(), StringComparison.CurrentCultureIgnoreCase))
{
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());
response.FilteredRecordCount = subset.Count();
}
return subset;
}