Handling null values in where clause using LINQ-to-SQL

Question

The LINQ-to-SQL query in Visual Studio generates an SQL query with errors. In LINQPad, the same LINQ query using the same database (or DataContext) runs just fine.

LINQ Query

var accesDomaines = from t in db.Access
                  where t.IdUser == access.IdUtilisateur
                  where t.IdDomain != null
                  where t.IdRole == access.IdRole
                  where t.IdPlace == access.IdPlace
                  select t;

Here's a small part of generated SQL where the error occurs:

WHERE (...) AND ([t3].[IdRole] = ) AND (...)

After the equals in where clause, there's literally nothing ! In the SQL query of LINQPad we see the good where clause:

WHERE (...) AND ([t3].[IdRole] IS NULL) AND (...)

When I compare the two generated SQL queries from VS and LINQPad, line by line, this is the same thing. Except LINQPad is using params and also the missing right part of equal in where clause of Visual Studio, as shown before.

---

Note 1

In the LINQ query, I tried with this syntax in where clauses:

where t.IdRole.Equals(acces.IdRole.Value)

But also generates a bad result. I even tried something like this before the LINQ query:

if (!acces.IdRole.HasValue) { acces.IdRole = null; }

Note 2

Properties are nullable integers. I do want null in query if property is null. Obviously, I want the value of property if there's a value.

Note 3

I have tried the proposition made in this question: Linq where column == (null reference) not the same as column == null

...with no success.

---

Any explanation of two similar LINQ queries, but generating a good and a bad SQL query? Any suggestion to solve this problem?

Thank you!

Answer 1

Have you tried verifying whether your properties had values with the HasValues property provided by the Nullables?

where t.IdRole == access.IdRole.HasValues ? access.IdRole.Value : null

Perhaps this could work. I haven't used LINQ-to-SQL really.

Answer 2

try this:

where object.Equals(t.IdRole, access.IdRole)