LINQ-to-SQL IN ()

Question

Currently I use a block of code like this, to fetch a set of DB objects with matching IDs.

List<subjects> getSubjectsById(List<long> subjectIDs){

  return ctx.tagSubjects.Where(t => subjectIDs.Contains(t.id)).ToList();
}

But this is really inefficient, because it requires the entire table to be read from the database and then filtered inside of C#.

What I would rather do would be something the equivelent of:

SELECT * FROM subjects WHERE subjects.id IN (1,2,3,4,5,...);

The big difference is that in the first example the filtering is happening inside the C# code, and in the second the filtering is done on the SQL server (where the data is).

Is there a [better] way to do this with LINQ?

Answer 1

Where did you find out that it downloads the entire table from SQL Server?

I'm sure it does what you want. It translates the query to a parameterized IN clause like:

... IN (@p1, @p2, @p3)

and passes the contents of the list as values to those parameters. You can confirm this with tools such as SQL Profiler and LINQ to SQL debugger visualizer or set the DataContext.Log property to console (before executing the query) and read the generated SQL:

dataContext.Log = Console.Out;