How do you do a SQL style 'IN' statement in LINQ to Entities (Entity Framework) if Contains isn't supported?

Question

I'm using LINQ to Entities (not LINQ to SQL) and I'm having trouble creating an 'IN' style query. Here is my query at the moment:

var items = db.InventoryItem
                .Include("Kind")
                .Include("PropertyValues")
                .Include("PropertyValues.KindProperty")
                .Where(itm => valueIds.Contains(itm.ID)).ToList<InventoryItem>();

When I do this however, the following exception is thrown:

LINQ to Entities does not recognize the method 'Boolean Contains(Int64)' method, and this method cannot be translated into a store expression.

Does anyone have a workaround or another solution for this?

Answer 1

You need to either use this one:

.Where(string.Format("it.ID in {0}", string.Join(",", valueIds.ToArray())));

or construct the WHERE part dynamically, as in this post.

Answer 2

Someone had created an expression for this scenario...I found it and hijacked it but can't remember where I found the original source. I'll post the code in a bit. For now this is a marker for me to come back.

Answer 3

My workaround is to convert the entities result to a List and after that apply the Contains().

Example:

var items = db.InventoryItem
                .Include("Kind")
                .Include("PropertyValues")
                .Include("PropertyValues.KindProperty")
                .ToList()
                .Where(itm => valueIds.Contains(itm.ID));