Entity Framework Linq Is Null, Is Not Null Issue

Question

I have a method that receives a category id, followed by two optional string parameters that default to null.

I tried using a few similar answers from other questions on SO but sofar none have helped.

I am trying to get the linq to EF query to work as follows:

If either optional parameter has a value, use the value otherwise use an Is Null.

If both optional parameters are present use these as part of the query or either one if only on eis supplied. But if no parmeters are added, just use the category id.

Both optional parameters in the db are marked as nullable.

Here is the code that's not working:

          from c in dtx.Categories
          where c.CategoryId == CatId
         && (string.IsNullOrEmpty(param1) ? c.Param1 == null : c.Param1 == param1)
         && (string.IsNullOrEmpty(param2) ? c.Param2 == null : c.Param2 == Param2)
        select c

Try Two:

          from c in dtx.Categories
          where c.CategoryId == CatId
          && (c.Param1 == null ? c.Param1 == null : c.Param1 == param1)
          && (c.Param2 == null ? c.Param2 == null : c.Param2 == param2)
          select c

No Errors are thrown, but both queries always return zero results unless both parameters are there.

One of the posts I tried: How can i query for null values in entity framework?

Shouldn't Param2 be param2? It may just be a posting issue. – marteljn May 24 '12 at 17:57 — marteljn, May 24 '12 at 17:57

score 2 · Accepted Answer · answered May 24 '12 at 18:36

From what I can tell, the problem look like the condition of the query are not written correctly. Let check what will append with an example:

The Data:

Id = 1, Param1 = null, Param2 = null
Id = 2, Param1 = 'a'   param2 = null
Id = 3, Param1 = null, Param2 = 'b'
Id = 4, Param1 = 'a'   param2 = 'c'

With the current query and the other solution proposed you will only get the Id 1. Your condition are saying : If the Param1 Is Null and c.Param1 (the stored value) Is null OR c.Param1 Is Equal to Param1 value.

What you need is a condition that says : If Param1 Is Null OR c.Param1 Is Equal to Param1 value.

If you use this query, you will always get the your result.

from c in dtx.Categories
where c.CategoryId == CatId
    && (string.IsNullOrEmpty(param1) || c.Param1 == param1)
    && (string.IsNullOrEmpty(param2) || c.Param2 == param2)
select c

Martin Ernst · Answer 2 · 2012-05-24T18:06:16.833

1

3rd edit for luck, maybe I read the question right this time :)

var p1 = string.IsNullOrEmpty(param1) ? null : param1;
var p2 = string.IsNullOrEmpty(param2) ? null : param2;

var query = dtx.Categories.Where(c => c.CategoryId == CatId);
if (p1 != null || p2 != null) {
    query = query.Where(c => c.Param1 == p1 && c.Param2 == p2);
}

edited May 24 '12 at 18:06

answered May 24 '12 at 17:59

Martin Ernst

5,629
2
17
14

Seems to return all the items. – SetiSeeker May 25 '12 at 06:23

score 0 · Answer 3 · answered May 24 '12 at 17:58

0

you should explicitly unfold the check, either both are null or they match

((string.IsNullOrEmpty(param1) && c.Param1 == null) || (c.Param1 == param1))

EDIT: just tested and it does not matter is u check for null or not the SQL is the same, so just do

from c in dtx.Categories 
    where c.CategoryId == CatId 
       && (c.Param1 == param1) 
       && (c.Param2 == Param2) 
select c

answered May 24 '12 at 17:58

Kris Ivanov

10,476
1
24
35

The edit seems correct, however, it won't return true if either db value is '' and param is null or db value is null and param is "". – Danny Varod May 24 '12 at 18:17
This was my initial attempt and I retried them both now, but I am still getting zero results. Thx for the help. – SetiSeeker May 24 '12 at 18:19

ALT · Answer 4 · 2013-10-29T10:31:13.883

The problem is when you wrote

from ... where c.Param1 == null ...

the LINQ translates it into the same SQL expression:

SELECT ... FROM ... WHERE Param1 = null ...

but you need this:

SELECT ... FROM ... WHERE Param1 IS NULL ...

So here the correct solution is:

from c in dtx.Categories
where c.CategoryId == CatId && 
     (param1 == null ? !c.Param1.HasValue : c.Param1.Value == param1) && 
     (param2 == null ? !c.Param2.HasValue : c.Param2.Value == param2)
select c

Entity Framework Linq Is Null, Is Not Null Issue

4 Answers4

Linked