Devart Forums

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.

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

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

I have omitted the type. Use the following code:

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

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

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 ?

We are unable to reproduce the issue.
I recommend you to use the lambda expression with Contains instead: This code will generate the "... Where ... In ..." statement as well.
Refer to this article for details.