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

c#, linq-to-sql

Solution

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();

Problem

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.

Original source