Extending LINQ to Entities

The other day I was trying to perform the equivalent of a SQL 'Where In' Clause using the Entity Framework.

As of today, the Entity Framework does not support such functionality natively. However, it is possible to replicate this functionality in a straight forward way.

Once again, I'm going to use extension methods and lambda expressions to get where I need.

To understand the way I've achieved this, you need to understand that (what I call) LINQ methods such as Where, OrderBy, Take etc manipulate the IQueryable<T> interface (where T is an EntityObject within your ObjectContext). So in order to extend the functionality of the Entity Framework, we're going to need to extend any class that implements the IQueryable<T> interface.

The extension method looks like this:

public static IQueryable<T> In<T, TValue>(this IQueryable<T> source, Expression<Func<T, TValue>> valueSelector, IEnumerable<TValue> values)
{
if (null == valueSelector)
{
throw new ArgumentNullException("valueSelector");
}

if (null == values)
{
throw new ArgumentNullException("values");
}

if (!values.Any())
{
throw new ArgumentOutOfRangeException("values");
}

ParameterExpression p = valueSelector.Parameters.Single();

var equals = values.Select(value => (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue))));

var body = equals.Aggregate((accumulate, equal) => Expression.Or(accumulate, equal));

var containsExpression = Expression.Lambda<Func<T, bool>>(body, p);

string command = "Where";

Type type = typeof(T);

var resultExpression = Expression.Call(
typeof(Queryable),
command,
new Type[] { type },
source.Expression,
Expression.Quote(containsExpression));

return source.Provider.CreateQuery<T>(resultExpression);
}

Firstly, we're providing two arguments - the first is a parameter to tell the 'In' method how to select values within the clause. The second is a list of values mimicking the In clause itself.

We expand the Expression tree to write a standard 'Where' clause, with each predicate explictly stated, instead of

Where [Name] In { A, B, C }

we write

Where [Name] = 'A' OR [Name] = 'B' OR [Name] = 'C'

Finally, we execute the expression on the source object. The return type of IQueryable<T> enables us to 'chain' this method with any of the existing methods.

To call this method, do something like this:

List<string> names = new List<string>();
names.Add("Fluffy");
names.Add("Sweetness");
var results = objectcontext.Person.In(p => p.Name, names).ToList();

This selects all users whose [Name] is either 'Fluffy' or 'Sweetness' from the table 'Person' within my object context. You'll notice that the code has the same feel as it might with any of the native LINQ methods.

Happy Coding!

Graeme