LINQ to SQL and the IN Clause
Dec 22, 2011
No Comments
As I am continuing to convert queries from T-SQL to LINQ (to SQL), I came across a scenario where I am using an IN query to pull back a result set where a criteria is within a given range of values. Here is the original SQL:
SELECT DISTINCT File_Format.File_Format_Key, File_Format_Name, Exit_Call_Routine.Exit_Call_Routine_Key, Exit_Call_Routine.Exit_Call_Routine_Text
FROM File_Format
INNER JOIN Exit_Call on File_Format.File_Format_key = Exit_Call.File_Format_Key
INNER JOIN Exit_Call_Routine on Exit_Call.Exit_Call_Routine_Key = Exit_Call_Routine.Exit_Call_Routine_Key
WHERE Exit_Call.Exit_Call_Routine_Key IN
(
1, 2, 4, 7
)
I ran into a couple of things when I tried converting this to LINQ. The first was how I was going to do an IN query in LINQ…after finding this article and this one, I ended up with this:
var k = new int[] {1, 2, 4, 7};
var e = (from f in File_Formats
join ec in Exit_Calls on f.File_Format_Key equals ec.File_Format_Key
join er in Exit_Call_Routines on ec.Exit_Call_Routine_Key equals er.Exit_Call_Routine_Key
where k.AsEnumerable().Contains((int)ec.Exit_Call_Routine_Key)
select new {name = f.File_Format_Name}).Distinct();
e.Dump();
What we’re doing here is putting our range of values into an array. We then kind of flip the where statement around, running the it against the array using the criteria field (Exit_Call_Routine_Key) value vs. running the where statement against the table itself. Here is the SQL that this LINQ code generates:
-- Region Parameters
DECLARE @p0 Int SET @p0 = 1
DECLARE @p1 Int SET @p1 = 2
DECLARE @p2 Int SET @p2 = 4
DECLARE @p3 Int SET @p3 = 7
-- EndRegion
SELECT DISTINCT [t0].[File_Format_Name] AS [name]
FROM [File_Format] AS [t0]
INNER JOIN [Exit_Call] AS [t1] ON ([t0].[File_Format_Key]) = [t1].[File_Format_Key]
INNER JOIN [Exit_Call_Routine] AS [t2] ON [t1].[Exit_Call_Routine_Key] = ([t2].[Exit_Call_Routine_Key])
WHERE (CONVERT(Int,[t1].[Exit_Call_Routine_Key])) IN (@p0, @p1, @p2, @p3)
I know this may be a little confusing—it took me a few passes reading and comparing these two statements to get it, and I will probably have to edit this explanation a few times in the future as well to make it clearer for you, the reader. But, if it’s still a puzzle to you, just keep the mental processing going, and check out the articles I linked above to get another take on this issue.
