ASP.NET MVC and LINQ to SQL – LINQPad to the Rescue

Recently I was rewriting  a broken algorithm in some ASP.NET MVC code.  For part of the fix I needed to write some LINQ to SQL. I needed to do a comparison against a nullable field but I was thinking in C# not in SQL. Here is a simplified version of the real problem. Suppose we have a table of customers where LastName is a nullable field. We want to return customers whose last name is NULL.

string lastName = null;

var customers =
    from c in Customers
    where c.LastName == lastName
    select c;

This was unexpectedly returning no values. The correct solution is

var customers =
    from c in Customers
    where c.LastName == null
    select c;

The reason was due to the “IS NULL vs. = NULL”  issue in the underlying SQL. This was easy to check by launching the excellent LINQPad application that allows you to view the underlying SQL after running a LINQ statement. I’d just assumed that from the C#/LINQ side it would automatically generate the appropriate code.

Comparing directly against null from the C# generates

SELECT [t0].[CustomerID], [t0].[FirstName], [t0].[LastName]

FROM [Customers] AS [t0]

WHERE [t0].[LastName] IS NULL

Comparing against a variable that’s null generates

DECLARE @p0 VarChar(1000) = null

SELECT [t0].[CustomerID], [t0].[FirstName], [t0].[LastName]

FROM [Customers] AS [t0]

WHERE [t0].[LastName] = @p0

In my real problem I was comparing against a nullable ID column and I wanted to return both null and non-null matches in one clause of a compound where. So I wanted to do a more elaborate version of this.

string name = (lastName != null) ? "Fred" : null;

var customers =
    from c in Customers
    where c.LastName == name
    select c;

But I was forced to use an if-else instead.

if (lastName != null)
{
    var customers =
        from c in Customers
        where c.LastName == lastName
        select c;
    // ...more code
}
else
{
    var customers =
        from c in Customers
        where c.LastName == null
        select c;
    // ...more code
}

I couldn’t think of a more concise way around this. Anyway, at least I solved the bigger problem of which this was a part. :)