LinqToSql and comparisons with null

February 8, 2010 Waldemar Mękal

Recently, I have been very surprised by LinqToSql, because it is not as smart as I supposed. Here is a code that confused me a little.

from customer in db.Customers
    join customerGallery in db.CustomerGalleries
        on customer.ID equals customerGallery.CustomerID
    join profile in db.CustomerProfiles
        on customer.ID equals profile.CustomerID
where
    (customerGallery.AlbumId != profile.CompanyHistoryAlbum)

The context is that customer can have many galleries depending on his profile, but he always have one default gallery. The query above retrieves a default customer gallery – the one not used in any profile.

The problem in this query is that it won’t work when in column CompanyHistoryAlbum is NULL. LinqToSQL translates “!=” comparison to SQL as simple “<>”, so when NULL is compared to customerGallery.AlbumId UNKNOWN is returned and condition is not met.

More on that you can read here (“Null semantics” section) and here (“Comparing Null Values” section). As a solution I added null check as if it was in SQL:

from customer in db.Customers
    join customerGallery in db.CustomerGalleries
        on customer.ID equals customerGallery.CustomerID
    join profile in db.CustomerProfiles
        on customer.ID equals profile.CustomerID
where
    ((profile.CompanyHistoryAlbum == null) ||
    (customerGallery.AlbumId != profile.CompanyHistoryAlbum))

I also found that for comparisons with variables, e.g.:

from customer in db.Customers
where
    (customer.Address == address)

the best is to use object.Equals – LinqToSql will generate then appropriate NULL checks:

from customer in db.Customers
where
    object.Equals(customer.Address, address)

Hope it helps!

Last posts