I'm trying to build a dynamic LINQ query, in Entity Framework, from a user-provided set of collection criteria. Eventually, this will include more complex behaviors, but currently I just have a list of field names and values, and I want to return all the records in which the field names have those values.
My basic structure is this:
public IEnumerable<ThingViewModel> getMythings(SelectionCriteria selectionCriteria)
{
var predicate = constructPredicate<Thing>(selectionCriteria);
var things = this.dbContext.Things.Where(predicate).ToList();
return Mapper.Map<List<Thing>, List<ThingViewModel>>(things);
}
Where all the interesting work is in constructPredicate():
private static Expression<Func<T, bool>> constructPredicate<T>(SelectionCriteria selectionCriteria)
{
// using Pete Montgomery's PredicateBuilder:
// http://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/
var predicate = PredicateBuilder.True<T>();
foreach (var item in selectionCriteria.andList)
{
// Accessing foreach values in closures can result in unexpected results.
// http://stackoverflow.com/questions/14907987/access-to-foreach-variable-in-closure
var fieldName = item.fieldName;
var fieldValue = item.fieldValue;
var parameter = Expression.Parameter(typeof (T), "t");
var property = Expression.Property(parameter, fieldName);
var value = Expression.Constant(fieldValue);
var lambda = buildCompareLambda<T>(property, value, parameter);
predicate = predicate.And(lambda);
}
return predicate;
}
All of that seems like a perfectly reasonable structure, but it's in the buildCompareLambda() that I'm having difficulties. I'm not seeing a way to do this in a generic way, I'm having to create different methods for different types. I'd started by handling strings, and that was simple enough. I next tried to handle integers, but it turns out that the integer fields in the database are nullable, which introduced a whole new class of complexity.
My buildCompareLambda(), so far:
private static Expression<Func<T, bool>> buildCompareLambda<T>(
MemberExpression property,
ConstantExpression value,
ParameterExpression parameter)
{
Expression<Func<T, bool>> lambda = null;
if (property.Type == typeof (string))
lambda = buildStringCompareLambda<T>(property, value, parameter);
else if (property.Type.IsGenericType && Nullable.GetUnderlyingType(property.Type) != null)
lambda = buildNullableCompareLambda<T>(property, value, parameter);
if (lambda == null)
throw new Exception(String.Format("SelectrionCriteria cannot handle property type '{0}'", property.Type.Name));
return lambda;
}
As I said, buildStringCompareLambda is simple enough:
private static Expression<Func<T, bool>> buildStringCompareLambda<T>(
MemberExpression property,
ConstantExpression value,
ParameterExpression parameter)
{
var equalsMethod = typeof (string).GetMethod("Equals",
new[] {typeof (string), typeof (string)});
var comparison = Expression.Call(equalsMethod, property, value);
return Expression.Lambda<Func<T, bool>>(comparison, parameter);
}
But buildNullableCompareLambda() is getting ugly:
private static Expression<Func<T, bool>> buildNullableCompareLambda<T>(MemberExpression property,
ConstantExpression value,
ParameterExpression parameter)
{
var underlyingType = Nullable.GetUnderlyingType(property.Type);
if (underlyingType == typeof (int) || underlyingType == typeof (Int16) || underlyingType == typeof (Int32) ||
underlyingType == typeof (Int64) || underlyingType == typeof (UInt16) || underlyingType == typeof (UInt32) ||
underlyingType == typeof (UInt64))
{
var equalsMethod = underlyingType.GetMethod("Equals", new[] {underlyingType});
var left = Expression.Convert(property, underlyingType);
var right = Expression.Convert(value, underlyingType);
var comparison = Expression.Call(left, equalsMethod, new Expression[] {right});
return Expression.Lambda<Func<T, bool>>(comparison, parameter);
}
return null;
}
It's my intent to add support for more types, in buildNullableCompareLambda(), and to move the handling of each type into a function, so that the same code can be called from buildCompareLambda() and from buildNullableCompareLambda(). But that's for the future - currently I'm stuck on comparing ints. Currently, I'm converting both the property and the value to the underlying type, since I don't want to have separate functions for each integer type, and I don't want the user to have to care whether the EF models a field into an Int16 or an Int32. And that's working, for non-null fields.
I've been browsing around SO, and finding some answers, which is how I've gotten as far as I have, but none of the answers I've seen on handling nullable types really work for me, because they don't really handle nulls.
In my case, if the user passes me a selection criteria with an item that is supposed to equal null, I would want to return the records for which that field are null, and this bit about converting both sides to a base type doesn't seem to work. I'm getting an "Object reference not set to an instance of an object" exception.
In SQL, what I'd want is a "WHERE field IS NULL", if the value is null, or "WHERE field = 'value'", if it is not. And I'm not seeing how to build that kind of alternative into an expression tree.
Any ideas?
Added: It has been suggested that I use Expression.Equal().
With that, my loop becomes:
private static Expression<Func<T, bool>> constructPredicate<T>(SelectionCriteria selectionCriteria)
{
var predicate = PredicateBuilderEx.True<T>();
var foo = PredicateBuilder.True<T>();
foreach (var item in selectionCriteria.andList)
{
var fieldName = item.fieldName;
var fieldValue = item.fieldValue;
var parameter = Expression.Parameter(typeof (T), "t");
var property = Expression.Property(parameter, fieldName);
var value = Expression.Constant(fieldValue);
var comparison = Expression.Equal(property, value);
var lambda = Expression.Lambda<Func<T, bool>>(comparison, parameter);
predicate = PredicateBuilderEx.And(predicate, lambda);
}
return predicate;
}
And that doesn't work. I get an exception:
The binary operator Equal is not defined for the types 'System.Nullable`1[System.Int16]' and 'System.Int16'.
