LINQ To SQL: Dynamic IN Clause

http://dotnet.org.za/hiltong/archive/2008/03/12/linq-to-sql-dynamic-in-clause.aspx

One of the things that we need to do every now and then is be able to select from a table based on a specific list of primary key IDs. For instance, we may have a UI with checkboxes that allow users to select specific products. We would then retrieve the ids for the selected products and build this into a list that we would want to make use of the SQL query, something like:

SELECT * FROM Products WHERE ProductID IN (1, 2, 3, 4)

There are a few ways to do this, including

1. building dynamic SQL in code
2. building dynamic SQL in a sproc (and using the 'exec' command)
3. using T-SQL functions (like COALESCE or ISNULL)

The first instance, building dynamic SQL in code, is pretty ugly, because it involved hard-coding portions of the SQL itself, like this:

public List<Product> GetProducts(List<int> productIDs)
{
        string SQL = "SELECT * FROM Products WHERE ProductID IN (";

        foreach (int productID in productIDs)
        {
            SQL += productID + ", ";
        }

        if (SQL.EndsWith(", ")) SQL = SQL.Substring(0, SQL.Length - 2);

        SQL += ")";

        //...Execute the sql...
}

The 2nd approach can be solved within a stored procedure using the Exec or sp_executesql commands, like so:

Create Procedure Search
    @strIDs VarChar(100)
AS 

Declare @SQL VarChar(1000) 

Select @SQL = 'SELECT * FROM Products '
Select @SQL = @SQL + 'WHERE ProductID in (' + @strIDs +')' 

Exec ( @SQL) 

GO

But this is still quite ugly, because we still need to build a string of SQL, we need certain security rights, and we're limited to the length of string that Exec or sp_executesql allow.

The third approach is probably the cleanest, most secure, and most structured. Here is an example:

Create Procedure sp_EmployeeSelect_Coalesce
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
        
AS
      Set NoCount ON
 
    Select * From tblEmployees 
    where EmployeeName = Coalesce(@EmployeeName, EmployeeName) AND
        Department = Coalesce(@Department, Department ) AND
        Designation = Coalesce(@Designation, Designation) AND
        JoiningDate >= Coalesce(@StartDate, JoiningDate) AND 
        JoiningDate <= Coalesce(@EndDate, JoiningDate) AND
        Salary >= Coalesce(@Salary, Salary)

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

(the example is from this CodeProject article, and here's another similar technique from SQLTeam.com). The problems with this approach though are that you have to code for each field by hand, and it also won't do a dynamic list, which is what we're trying to achieve.

The LINQ To SQL Where In Version

In LINQ To SQL, we now have a new approach to solving this problem. We can make use of the list parameter directly, and LINQ To SQL will generate the SQL for us. This means we can write the following:

public List<Product> GetProducts(List<int> productIDs)
{
    using (NorthwindDataContext dc = new NorthwindDataContext())
    {
        var products = from p in dc.Products
                       where productIDs.Contains(p.ProductID)
                       select p;

        return products.ToList();
    }
}

And have it generate the following SQL:

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID
], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnO
rder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductID] IN (@p0, @p1, @p2, @p3)
-- @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]

It's a little bit different in terms of the structuring because we're used to specifying the table's column first, then the list, whereas here we need to specify the list.Contains(), but I guess we're already getting used to the From .. Where .. Select ;->