Being IN in LINQ to SQL - Or How I learned to Love Contains (aka Getting Linq to SQL to generate a T-SQL WHERE clause that includes an IN)

So today was my first time really playing with Linq to SQL (or any flavor of Linq for that matter). I'm prototyping a new feature and wanted to see how Linq could help...

The biggest road block? Trying to figure out how to do a IN in Linq to SQL, i.e. SELECT * FROM table WHERE column IN ('value','another','etc')

Dynamically building OR's wasn't going to cut it. (Linq: ... WHERE C.Column = "Value" or C.Column = "another" or C.Column = "etc" )

And using a Lambda expression in the Linq Where was making my brain hurt.

There had to be a simple and easy way to do this...

Luckily through the power of the search I found a hint for a truly workable and clean solution. One that results in final SQL that includes an IN. The Blomsma Code - LINQ to SQL: Joining database data with non-database data

 

The magic is with the Contains and to turn the Where on its head.

Here's an example (see the screenshots below).  The code pretty much speaks for itself.

The key is the order in the Where clause. I kept looking for an another method, another way, a more T-SQL kind of thing. Some way to say emp.Country IN bla bla or emp.Country.Contains(bla, bla), etc. But nothing would work or result in an IN based SQL Query... That is until I found the hint I needed in the above post.

Now the below code works exactly as I wanted. It builds a T-SQL statement using IN in the WHERE clause, is clean, scalable and easy to understand...

The source code and project is also available here, http://code.msdn.microsoft.com/LinqtoSQLJoinExample (but it's all below too... Yep, that's it, that's all of it... )

Source

Imports System.Data.Linq
Imports System.Data.SqlClient

Public Class Form1




  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim db As New Northwind("Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf")

    Dim Countries() As String = Me.TextBox1.Text.Split(","c)

    Dim query = From emp In db.Employees _
                Where Countries.Contains(emp.Country) _
                Select emp

    db.Log = Console.Out

    Me.DataGridView1.DataSource = query

  End Sub

End Class

Screenshots

T-SQL Generated by Linq to SQL

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [USA]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0, @p1)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [USA]
-- @p1: Input String (Size = 0; Prec = 0; Scale = 0) [UK]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [UK]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0, @p1)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [France]
-- @p1: Input String (Size = 0; Prec = 0; Scale = 0) [UK]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

SELECT [t0].[Employee ID] AS [EmployeeID], [t0].[Last Name] AS [LastName], [t0].[First Name] AS [FirstName], [t0].[Title], [t0].[Birth Date] AS [BirthDate], [t0].[Hire Date] AS [HireDate], [t0].[Address], [t0].[City], [t0].[Region], [t0].[Postal Code] AS [PostalCode], [t0].[Country], [t0].[Home Phone] AS [HomePhone], [t0].[Extension], [t0].[Photo], [t0].[Notes], [t0].[Reports To] AS [ReportsTo]
FROM [Employees] AS [t0]
WHERE [t0].[Country] IN (@p0, @p1, @p2)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [France]
-- @p1: Input String (Size = 0; Prec = 0; Scale = 0) [UK]
-- @p2: Input String (Size = 0; Prec = 0; Scale = 0) [USA]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8