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;
}