We had the same issues with our data and found a solution that seems to work well for us. We use a lot of entity framework and if we did it as the example code shows we would have to return our entire list and then filter it. Ok it's good for the client but bad for the server since you need to return all those rows. In one case we have 40 million rows so that wouldn't work for us.
We created a new generic method that uses IQueryable. The only requirements is that you have at least 1 ORDER BY in order to perform at take limit.
We also used some extension methods we found that allows you to add dynamic conditions to the where clause allowing full control of the created sql statement before you process it.
Hope it works for you, best part about this is it's all using generic objects.. :)
/// <summary>
/// Generic Filter Header using IQueryable for faster results.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="sort"></param>
/// <param name="filter"></param>
/// <param name="data"></param>
/// <param name="count"></param>
/// <returns></returns>
public static List<T> ListFilterHeader<T>(int start, int limit, DataSorter[] sort, string filter, IQueryable<T> data, out int count) where T : class
{
//-- start filtering -----------------------------------------------------------
FilterHeaderConditions fhc = new FilterHeaderConditions(filter);
foreach (FilterHeaderCondition condition in fhc.Conditions)
{
string dataIndex = condition.DataIndex;
FilterType type = condition.Type;
string op = condition.Operator;
object value = null;
switch (condition.Type)
{
case FilterType.String:
value = condition.Value<string>();
data = data.AddContainsCondition(dataIndex, value);
break;
case FilterType.Date:
value = condition.Value<DateTime>();
data = data.AddEqualCondition(dataIndex, value);
break;
case FilterType.Numeric:
value = condition.Value<string>();
data = data.AddContainsCondition(dataIndex, value);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
//-- end filtering ------------------------------------------------------------
//-- start sorting ------------------------------------------------------------
if (sort.Length > 0)
{
int counter = 0;
foreach (DataSorter s in sort)
{
data = data.AddOrderByCondition(s.Property, s.Direction.ToString(), counter);
counter++;
}
}
else
{
throw new Exception("At least 1 data sort is required to use this function!");
}
//-- end sorting ------------------------------------------------------------
count = data.Count();
//-- start paging ------------------------------------------------------------
data = data.Skip(start).Take(limit);
//-- end paging ------------------------------------------------------------
return data.ToList();
}
//Extension Methods....
public static IQueryable<T> AddEqualCondition<T, V>(this IQueryable<T> queryable, string propertyName, V propertyValue)
{
ParameterExpression pe = Expression.Parameter(typeof(T), "p");
var me = Expression.Property(pe, typeof(T).GetProperty(propertyName));
var ce = Expression.Constant(propertyValue, typeof(string));
MethodInfo method = typeof(string).GetMethod("Equal", new[] { typeof(string) });
var call = Expression.Call(ce, method, me);
var lambda = Expression.Lambda<Func<T, bool>>(call, pe);
IQueryable<T> x = queryable.Where<T>(lambda);
return (x);
}
public static IQueryable<T> AddContainsCondition<T, V>(this IQueryable<T> queryable, string propertyName, V propertyValue)
{
ParameterExpression _pe = Expression.Parameter(typeof(T), "p");
var _columnExpression = Expression.Property(_pe, typeof(T).GetProperty(propertyName));
var _valueExpression = Expression.Constant(propertyValue.ToString().ToLower(), typeof(string));
MethodInfo _methodToLower = typeof(string).GetMethod("ToLower", System.Type.EmptyTypes);
Expression _firstCall = Expression.Call(_columnExpression, _methodToLower);
MethodInfo _methodContains = typeof(string).GetMethod("Contains", new[] { typeof(string) });
Expression _secondCall = Expression.Call(_firstCall, _methodContains, _valueExpression);
var _lambda = Expression.Lambda<Func<T, bool>>(_secondCall, _pe);
IQueryable<T> x = queryable.Where<T>(_lambda);
return (x);
}
public static IQueryable<T> AddOrderByCondition<T>(this IQueryable<T> queryable, string ordering, string sortDirection, int count)
{
var type = typeof(T);
var property = type.GetProperty(ordering);
var parameter = Expression.Parameter(type, "p");
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
string direction;
if (count == 0)
{
direction = (sortDirection == "ASC") ? "OrderBy" : "OrderByDescending";
}
else
{
direction = (sortDirection == "ASC") ? "ThenBy" : "ThenByDescending";
}
MethodCallExpression resultExp = Expression.Call(typeof(Queryable), direction, new Type[] { type, property.PropertyType }, queryable.Expression, Expression.Quote(orderByExp));
return queryable.Provider.CreateQuery<T>(resultExp);
}
