In SQL Server, a SQL statement like 'NULL=NULL' evaluates to false. however 'NULL IS NULL' evaluates to true. So, for NULL values in your database columns, you need to use the 'IS' operator instead of the regular '=' operator.
The problem is, in Linq to SQL, there is no such 'IS' operator since 'IS' is already used as a C# language keyword. So, when you are invoking an equality check in your Linq to SQL where clause to a nullable column you need to be alert on this behavior.
For example, take the following sample code that I wrote to demonstrate this topic.
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.IO;
6
7 namespace ConsoleApplication1
8 {
9 class Program
10 {
11 static void Main(string[] args)
12 {
13 MyDataContext context = new MyDataContext();
14 context.Log = new ConsoleWriter();
15
16 string name = null;
17 var aff = from a in context.Affiliates
18 where
19 a.CompanyName == name
20 select a.ID;
21 var aff2 = from a in context.Affiliates where a.CompanyName == null select a.ID;
22
23 aff.ToList();
24 aff2.ToList();
25 }
26 }
27
28 class ConsoleWriter : TextWriter
29 {
30
31 public override Encoding Encoding
32 {
33 get { return Encoding.UTF8; }
34 }
35
36 public override void Write(string value)
37 {
38 base.Write(value);
39 Console.WriteLine(value);
40 }
41
42 public override void Write(char[] buffer, int index, int count)
43 {
44 base.Write(buffer, index, count);
45 Console.WriteLine(buffer, index, count);
46 }
47 }
48 }
In this code, I have attached a sample logger to my DataContext so that all my queries are logged. Now I ran two queries. Lets take a look at the first query and its logger output,
16 string name = null;
17 var aff = from a in context.Affiliates
18 where
19 a.CompanyName == name
20 select a.ID;
The logger output after executing this query is, as follows -
SELECT [t0].[ID]
FROM [dbo].[Affiliates] AS [t0]
WHERE [t0].[CompanyName] = @p0
-- @p0: Input VarChar (Size = 0; Prec = 0; Scale = 0) [Null]
So, you see that although a null is assigned in the variable 'name', the Linq to SQL generated query uses the '=' operator which may lead to undesired results.
However, the second query and its logger output looks like the following -
21 var aff2 = from a in context.Affiliates where a.CompanyName == null select a.ID;
SELECT [t0].[ID]
FROM [dbo].[Affiliates] AS [t0]
WHERE [t0].[CompanyName] IS NULL
Here, the generated query uses the 'IS' operator which is desirable.
In case, you want Linq to SQL to generate the first code using 'IS' operator, you may use something like the following one -
26 var aff3 = from a in context.Affiliates
27 where
28 ((name == null && a.CompanyName == null) || (a.CompanyName == name))
29 select a.ID;
This query produces the following SQL code -
SELECT [t0].[ID]
FROM [dbo].[Affiliates] AS [t0]
WHERE ([t0].[CompanyName] IS NULL) OR ([t0].[CompanyName] = @p0)
So, to end, whenever you are writing a where clause on a nullable column using Linq to SQL, make sure you know the consequences and take measures accordingly.
Happy coding!
