Take the 2-minute tour ×

What's the most efficient way to select multiple entities by primary key?

public IEnumerable<Models.Image> GetImagesById(IEnumerable<int> ids)

    //return ids.Select(id => Images.Find(id));       //is this cool?
    return Images.Where( im => ids.Contains(im.Id));  //is this better, worse or the same?
    //is there a (better) third way?


I realise that I could do some performance tests to compare, but I am wondering if there is in fact a better way than both, and am looking for some enlightenment on what the difference between these two queries is, if any, once they have been 'translated'.

share|improve this question
Well, thats indeterminate, usually not a huge number, but would like to be able to support large numbers in a scalable fashion. –  Tom Nov 12 '11 at 22:25
add comment

2 Answers

up vote 46 down vote accepted

Using Contains in Entity Framework is actually very slow. It's true that it translates into an IN clause in SQL and that the SQL query itself is executed fast. But the problem and the performance bottleneck is in the translation from your LINQ query into SQL. The expression tree which will be created is expanded into a long chain of OR concatenations because there is no native expression which represents an IN. When the SQL is created this expression of many ORs is recognized and collapsed back into the SQL IN clause.

This does not mean that using Contains is worse than issuing one query per element in your ids collection (your first option). It's probably still better - at least for not too large collections. But for large collections it is really bad. I remember that I had tested some time ago a Contains query with about 12.000 elements which worked but took around a minute even though the query in SQL executed in less than a second.

It might be worth to test the performance of a combination of multiple roundtrips to the database with a smaller number of elements in a Contains expression for each roundtrip.

This approach and also the limitations of using Contains with Entity Framework is shown and explained here:

Why does the Contains() operator degrade Entity Framework's performance so dramatically?

It's possible that a raw SQL command will perform best in this situation which would mean that you call dbContext.Database.SqlQuery<Image>(sqlString) or dbContext.Images.SqlQuery(sqlString) where sqlString is the SQL shown in @Rune's answer.


Here are some measurements:

I have done this on a table with 550000 records and 11 columns (IDs start from 1 without gaps) and picked randomly 20000 ids:

using (var context = new MyDbContext())
    Random rand = new Random();
    var ids = new List<int>();

    Stopwatch watch = new Stopwatch();

    // here are the code snippets from below

    var msec = watch.ElapsedMilliseconds;

Test 1

var result = context.Set<MyEntity>()
    .Where(e => ids.Contains(e.ID))

Result -> msec = 85.5 sec

Test 2

var result = context.Set<MyEntity>().AsNoTracking()
    .Where(e => ids.Contains(e.ID))

Result -> msec = 84.5 sec

This tiny effect of AsNoTracking is very unusual. It indicates that the bottleneck is not object materialization (and not SQL as shown below).

For both tests it can be seen in SQL Profiler that the SQL query arrives at the database very late. (I didn't measure exactly but it was later than 70 seconds.) Obviously the translation of this LINQ query into SQL is very expensive.

Test 3

var values = new StringBuilder();
values.AppendFormat("{0}", ids[0]);
for (int i = 1; i < ids.Count; i++)
    values.AppendFormat(", {0}", ids[i]);

var sql = string.Format(
    "SELECT * FROM [MyDb].[dbo].[MyEntities] WHERE [ID] IN ({0})",

var result = context.Set<MyEntity>().SqlQuery(sql).ToList();

Result -> msec = 5.1 sec

Test 4

// same as Test 3 but this time including AsNoTracking
var result = context.Set<MyEntity>().SqlQuery(sql).AsNoTracking().ToList();

Result -> msec = 3.8 sec

This time the effect of disabling tracking is more noticable.

Test 5

// same as Test 3 but this time using Database.SqlQuery
var result = context.Database.SqlQuery<MyEntity>(sql).ToList();

Result -> msec = 3.7 sec

My understanding is that context.Database.SqlQuery<MyEntity>(sql) is the same as context.Set<MyEntity>().SqlQuery(sql).AsNoTracking(), so there is no difference expected between Test 4 and Test 5.

(The length of the result sets was not always the same due to possible duplicates after the random id selection but it was always between 19600 and 19640 elements.)

Edit 2

Test 6

Even 20000 roundtrips to the database are faster than using Contains:

var result = new List<MyEntity>();
foreach (var id in ids)
    result.Add(context.Set<MyEntity>().SingleOrDefault(e => e.ID == id));

Result -> msec = 73.6 sec

Note that I have used SingleOrDefault instead of Find. Using the same code with Find is very slow (I cancelled the test after several minutes) because Find calls DetectChanges internally. Disabling auto change detection (context.Configuration.AutoDetectChangesEnabled = false) leads to roughly the same performance as SingleOrDefault. Using AsNoTracking reduces the time by one or two seconds.

Tests were done with database client (console app) and database server on the same machine. The last result might get significantly worse with a "remote" database due to the many roundtrips.

share|improve this answer
I am starting to think that ideally, from a performance point of view, this type of query is something that should simply be avoided. –  Tom Nov 13 '11 at 1:11
@Tom: I did some tests, see my Edit. –  Slauma Nov 13 '11 at 1:46
@Tom: I've done a test number 6, see my Edit 2. Yes, avoiding this situation in the first place seems a good strategy. But sometimes you might just need such a query and can't circumvent it. My conclusion is more: Sometimes using raw SQL makes sense or is even a MUST for performance reasons with an ORM like Entity Framework. –  Slauma Nov 13 '11 at 13:09
@Tom: +1 for your question now, I forgot it. Because I learned a lot and it helped me to detect a serious performance bottleneck in an own application. Thanks for the question :) –  Slauma Nov 13 '11 at 13:25
Great answer and research. A super minor note, but you can build the list of IDs in Test 3 in one line like this: string values = string.Join(", ", ids); –  ThisGuy Dec 29 '13 at 7:56
show 2 more comments

The second option is definitely better than the first. The first option will result in ids.Length queries to the database, while the second option can use an 'IN' operator in the SQL query. It will basically turn your LINQ query into something like the following SQL:

FROM ImagesTable
WHERE id IN (value1,value2,...)

where value1, value2 etc. are the values of your ids variable. Be aware, however, that I think there may be an upper limit on the number of values that can be serialized into a query in this way. I'll see if I can find some documentation...

share|improve this answer
Thanks, is this the way to go then do you think? Or is there an alternative approach? –  Tom Nov 12 '11 at 20:52
@Tim: This is definitely the way to go. As long as you're on EF 4+, you can use this, and get a single fetch to the db... –  Reed Copsey Nov 12 '11 at 20:55
I think this is the way to go - I am not aware of a better approach. I'll see if I can find that size limit documented somewhere. If it exists, you can just divide ids into chunks of an appropriate size. If your collection of ids is known to be small, you may even be able to just ignore the issue. I believe the limit is something like 512 or 1024... –  Rune Nov 12 '11 at 20:56
Hmm, can't find that upper limit documented anywhere. Maybe I am mistaken and it only applied to Linq2SQL. I'll upvote anybody who documents (possibly, the lack of) that upper limit :-) –  Rune Nov 12 '11 at 21:05
There's no limit in EF since it converts it to a non-parameterized IN expression (column IN (val1, val2, val3, ...)) whereas LINQ-to-SQL parameterizes all the IN values (column IN (@p1, @p2, @p3, ...)) and so you hit the 2100 parameter limit pretty quickly. –  Allon Guralnek Nov 12 '11 at 22:19
show 4 more comments

Your Answer


