Collection-valued parameters with The Entity Framework ?

Question

Hi,

In my last project i decided to use Entity Framework and it was all going well until i try to get datas with "where in", i got an error.

After a tiny search i ve come up with this post and that post.

This is what i am trying to do

var all = fooelements
              .Where(l=>controlsToGet
                            .Contains(l.Control.Name));

Is there any to handle it with lambda expressions or linq with the Entity Framework ?

Thank you

Answer 1

I don't know of a way to generate a WHERE IN clause with EF, but you can use expression trees to construct a WHERE clause that will test for each of your values:

    Expression<Func<FooEntity, bool>> seed = l => false;
    var predicate = controlsToGet.Aggregate(seed,
        (e, c) => Expression.Lambda<Func<DataEventAttribute, bool>>(
            Expression.OrElse(
                Expression.Equal(
                    Expression.Property(
                        Expression.Property(
                            e.Parameters[0],
                            "Control"),
                        "Name"),
                    Expression.Constant(c)),
                e.Body),
            e.Parameters));

    var all = fooelements.Where(predicate);

If you print out predicate, you should see an expression like this:

l => ((l.Control.Name = ctrl5) || l.Control.Name = ctrl4 || ... || False )

Answer 2

Building upon the previous answer I have a tip that makes it easy to do this here:

Tip 8 - How to write 'WHERE IN' style queries using LINQ to Entities

Hope this helps

Alex James

Program Manager - Entity Framework Team

Entity Framework Tips