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. :)
