środa, 14 stycznia 2009

WHERE IN clause in Linq To Entities

Recently I found nice solution to dynamically build Linq Expression with WHERE IN clause. Listing 1. Function
        static Expression<Func<TElement, bool>> BuildContainsExpression<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();

            // p => valueSelector(p) == values[0] || valueSelector(p) == ...
            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);
        }
Listing 2. Calling BuildContainsExpression function:
List<string> senderList = new List<string>();
senderList.Add("Jacek");
senderList.Add("Krystian");

using (EntityDataModel edm = new EntityDataModel())
{
       var query = edm.senders.Where(e => e.StartDate > DateTime.Now);
       query = query.Where(BuildContainsExpression<PrListing, string>(e => e.sendernm, sendersList));
}
This builds SQL expression like this: Listing 3. SQL Example
SELECT * FROM senders WHERE (sendernm = 'Jacek' || sendernm = 'Krystian') 

Brak komentarzy: