Use the new LINQ Contains extension method for the SQL IN clause by ThinqLinq

Use the new LINQ Contains extension method for the SQL IN clause

For a while, users were requesting a way to map the SQL "IN" clause as in the following:

SELECT Customer.Name
FROM Customer
WHERE Customer.State IN ('GA', 'FL', 'AL')

Prior to Beta 2, this would mean either separate OR clauses for each item, or worse yet a heterogeneous join between the database and an in memory array. Why do I say, worse yet? Because the heterogeneous join option meant bringing all of the records from the database and filtering on the client.

Beta 2 introduced a new "Contains" extension method which converts to the TSQL "IN" clause. Below is an example that I put together for one of my first production apps that is already live.

In this case, I fetch a list of items that the user has selected in a CheckBoxList and use the values from that for my IN clause. The selected items are declared as follows:

Dim channels = From channel As ListItem In Me.CheckBoxList1.Items _
               Where channel.Selected _
               Select CStr(channel.Value)

Next, we need to set-up a lambda expression that will serve as a filtered table set in my join clause.

Dim companies = dc.Companies.Where(Function(c As Company) channels.Contains(c.Channel))

Here I am identifying an IQuerable<Company> which consists of the company table filtered by the declared function. In case this syntax is new to you, the "Function" clause is VB's implementation of a Lambda expression. I'll post more on this soon. Basically, the function states that, given a company that we will call "c", return true if the channels collection contains the channel for this company.

Because of the IQuerable composability, we can then leverage this object as part of a larger query as follows:

Dim users As IEnumerable(Of String) = From user In dc.Users _
   Join company In companies On user.CompanyId Equals company.CompanyId _
   Order By user.UserName _
   Select user.UserName Distinct

Here's the great part. When we look at the generated SQL, not only is the Contains expression translated into the IN clause, but the separate portions of the query are pieced together into a single query. Take a look:

FROM [dbo].[User] AS [t0] INNER JOIN 
     [dbo].[Company] AS [t1] ON [t0].[CompanyId] = [t1].[CompanyId]
WHERE (CONVERT(NVarChar(MAX),[t1].[Channel])) IN (@p0, @p1)

Notice also, the VB team has added a number of query expressions that are not available in C#. This query uses the Distinct expression at the end of the select clause which is likewise translated directly as part of the TSQL select statement. You no longer have to call the .Distinct method on the query to get the desired results, it is now a native query expression.

Ah, feel the VB love...

Posted on - Comment
Categories: LINQ) - VB) -
comments powered by Disqus