Thursday 18 November 2010

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