CODEWITHSUNDEEP
c#linq-to-sql
sgy
sgy

Reputation: 3072

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

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!

Upvotes: 12

Views: 15882

Answers (4)

estinamir
estinamir

Reputation: 503

This example worked for me:

var aff3 = from a in context.Affiliates
where ((name == null && a.CompanyName == null) || (a.CompanyName == name))
select a.ID;

Upvotes: 0

Falaque
Falaque

Reputation: 896

Use 

object.Equals()

.Net would take care of generating correct sql for null condition.

Example:

Say you have a street table with columns like Suffix and Prefix. Then following linq query doesn't work:

  string suffix = "ST";
  string prefix = null;

  var map = from s in Streets
            where s.Suffix==suffix || s.Prefix==prefix
            select s;

It would generate following sql:

SELECT [t0].[StreetId], [t0].[Prefix], [t0].[Suffix]
FROM [Street] AS [t0]
WHERE ([t0].[Suffix] = @p0) AND ([t0].[Prefix] = @p1)

we can clearly see it will not return any result.

Using object.Equals():

  string suffix = "ST";
  string prefix = null;

  var map = from s in Streets
  where object.Equals(s.Suffix, suffix) && object.Equals(s.Prefix,prefix)
  select s;

would generate sql:

SELECT [t0].[StreetId], [t0].[Prefix], [t0].[Suffix]
FROM [Street] AS [t0]
WHERE ([t0].[Suffix] IS NOT NULL) AND ([t0].[Suffix] = @p0) 
         AND ([t0].[Prefix] IS NULL)

Which is correct one.

(Little late, but wanted to expand the answer for the benefit of others)

Upvotes: 6

BFree
BFree

Reputation: 103770

try this:

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

Upvotes: 11

Will Marcouiller
Will Marcouiller

Reputation: 24142

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.

Upvotes: 0

Related Questions