WHERE IN statement in Entity Framework?

danielliu8 · Post by **danielliu8** » Wed 23 Sep 2009 19:31

Hi,

Is it anyway to accomplish the same functionality as a SQL WHERE IN statement in Entity Framework?

Like:
SELECT *
FROM Foo
WHERE blah IN (1, 3, 5, 7)

I tried following linq, but not working well:
var query = from e in context.tblFACT
join id in ids on
e.ID equals id
select e;

The error we received was:

"Unable to create a constant value of type 'System.Collections.Generic.IEnumerable`1'. Only primitive types (for instance Int32, String and Guid) are supported in this context."

Thanks for any help.

AndreyR · Post by **AndreyR** » Thu 24 Sep 2009 08:15

There is no simple solution using LINQ to Entities - it has some limitations.
You can use Entity SQL supporting the IN clause.

Code: Select all

string entitySql = String.Format("SELECT VALUE O FROM Foo AS O WHERE O.Id IN {{{0}}}", String.Join(",", blahsCollection.ToList().ConvertAll(blah => blah.ToString()).ToArray()));
ObjectQuery q = new ObjectQuery(entitySql, context);

danielliu8 · Post by **danielliu8** » Thu 24 Sep 2009 21:25

Thanks, but your sample code is not working, here is the error:

Cannot create an instance of the abstract class or interface 'System.Data.Objects.ObjectQuery'

Would you please provide the correct code sample?

thanks

AndreyR · Post by **AndreyR** » Fri 25 Sep 2009 07:01

I have omitted the type. Use the following code:

Code: Select all

ObjectQuery q = new ObjectQuery(entitySql, context);

danielliu8 · Post by **danielliu8** » Fri 25 Sep 2009 18:10

O.K., here is the code:

string entitySql = String.Format("SELECT VALUE p FROM context.GIVING_FACT_TOTAL_BYUNITYEAR AS p WHERE p.BUSINESS_UNIT IN {{{0}}}", String.Join(",", units.ToList().ConvertAll(e => e.ToString()).ToArray()));
ObjectQuery query = new ObjectQuery(entitySql, context);

And Here is the error:

base {System.Data.EntityException} = {"The query syntax is not valid., near term '}', line 2, column 0."}

Would you help?

thanks

AndreyR · Post by **AndreyR** » Mon 28 Sep 2009 08:32

Please send me a small test project reproducing the problem.
We will investigate the situation.

wgkwvl · Post by **wgkwvl** » Tue 07 Jun 2011 11:09

Hi, I get the exact same error in my log files,
when executing :
IEnumerable query = _context.Afda_Zorgen
.Include("Afda_Werknemers")
.Include("Afda_Adressen")
.Where("it.Zorg_Id IN {" + string.Join(",", ids) + "}");

For the moment i cannot reproduce it tho, since the users havent been able to tell me what they were doing at the time .
ids is a list of strings representing the zorg_id which in itself is int 64

how was this error solved ?

AndreyR · Post by **AndreyR** » Tue 07 Jun 2011 13:52

We are unable to reproduce the issue.
I recommend you to use the lambda expression with Contains instead:

Code: Select all

IEnumerable query = _context.Afda_Zorgen 
 .Include("Afda_Werknemers") 
 .Include("Afda_Adressen") 
 .Where(it => ids.Contains(it.Zorg_Id));

This code will generate the "... Where ... In ..." statement as well.
Refer to this article for details.