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!

BFree · Accepted Answer · 2010-03-09 19:33:04Z

11

try this:

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

answered Mar 9, 2010 at 19:33

BFree

104k21 gold badges164 silver badges205 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sgy Over a year ago

This is working! But I still don't understand why Visual Studio don't generates the exact same SQL query as LINQPad... LINQPad manages null values/references differently? Anyway, thank you.

Falaque · Accepted Answer · 2013-03-27 14:31:06Z

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)

Will Marcouiller · Accepted Answer · 2010-03-09 19:28:28Z

0

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.

answered Mar 9, 2010 at 19:28

Will Marcouiller

24.3k26 gold badges100 silver badges166 bronze badges

Comments

estinamir · Accepted Answer · 2020-10-07 16:45:46Z

0

This example worked for me:

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

answered Oct 7, 2020 at 16:45

estinamir

5035 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

LINQ Query

Note 1

Note 2

Note 3

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

LINQ Query

Note 1

Note 2

Note 3

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related