CODEWITHSUNDEEP
c#linqentity-frameworkoptional-parametersisnull
SetiSeeker
SetiSeeker

Reputation: 6684

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?

Upvotes: 1

Views: 22149

Answers (4)

ALT
ALT

Reputation: 1242

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

Upvotes: 0

Stephan
Stephan

Reputation: 591

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

Upvotes: 2

Martin Ernst
Martin Ernst

Reputation: 5679

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

Upvotes: 1

Kris Ivanov
Kris Ivanov

Reputation: 10598

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

Upvotes: 0

Related Questions