LINQ Query Optimization - How to Improve Runtime?

Question

I am wondering if there is a good way to optimize my LINQ queries. I am taking data from the database using a LINQ query similar to the following:

// PKs is a list of integers   
var import = context.table.Where(x => PKs.Contains(x.PrimaryKey)).AsEnumerable();

I assumed (incorrectly) that this would result in an SQL call, as shown below:

SELECT * from table where PrimaryKey in (PK[1], PK[2], ...)

ie 1 hit the database. Using Glimpse to check SQL calls, I saw that the LINQ query has been translated to many individual SQL calls, for example:

SELECT * from table where PrimaryKey=PK[1]
SELECT * from table where PrimaryKey=PK[2]
...

Individual queries were executed very quickly, but since my list of integers was large (in thousands), the bias due to overhead caused the query to take about 25 seconds to run.

I decided to strip the time-consuming LINQ queries with database calls and import the data using a stored procedure call:

var import = this.p_import(parameter).ToList();

, , 25 1 .

, , LINQ, , . LINQ?

Answer 1

, .

context.table.Join(PKs,t => t.PrimaryKey, pk => pk, (t, pk) => t).AsEnumerable()

Linq2Sql POCO, EF, .

Linq2Sql Contains, .

Answer 2

EF 4 IN-. Contains in EF, , .