Thursday, April 23, 2009

How to make a T-SQL IN query with LINQ to SQL

If you would like to find all rows in a table where a value in a column matches any value in a list, how do you do that in LINQ to SQL?

In this example I have a generic list of integers.
List<int> intList = new List<int>(){ 1, 2, 3, 4, 5 };

You have to write the LINQ query so that you match all values in the list against the column and not the column against the list.
var result = from f in context.FirstTables
where intList.Contains(f.ColumnA)
select f;

The T-SQL generated:
FROM [dbo].[FirstTable] AS [t0]
WHERE [t0].[ColumnA] IN (@p0, @p1, @p2, @p3, @p4)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [4]
-- @p4: Input Int (Size = 0; Prec = 0; Scale = 0) [5]

No comments:

Post a Comment