I have a generic method which dynamically creates a query in Entity Framework. I use this as a search function on data table headers.
The function works perfectly if the Entity property type/SQL data type is a string. This is because of the .Contains() extensions.
The problem comes in when the data type is something other than a string. These data types don't have the .Contains() extension.
I would like to be able to use this method across all data types, and have found that I could possibly use SqlFunctions.StringConvert. I also know that it has no option for integer, and will have to convert the integer based properties into double.
I am unsure how to implement SqlFunctions.StringConvert generically, please see my below method (you will see that I have excluded the data types which have no .Contains() extension):
public static IQueryable<T> Filter<T>(this IQueryable<T> query, List<SearchFilterDto> filters)
where T : BaseEntity
{
if (filters != null && filters.Count > 0 && !filters.Any(f => string.IsNullOrEmpty(f.Filter)))
{
Expression filterExpression = null;
ParameterExpression parameter = Expression.Parameter(query.ElementType, "item");
filterExpression = filters.Select(f =>
{
Expression selector = parameter;
Expression pred = Expression.Constant(f.Filter);
foreach (var member in f.Column.Split('.'))
{
PropertyInfo mi = selector.Type.GetProperty(member, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
if (mi != null)
{
selector = Expression.Property(selector, mi);
if (selector.Type == typeof(Guid) ||
selector.Type == typeof(Guid?) ||
selector.Type == typeof(DateTime) ||
selector.Type == typeof(DateTime?) ||
selector.Type == typeof(int) ||
selector.Type == typeof(int?)
)
{
return null;
}
}
else
{
return null;
}
}
Expression containsMethod = Expression.Call(selector, "Contains", null, pred);
return containsMethod;
}).Where(r => r != null).Aggregate(Expression.And);
LambdaExpression where = Expression.Lambda(filterExpression, parameter);
MethodInfo whereCall = (typeof(Queryable).GetMethods().First(mi => mi.Name == "Where" && mi.GetParameters().Length == 2).MakeGenericMethod(query.ElementType));
MethodCallExpression call = Expression.Call(whereCall, new Expression[] { query.Expression, where });
return query.Provider.CreateQuery<T>(call);
}
return query;
}
The function works perfectly if the Entity property type/SQL data type is a string. This is because of the .Contains() extensions.
I would like to mention that the Contains in this case is not an extension, but a regular string.Contains method.
I would like to be able to use this method across all data types
This is not a good idea since the non string values can have different string representations, so it's not quite clear what you'll be searching for.
But let assume you want it anyway.
and have found that I could possibly use SqlFunctions.StringConvert
There are two drawbacks - first, SqlFunctions are SqlServer specific (not like DbFunctions for instance), and second, StringConvert works only with double and decimal. IMO the better choice would be using the object.ToString method which is supported in EF (at least in the lastest EF6).
I'm going to provide you a solution based on object.ToString(). But before doing that, let me give you some hints when working with expressions. Anytime you want to build an expression using System.Linq.Expressions and don't know how, you can build a similar sample typed expression and examine it inside the debugger Locals/Watch window. For instance:
public class Foo
{
public int Bar { get; set; }
}
Expression<Func<Foo, bool>> e = item =>
SqlFunctions.StringConvert((decimal?)item.Bar).Contains("1");
You can put a break point and start expanding e members, then their members etc. and you'll see how the expression has been built by the compiler, then all you need is to find the respective Expression methods.
Finally, here is the solution itself. I've also included some little tricks that allow avoiding working directly with reflection and string method names where possible:
public static class QueryableUtils
{
static Expression<Func<T, TResult>> Expr<T, TResult>(Expression<Func<T, TResult>> source) { return source; }
static MethodInfo GetMethod(this LambdaExpression source) { return ((MethodCallExpression)source.Body).Method; }
static readonly MethodInfo Object_ToString = Expr((object x) => x.ToString()).GetMethod();
static readonly MethodInfo String_Contains = Expr((string x) => x.Contains("y")).GetMethod();
public static IQueryable<T> Filter<T>(this IQueryable<T> query, List<SearchFilterDto> filters)
// where T : BaseEntity
{
if (filters != null && filters.Count > 0 && !filters.Any(f => string.IsNullOrEmpty(f.Filter)))
{
var item = Expression.Parameter(query.ElementType, "item");
var body = filters.Select(f =>
{
// Process the member path and build the final value selector
Expression value = item;
foreach (var memberName in f.Column.Split('.'))
{
var member = item.Type.GetProperty(memberName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance) ??
(MemberInfo)item.Type.GetField(memberName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
if (member == null) return null; // Should probably throw an error?
value = Expression.MakeMemberAccess(value, member);
}
// NOTE: "Safe" skipping invalid arguments is not a good practice.
// Without that requirement, the above block will be simply
// var value = f.Column.Split('.').Aggregate((Expression)item, Expression.PropertyOrField);
// Convert value to string if needed
if (value.Type != typeof(string))
{
// Here you can use different conversions based on the value.Type
// I'll just use object.ToString()
value = Expression.Call(value, Object_ToString);
}
// Finally build and return a call to string.Contains method
return (Expression)Expression.Call(value, String_Contains, Expression.Constant(f.Filter));
})
.Where(r => r != null)
.Aggregate(Expression.AndAlso);
var predicate = Expression.Lambda<Func<T, bool>>(body, item);
query = query.Where(predicate);
}
return query;
}
}