Entity Framework - attribute IN Clause usage

Question

I need to filter some Entities by various fields using "normal" WHERE and IN clauses in a query over my database, but I do not know how to do that with EF.

This is the approach:

Database table

Licenses
-------------
license INT
number INT
name VARCHAR
...

desired SQL Query in EF

SELECT * FROM Licenses WHERE license = 1 AND number IN (1,2,3,45,99)

EF Code

using (DatabaseEntities db = new DatabaseEntities ())
{
    return db.Licenses.Where(
        i => i.license == mylicense 
           // another filter          
        ).ToList();
}

I have tried with ANY and CONTAINS, but I do not know how to do that with EF.

How to do this query in EF?

possible duplicate of [How to do an "in" query in entity framework?](http://stackoverflow.com/questions/1137921/how-to-do-an-in-query-in-entity-framework) — Alex Angas, Apr 10 '14 at 01:52

score 89 · Accepted Answer · answered Nov 12 '12 at 11:29

89

int[] ids = new int[]{1,2,3,45,99};
using (DatabaseEntities db = new DatabaseEntities ())
{
    return db.Licenses.Where(
        i => i.license == mylicense 
           && ids.Contains(i.number)
        ).ToList();
}

should work

answered Nov 12 '12 at 11:29

Albin Sunnanbo

46,430
8
69
108

1

Thx!! I have tried just the opposite: i.number.Contains(...). I was going crazy... – unairoldan Nov 12 '12 at 11:43
I think there must be some expression black magic going on in the IQueryable for EF.NET to enable this... But really, you wouldn't expect this to work without moving to IEnumerable, first... they must be parsing the expression and looking for .Contains calls. – Sprague Sep 03 '13 at 09:58
1

@Sprague, When you use an `IQueryable` your code is compiled into an Expression Tree instead of ready to run IL code. EF analyzes the expression tree and translates it into SQL queries at runtime. The black magic to do so is required even for trivial queries, the `contains` trick here is not more special than any other pattern that the EF provider can identify and translate into SQL queries. – Albin Sunnanbo Sep 03 '13 at 19:03
@AlbinSunnanbo I suppose I was less doubting the capability to do this and more interested in the fact that they had. Many things remain unimplemented in IQueryable form. I suppose this speaks to the prevalence of the IN clause in SQL. the .Contains method of checking for set membership is fairly idiomatic, I think it's interesting that they've done it this way (interesting, but sensible.) – Sprague Sep 05 '13 at 09:45
3

`ids.Contains(i.number)` is not good when `ids` contains a huge number of elements. EF translate that to `...WHERE (1=number) AND(2=number) AND(3=number) AND (45=number) AND...`. it's realy bad with 10.000 elements – Salem May 20 '14 at 07:47
5

@Salem What makes you think that? Definitely not `AND`, `OR` would be a possibility, but it is translated into `number in (1,2,3,45,99)`. Although in EF5 and earlier the internal code was not efficient for more than lets say 1000 items, but from EF6 that is fixed. I have a query with 10000 items and it takes about 70ms in EF6.1. Not super fast (it is a really big query to parse and execute), but not particularly slow either. – Albin Sunnanbo May 22 '14 at 09:53
1

that's true Albin Sunnanbo, it is an `OR`. am using EF4, and it is a desaster when am implementing a List Contain. so to resolve this, sometime i do write SQL queries like this : `contxt.MyEntitySet.SqlQuery("SELECT... WHERE myCol IN ('" + string.Join("','", intersecList) + "')")` – Salem May 22 '14 at 11:21
For some performance talk see: [Why is .Contains slow? Most efficient way to get multiple entities by primary key?](http://stackoverflow.com/questions/8107439/why-is-contains-slow-most-efficient-way-to-get-multiple-entities-by-primary-ke) – Hossein Jun 19 '16 at 10:34

Entity Framework - attribute IN Clause usage

1 Answers1

Linked

Related