LINQ-to-SQL query not returning row when where clause compares against NULL value

Question

Lets consider a table with 2 columns: ID (int) and Role(string). Both are nullable.

Now assume that the data in the two columns is:

ID     Role
--     ----
 1     NULL
 2     Admin

The query looks like this:

List<types> t1 = (
    from a in datacontext.RoleTable 
    where a.Role != "Admin"
    select a
).ToList();

I thought the above query should be returning the first record of the table as its Role column is not equal to 'Admin' but the query returns an empty list.

Now when I use this query:

List<types> t2 = (
    from a in datacontext.RoleType 
    where a.Role != "Admin" && a.Role == DBNull.Value.ToString() 
    select a
).ToList();

I get the correct answer.

Can anybody tell me why the first query is not working please.

FYI: If the Role column in the first row in the table is changed to User instead of NULL then the first query works fine.

I am using SQL Express and LINQ to SQL.

Answer 1

The first query doesn't behave as expected, because it is translated into SQL that is equivalent to the following:

select * from RoleTable where Role != 'Admin'

Now, in SQL NULL != 'Admin' is not TRUE (nor is it FALSE - it is undefined).
That's one of the many cases where the abstraction that LINQ to SQL provides is leaky and you still need to know SQL.

BTW: Your second query is also incorrect, it will select only those rows that are null. It wouldn't select a row with the role 'User'.

The correct query would look like this:

List<types> t2 = 
    (from a in datacontext.RoleTable 
     where a.Role != "Admin" || a.Role == null 
     select a).ToList();