3

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.

Improve this question
3
  • You don't need that line. There's no need for 'catch-all" parameters in LINQ, you can just add another Where() clause as necessary Commented Oct 11, 2018 at 8:29
  • Could you describe, in plain English, what you want? Commented Oct 11, 2018 at 8:30
  • I edited the topic. @Panagiotis Kanavos can you explain a bit more your idea? Commented Oct 11, 2018 at 8:35

4 Answers 4

Reset to default
5

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.

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

5 Comments

In your case (2nd line), if categoryId = 5 I will get, all the elems with categoryId = 5 but also all the elems with no category. When categoryId has a value, I just want the elems with this categoryId. Am I right?
@TylerDurden I cannot tell you what you want. That's why I asked in the comments to your question. If you can describe what you want to happen, we can help you with that.
@nvoigt if that was SQL it would be the catch-all parameter antipattern. If categoryId is null the right option is to not add the condition at all.
@nvoigt Okay I think, I understand what you mean. Thank you for your reply and explanations. I changed the line by ((categoryId.HasValue) ? (p.CategoryID == categoryId) : true) and it is working like a charm.
@TylerDurden better check the performance first. The query optimizer will cache the plan used for the first execution. If the first execution had a null CategoryID parameter it probably won't use any indexes that include categoryId. This could harm performance for calls with a non-null CategoryID parameter, eg by performing a table scan instead of a seek on an indext that contains CategoryID
1

Make CategoryId as nullable type and try.

Nullable<int> CategoryId = null;
Improve this answer

Comments

1

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

Improve this answer

Comments

1

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.