Tip 8 - How to write 'WHERE IN' style queries using LINQ to Entities
Imagine if you have a table of People and you want to retrieve only those whose the Firstname is in a list of interesting firstnames. This is trivial in SQL, you write something like this:
SELECT * FROM People
WHERE Firstname IN ('Alex', 'Colin', 'Danny', 'Diego')
A SQL IN is equivalent to a LINQ Contains
In the LINQ (to objects) world there is no 'IN' so you need to reverse the order like and use the Contains method:
var names = new string[] { "Alex", "Colin", "Danny", "Diego" };
var matches = from person in people
where names.Contains(person.Firstname)
select person;
Notice that semantically we've gone from:
value.IN(set)
in SQL to
set.Contains(value)
In LINQ. The result however is the same.
Support for Contains in .NET 3.5 SP1 vs .NET 4.0
IEnumerable<T>.Contains(T t)will be supported in EF in .NET 4.0, so you can write queries like the LINQ query above in the next version of EF.
Unfortunately though it doesn't work in .NET 3.5 SP1: LINQ to Entities complains when it encounters a LINQ expression like this because it doesn't know how to translate calls to Contains to SQL.
But what about all our .NET 3.5 SP1 users out there? What can they do in the meantime?
Workaround for .NET 3.5 SP1
Well there is a workaround courtesy of Colin one of the big brains on the EF team.
The essence of the workaround is to recognize that you can rewrite the above query like this:
var matches = from person in people
where person.Firstname == "Alex" ||
person.Firstname == "Colin" ||
person.Firstname == "Danny" ||
person.Firstname == "Diego"
select person;
Certainly this is more 'wordy' and a pain to write but it works all the same.
So if we had some utility method that made it easy to create these kind of LINQ expressions we'd be in business.
Well that is exactly what Colin did in our forums a while back, with his utility method in place you can write something like this:
var matches = ctx.People.Where(
BuildOrExpression<People, string>(
p => p.Firstname, names
)
);
This builds an expression that has the same effect as:
var matches = from p in ctx.People
where names.Contains(p.Firstname)
select p;
But which more importantly actually works against .NET 3.5 SP1.
Extra Credit...
If you've read this far, well done!
Here is the plumbing function that makes this possible:
public static Expression<Func<TElement, bool>> BuildOrExpression<TElement, TValue>(
Expression<Func<TElement, TValue>> valueSelector,
IEnumerable<TValue> values
)
{
if (null == valueSelector)
throw new ArgumentNullException("valueSelector");
if (null == values)
throw new ArgumentNullException("values");
ParameterExpression p = valueSelector.Parameters.Single();
if (!values.Any())
return e => false;
var equals = values.Select(value =>
(Expression)Expression.Equal(
valueSelector.Body,
Expression.Constant(
value,
typeof(TValue)
)
)
);
var body = equals.Aggregate<Expression>(
(accumulate, equal) => Expression.Or(accumulate, equal)
);
return Expression.Lambda<Func<TElement, bool>>(body, p);
}
I'm not going to try to explain this method, other than to say it essentially builds a predicate expression for all the values using the valueSelector (i.e. p => p.Firstname) and ORs those predicates together to create an expression for the complete predicate.
For more information on the technique Colin uses here mosey on over to his blog and read this.
