Entity Framework Linq Is Null, Is Not Null Issue

问题:

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?

回答1:

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

回答2:

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);
}

回答3:

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 

回答4:

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