The way Linq to Sql deals with nulls had me scratching my head initially.
At first things seemed simple - for example the following does exactly what you would expect (Mike Taulty goes into more details on why here):
Customers.Where(c => c.Region==null)
This resolves to the following SQL:
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address],
[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [Customers] AS [t0]
WHERE [t0].[Region] IS NULL
Notice that the Where clause uses IS NULL rather than = null which is correct. However if I pass a null parameter to the query then the SQL is resolved incorrectly. For example:
string region=null;
Customers.Where (c => c.Region==region);
This resolves to the following SQL which will never return any records when the parameter is null because it uses = instead of IS:
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address],
[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [Customers] AS [t0]
WHERE [t0].[Region] = @p0
The Answer is Object.Equals
The answer is to use the static Object.Equals method in the Linq expression:
string region=null;
Customers.Where (c => Object.Equals(c.Region,region))
Which causes the SQL to be correctly created again:
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address],
[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [Customers] AS [t0]
WHERE [t0].[Region] IS NULL
Cheers
Ian