How to do a "where in values" in LINQ-to-Entities

Question

Does anybody know how to apply a "where in values" type condition using LINQ-to-Entities? I've tried the following but it doesn't work:

var values = new[] { "String1", "String2" }; // some string values

var foo = model.entitySet.Where(e => values.Contains(e.Name));

I believe this works in LINQ-to-SQL though? Any thoughts?

Answer 1

Yes it does translate to SQL, it generates a standard IN statement like this:

SELECT [t0].[col1] FROM [table] [t0] WHERE [col1] IN ( 'Value 1', 'Value 2')

Answer 2

Using the where method doesn't alway work

var results = from p in db.Products

             where p.Name == nameTextBox.Text

             select p;

Answer 3

Instead of doing it that way use the join method. It's somewhat difficult to understand without using the query operators, but once you get it, you've got it.

var foo = 
model.entitySet.Join(  //Start the join
values, //Join to the list of strings
e => e.Name, // on entity.Name
value => value, //equal to the string
(ModelItem ent, String str) => ent);//select the entity

Here it is using the query operators

var foo = from e in model.entitySet
join val in values on
e.Name equals val
select e;

Answer 4

It is somewhat of a shame that Contains is not supported in Linq to Entities.

IN and JOIN are not the same operator (Filtering by IN never changes the cardinality of the query).

Answer 5

Contains is not supported in EF at this time.

Answer 6

FYI:

If you are using ESql you are able to use in operation. I don't have VS 2008 With me but code should be something like following:

var ids = "12, 34, 35";
using (context = new Entites())
{
    var selectedProducts = context.CreateQuery<Products>(
        String.Format("select value p from [Entities].Products as p 
                       where p.productId in {{{0}}}", ids)).ToList();
    ...
}