How does Linq to SQL perform when using List.Contain()?

Question

I have to select some items from a database. The IDs of the items are in a List.

In an answer here, Jon Skeet states that we might use Contains()

var foo = channel.AsQueryable<CodeData>()
                 .Where(codeData => codeIDs.Contains(codeData.CodeId));

In a context where the database is huge and the IDs list is really small, how does it perform?

Is Linq smart enough to use the IN clause in SQL?

Edit: I am using DataContext and SQL Server 2008

Are you using SQL Server? Try to use SQL Server profiler to see the generated SQL — Rui Jarimba, Nov 26 '12 at 11:59
The performance mainly depends on your data and which indexes are existing — Jan, Nov 26 '12 at 11:59
As far as I know, EF is smarter with this case than old Linq to SQL. — EgorBo, Nov 26 '12 at 12:01
@Jan: it will depend on the data and indexes AND the query executed — Rui Jarimba, Nov 26 '12 at 12:02
@RuiJarimba thanks for the idea. I sadly don't have SQL Server Profiler on my machine. (And the production servers are not up yet :)) — Jonas Schmid, Nov 26 '12 at 17:29
You can do it without a profiler: http://stackoverflow.com/questions/4899974/how-to-view-linq-generated-sql-statements — Pleun, Nov 27 '12 at 21:45

score 7 · Accepted Answer · answered Nov 26 '12 at 12:07

Yes, LINQ 2 SQL would use the IN clause in this scenario.

But not because it is "smart" and detects that you have a huge DB and a small id list.

The IN clause is how LINQ 2 SQL "knows" to express the List.Contains in SQL.

In fact, if you had a list of 1000 items you'll see an IN clause with 1000 parameters.

If you have a list of more than 2100 ids, you'll hit a limitation in the SQL Client that does not allow more than 2100 parameters on a command. But LINQ 2 SQL would happily generate a command that has, for example, even 5000 parameters.

How does Linq to SQL perform when using List.Contain()?

1 Answers1