public static FilteringResult <T> ApplyToQuery <T>(this IDataTablesRequest request, IQueryable <T> query, Expression <Func <ApplyToQueryOptions <T>, ApplyToQueryOptions <T> > > options)
{
var result = new FilteringResult <T>();
var opt = new ApplyToQueryOptions <T>(request);
if (options != null)
{
options.Compile()(opt);
}
VerifyOptionsAgainstRequest <T>(request, opt);
var q = query;
result.TotalRecords = opt.TotalRecords.HasValue ? opt.TotalRecords.Value : q.Count();
//apply searching
q = ApplySearchExpression(q, request, opt);
//apply sorting
q = ApplySortExpression(q, request, opt);
result.TotalRecordsFiltered = q.Count();
//apply pagination
if (request.Length > 0)
{
q = q.Skip(request.Start).Take(request.Length);
}
result.QueryFiltered = q.Cast <T>();
return(result);
}
private static IQueryable <T> ApplySortExpression <T>(IQueryable <T> query, IDataTablesRequest request, ApplyToQueryOptions <T> options)
{
var sortedColumns = request.Columns.Where(c => c.IsSortable && c.Sort != null).OrderBy(c => c.Sort.Order);
if (sortedColumns.Count() == 0)
{
return(query);
}
var q = query;
ParameterExpression target = Expression.Parameter(typeof(T), "target");
int cnt = 0;
foreach (var col in sortedColumns)
{
var opt = options.GetColumnOptionsFor(col.Name);
if (opt.IgnoreWhenSorting)
{
continue;
}
Expression sortExp = null;
if (opt.SortExpression != null)
{
//replace expression parameters
sortExp = ExpressionHelper.Replace(opt.SortExpression.Body, opt.SortExpression.Parameters[0], target);
}
else if (opt.TargetProperty != null)
{
sortExp = ExpressionHelper.ExtractPropertyChain(opt.TargetProperty, target);
}
if (sortExp != null)
{
if (cnt == 0)
{
q = (col.Sort.Direction == SortDirection.Ascending) ? ExpressionHelper.AddOrderBy(q, sortExp, target) : ExpressionHelper.AddOrderByDescending(q, sortExp, target);
}
else
{
q = (col.Sort.Direction == SortDirection.Ascending) ? ExpressionHelper.AddThenBy(q, sortExp, target) : ExpressionHelper.AddThenByDescending(q, sortExp, target);
}
cnt++;
}
}
return(q);
}
private static IQueryable <T> ApplySearchExpression <T>(IQueryable <T> query, IDataTablesRequest request, ApplyToQueryOptions <T> options)
{
ParameterExpression target = Expression.Parameter(typeof(T), "target");
var columnSearchExp = BuildColumnSearchExpression(query, request, options, target);
var globalSearchExp = BuildGlobalSearchExpression(query, request, options, target);
if (columnSearchExp == null && globalSearchExp == null)
{
return(query);
}
Expression searchExp = (columnSearchExp != null) ? columnSearchExp : globalSearchExp;
if (columnSearchExp != null && globalSearchExp != null)
{
searchExp = Expression.AndAlso(columnSearchExp, globalSearchExp);
}
return(ExpressionHelper.AddWhere(query, searchExp, target));
}
private static Expression BuildGlobalSearchExpression <T>(IQueryable <T> query, IDataTablesRequest request, ApplyToQueryOptions <T> options, ParameterExpression target)
{
if (String.IsNullOrEmpty(request.Search.Value))
{
return(null);
}
Expression exp = null;
foreach (var col in request.Columns.Where(c => c.IsSearchable))
{
var opt = options.GetColumnOptionsFor(col.Name);
if (opt.IgnoreWhenSearching)
{
continue;
}
Expression matchExp = null;
if (!opt.EnableGlobalSearch)
{
matchExp = Expression.Constant(false);
}
else if (opt.SearchExpression != null)
{
//replace expression parameters
matchExp = ExpressionHelper.Replace(opt.SearchExpression.Body, opt.SearchExpression.Parameters[0], target);
matchExp = ExpressionHelper.Replace(matchExp, opt.SearchExpression.Parameters[1], Expression.Constant(request.Search.Value));
}
else
{
matchExp = BuildMatchExpression(opt.TargetProperty, request.Search.Value, opt.StringMatchMethod, target);
}
if (matchExp != null)
{
exp = (exp == null) ? matchExp : Expression.OrElse(exp, matchExp);
}
}
return(exp);
}
private static void VerifyOptionsAgainstRequest <T>(IDataTablesRequest request, ApplyToQueryOptions <T> options)
{
var searchableColumns = request.Columns.Where(c => c.IsSearchable);
foreach (var col in searchableColumns)
{
var opt = options.GetColumnOptionsFor(col.Name);
if (!opt.IgnoreWhenSearching && opt.TargetProperty == null && opt.SearchExpression == null)
{
throw new Exception(String.Format("Can't find the target property for searchable column '{0}' in type '{1}'. Mark column as not searchable or use MapToProperty(), UseSearchExpression() or IgnoreWhenSearching() methods.", col.Name, typeof(T)));
}
if (opt.SearchExpression != null)
{
bool parameterIsUsed = ExpressionHelper.FindParameter(opt.SearchExpression.Body, opt.SearchExpression.Parameters[1]);
if (!parameterIsUsed)
{
throw new Exception(String.Format("Search value is not used in SearchExpression for column '{0}'", col.Name));
}
}
}
var sortableColumns = request.Columns.Where(c => c.IsSortable);
foreach (var col in sortableColumns)
{
var opt = options.GetColumnOptionsFor(col.Name);
if (!opt.IgnoreWhenSorting && opt.TargetProperty == null && opt.SortExpression == null)
{
throw new Exception(String.Format("Can't find the target property for sortable column '{0}' in type '{1}'. Mark column as not searchable or use MapToProperty(), UseSortExpression() or IgnoreWhenSorting() methods.", col.Name, typeof(T)));
}
}
}
