CODEWITHSUNDEEP
c#linqlinq-to-sqlwhere-clause
Tyler Durden
Tyler Durden

Reputation: 516

Where condition equal true and Nullable object must have a value

I've been looking for an answer but I couldn't find anything to help me. I get this error

Nullable object must have a value.

My request is:

from e in dc.tblElements 
where
    e.IsUnique &&
    (e.TypeID == 2) &&     
    (categoryId != null ? e.CategoryId.Value == categoryId.Value : true) &&
    ((e.Name.Contains(keyword)) ||
    (e.Keywords.Contains(keyword)))
select e

The third line of the where condition is the problem (categoryId). If categoryId has a value, it works but not when it is null. However, I replaced this line with true and it works as well. I can't understand what is the problem here.

in my table CategoryId can be null so I tried:

(categoryId.HasValue && e.CategoryId.HasValue ? e.CategoryId.Value == categoryId.Value : true)

What I want to do: I want to select all the elements of this table depending on the where condition. categoryId comes from a drop down so if the default value is still selected when the user does the request, I want to display all the elements no matter what the category.

Upvotes: 3

Views: 417

Answers (4)

ubaldisney
ubaldisney

Reputation: 66

Try this:

from e in dc.tblElements 
where
    e.IsUnique &&
    (e.TypeID == 2) &&     
    (categoryId.HasValue && e.CategoryId.Value == categoryId.Value) &&
    ((e.Name.Contains(keyword)) ||
    (e.Keywords.Contains(keyword)))
select e

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131305

Looks like you are trying to implement a "catch-all" categoryId parameter. That's an anti-pattern in SQL and a strong smell that can lead to bad performance.

In LINQ, it's not necessary since you can add .Where() conditions just by adding another .Where() call to your query, eg :

var query = from e in dc.tblElements 
            where
                e.IsUnique &&
                e.TypeID == 2 &&     
                ( e.Name.Contains(keyword) ||
                  e.Keywords.Contains(keyword) )
            select e;
if (categoryId.HasValue)
{
    query=query.Where(e.CategoryId == categoryId);
}

You can use this to add multiple conditions at runtime

Upvotes: 1

nvoigt
nvoigt

Reputation: 77294

You should be good with just comparing your two variables:

e.CategoryId == categoryId

If you want special treatment of one being NULL, maybe because you want that to be a special case where NULL matches everything instead of just another NULL, you can add that:

e.CategoryId == categoryId || !e.CategoryId.HasValue || !categoryId.HasValue

Your problem with your statement is that you access .Value. Yes, if you would run the code with Linq-To-Objects in memory, it would work because the compiler will only run the code of one branch of your if-statement (ternary operator, I know, but you get what I mean). But for a database, there needs to be a statement prepared. That statement needs to be there in full, it does not use any short-circuiting. So the statement builder will access both your branches to build that statement for the database and one of those branches will fail because it accesses .Value although there is none.

Upvotes: 5

gopal
gopal

Reputation: 11

Make CategoryId as nullable type and try.

Nullable<int> CategoryId = null;

Upvotes: 1

Related Questions