How to query nullable fields using linq

Querying nullable fields using linq is a tricky task. Let's create a simple sql table and start some testing using linqpad
Our table schema should look like the following;

USE [TestData]
GO
 
/****** Object:  Table [dbo].[Patterns]    Script Date: 05/27/2011 09:20:12 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Patterns](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ParentID] [int] NULL,
	[PatternName] [nvarchar](150) NOT NULL,
	[CreatedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_Patterns] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
/****** insert some test data ******/
insert into patterns values(2,'Pattern 1',null)
insert into patterns values(2,'Pattern 2',null)
insert into patterns values(3,'Pattern 3','Cathy')
insert into patterns values(4,'Pattern 4',null)
insert into patterns values(5,'Pattern 5',null)
insert into patterns values(null,'Pattern 6','Joe')
insert into patterns values(null,'Pattern 7',null)

Now we have our table and data in place,lets create a method to query our data

void QueryPatternsWithNullCreatedBy(string userName)
{
    using (var context = new PatternContext())
    {
        var patterns = from x in context.Patterns
                       where x.CreatedBy == userName
                       orderby x.Id
                       select x;                                                                                                                                      
        patterns.Dump(); //Dump() method is a linqpad extension method
    }
}

Call number 1 will send null as the userName param

QueryPatternsWithNullCreatedBy(null);

Why aren't we getting any data returned? Whatever happened to the five rows we just inserted with null CreatedBy value?

Let's examine the generated SQL to see what happened

DECLARE @p0 NVarChar(1000) = null
SELECT [t0].[Id], [t0].[ParentID], [t0].[PatternName], [t0].[CreatedBy]
FROM [Patterns] AS [t0]
WHERE [t0].[CreatedBy] = @p0
ORDER BY [t0].[Id]

Here is our aha moment, the generated SQL is querying the string literal 'null' (Look at the first line in the above snippet). NULL is not a value and we cannot compare equality.

To get linq to change the generated SQL and use Is Null, we can change the query in our method to the following:

void QueryPatternsWithNullCreatedBy(string userName)
        {
            using (var db = new PatternContext())
            {
                var patterns = from x in db.Patterns
                               where object.Equals(x.CreatedBy, userName)
                               orderby x.Id
                               select x;                                                                                                                                                                                                                                                        
                patterns.Dump();
            }
        }

Now we see our generated SQL is creating the correct query

SELECT [t0].[Id], [t0].[ParentID], [t0].[PatternName], [t0].[CreatedBy]
FROM [Patterns] AS [t0]
WHERE [t0].[CreatedBy] IS NULL
ORDER BY [t0].[Id]

Call number 2:

QueryPatternsWithNullCreatedBy("joe");

Will return a single row as expected using both queries.

The lesson here is to always check the generated SQL when using any ORM

Add comment