LINQ-to-SQL query does not return a string when the where clause is compared to NULL

Question

Consider a table with two columns: ID (int) and Role (string). Both values ​​are zero.

Now suppose the data is in two columns:

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

The request is as follows:

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

I thought the above query should return the first record of the table, since the 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 someone tell me why the first request is not working please.

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

I am using SQL Express and LINQ to SQL.

Answer 1

The first query does not behave as expected because it is translated into SQL, which is equivalent to the following:

 select * from RoleTable where Role != 'Admin' 

Now in SQL NULL != 'Admin' there is no TRUE (and it is not FALSE - it is undefined).
This is one of many cases where the abstraction provided by LINQ to SQL is leaky and you still need to know SQL.

By the way: your second query is also incorrect, it will select only those rows that are null . He will not select a row with the role of '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();