LINQ Exchange

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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            MyDataContext context = new MyDataContext();
            context.Log = new ConsoleWriter();
            string name = null;

            var aff = from a in context.Affiliates
                      where a.CompanyName == name
                      select a.ID;

            var aff2 = from a in context.Affiliates where a.CompanyName == null select a.ID;

            aff.ToList();
            aff2.ToList();
        }
    }
}

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,

string name = null;

var aff = from a in context.Affiliates
             where a.CompanyName == name
             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 -

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 -

var aff3 = from a in context.Affiliates
             where ((name == null && a.CompanyName == null)  || (a.CompanyName == name))
             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.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.