Entity Framework List Contains in lambda

Question

I want to query items with specific IDs using. For example:

var ids = new List<int> { 1, 3, 5 };

var items = context.Items.Where(item => ids.Contains(item.ID)).ToList();

Questions:

1. Will this generate a single query with SQL IN operator?
2. Is this code OK in terms of performance?
3. Are there any better ways to do it?

I am using Entity Framework 6 with Microsoft SQL Server.

Solution

1. Will this generate a single query with SQL IN operator?
   Yes
2. Is this code OK in terms of performance?
   Yes (for small lists)
3. Are there any better ways to do it?
   No (for small lists)

If the list is really big and the table is reasonably small you might get better performance bringing the complete table into memory and do an in memory join with the list.

Comments

[Giorgi]

SQL IN will be generated only if the provider supports it. Sql Server providers supports it but Oracle provider doesn't

[Douglas Gaskell]

For me this brings the entire table in, then grabs the values it needs from memory. Considering the table has ~15 million rows, this is crippling performance wise. It pretty much performs a SELECT * FROM query without an IN.

[Magnus]

@DouglasGaskell Perhaps you are using an older version of EF. Or using non SQL-Server provider.

[Douglas Gaskell]

@Magnus I'm using EF Core (7) right now. I may be missing this sort of functionality.

[yv989c]

Hi @DouglasGaskell. If you are using EF Core 3.1+, you may find my work useful: github.com/yv989c/BlazarTech.QueryableValues