ASP.NET Tips #65 - Don't overlook 'WHERE IN' style LINQ to SQL Queries

Entity Framework is smart enough to convert the Contains() operator on LINQ queries to WHERE IN (...) in SQL. But there is a hidden problem: Giving a data set length of greater than around 10,000 records in a WHERE IN (...) clause will significantly degrade the performance of the query generation and query execution:

var ids = new int[]{0,1, 2,3,4,5,6,7,8,9,10........99995, 99996,99997,99998,99999};

var matches = (from person in people
               where ids.Contains(person.Id)
               select person).ToArray();

The above statement generates the following fat SQL query:

SELECT * FROM PERSON WHERE ID IN
   (0,1,2,3,4,5,6,7,8,9,10.....,99995,99996,99997,99998,99999)

It is advisable therefore to send data in batches. 500 records per batch, for example, would yield a significant improvement in performance, but you should do benchmarking to see what works for you.