Linq to SQL Join Example

A example of creating a dynamic join of client data with a T-SQL statement.

The power of Contains and T-SQL IN

See this blog post, 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) for more information...

Details

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 Example

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