Sohan's Blog

Living the Developer's Life

Comparing With NULL in Where Clause Using Linq to SQL

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!

Comments

Technoverloaded
If you don’t know beforehand whether or not the variable “name” is null or not, you might want to try the Equals method.

For example, in the AdventureWorks database:

var query = from e in Employees
where Object.Equals(e.ManagerID, pManagerID)
select e.EmployeeID ;

If pManagerID == null, then LINQ to SQL generates

SELECT [t0].[EmployeeID]
FROM [HumanResources].[Employee] AS [t0]
WHERE [t0].[ManagerID] IS NULL

but if pManagerID contains a value, e.g. 109, then the SQL generated is

SELECT [t0].[EmployeeID]
FROM [HumanResources].[Employee] AS [t0]
WHERE ([t0].[ManagerID] IS NOT NULL) AND ([t0].[ManagerID] = @p0)
– @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [109]
merlin981
Great article. It is very important for developers new to LINQ to understand these ramifications. Thanks for demonstrating how the underlying code differs based on column type.

LINQ Exchange - Learn LINQ and Lambda Expressions