null vs default(decimal?) set to null - giving different results

Question

Using: Entity Framework 4.3.1, MVC 4

Sample database records (for example):

Id    Height
1     null  
2     2.1

Why does this first statement bring back zero results:

decimal? scannedItemHeight = default(decimal?);

(from st in Stocks
 where st.Height == scannedItemHeight 
 select st.Id).ToList();

But this statement returns record Id 1:

(from st in Stocks
 where st.Height == null
 select st.Id).ToList();

If scannedItemHeight is null, I only want to match values where the height is null too.

I only want to return the first record, Id 1.

UPDATE I ended up using:

st.Height == scannedItemHeight || (scannedItemHeight == null && st.Height == null)

Log the actual SQL statements that are generated and see if there's a difference. http://stackoverflow.com/questions/16880687/how-can-i-log-the-generated-sql-from-dbcontext-savechanges-in-my-program — Jeroen Vannevel, May 10 '16 at 15:58
Do you actually have items with non-null Height which match other conditions? Otherwise the results for both methods would be the same in _this_ case (because Jon's method will return only items with Height is null, while my method just all items). It's very important to understand distinction between those methods. As I undestand you need to match items with null Height if parameter is null - so use Jon's method. — Evk, May 11 '16 at 10:58
@Evk Doh! I think your right, I chose height for the example and didn't check that for the other given filtering parameters (width, length, gauge) that all of the results have a null height, let me retest and get back to you, but yeah that would explain everything. — Paul Zahra, May 11 '16 at 11:10
@Evk you are correct. Sorry for not being clearer with my initial post, but your insight into what the SQL is was very helpful, and technically did answer my question, although whilst Jon didn't explain the issue nowhere near as well as you he went a step further and correctly presumed what I was trying to do... hmm it's a coin toss as to who to mark as the answer, however as you answered the initial question correctly I feel it only just to award it to you... thanks to you both! — Paul Zahra, May 11 '16 at 11:16
For the sake of anyone reading the question in future, I'd suggest *massively* reducing it (we only need one condition, for example) and explaining what you're trying to achieve - namely that if `scannedItemHeight` is null, you only want to match values where the height is null too. At that point, I think Evk should edit their answer to match the question, as otherwise anyone reading the question will assume that the accepted answer matches the question asked, and potentially get confused. — Jon Skeet, May 11 '16 at 11:25
@Evk: But if the question *is* edited to be basically how I'd understood it, that leaves your answer as "If you want that behaviour, see Jon's answer"... — Jon Skeet, May 11 '16 at 11:40
@JonSkeet I've whittled down and clarified the question and hopefully explained things adequately. — Paul Zahra, May 11 '16 at 11:42
@PaulZahra: Sort of - but the part about still getting 12 results because it so happened they were all null is irrelevant to future readers. Instead, I would suggest: a) here's some sample data; b) here are sample parameter values; c) here's expected output; d) here's actual output. Both the sample data and sample parameter values should contain non-null and null values. Think about this question as if you were trying to teach someone in the future, rather than it being about recounting your recent experience. — Jon Skeet, May 11 '16 at 11:52

Jon Skeet · Answer 1 · 2016-05-11T11:23:38.893

I've certainly seen some odd LINQ behaviour before now, due to the way that SQL handles nulls not being the same as the way that C# handles nulls. In C#, a == b is true if a and b are both null, whereas in SQL they're not... instead you have to deliberately check for nullity. I would try:

var temp = (from st in Stocks
            where st.Height == scannedItemHeight || (scannedItemHeight == null && st.Height == null)
            select st.Id).ToList();

Or construct the queries differently:

var filtered = scannedItemHeight == null
    ? Stocks.Where(st => st.Height == null)
    : Stocks.Where(st => st.Height == scannedItemHeight);
var temp = filtered.Select(st => st.Id).ToList();

Evk · Accepted Answer · 2016-05-11T11:47:31.373

3

That's because in first case database query would be something like

where Height = @param1 ...

While in second case that would be

where Height is null

First query would return no results because @param1 would be null and no row can match such condition.

Main point is that from C# standpoint those queries are equivalent, but from sql standpoint they are not: you should use IS NULL (or IS NOT NULL) in sql to check for nulls.

How to fix depends on what do you want to do when your parameter is null. In your case: use Jon Skeet's answer.

If someone will want to ignore parameter and not filter by it at all(quite common case, for example when parameter represents user's input in some field, and when nothing is typed there - nothing to filter), then do:

where scannedItemHeight == null || st.Height == scannedItemHeight

which will be in sql like

where @param1 is null OR Height = @param1

edited May 11 '16 at 11:47

answered May 10 '16 at 16:00

Evk

98,527
8
141
191

Hmm... makes sense... so how could I pass a nullable decimal variable with a null value and it be treated the same as 'is null'? – Paul Zahra May 10 '16 at 16:03
Hang on - this gives the wrong results though, surely... if `scannedItemHeight` is null, presumably the OP *only* wants to match values where `st.Height` is null, whereas this will include values where `st.Height` has any value... – Jon Skeet May 11 '16 at 10:25
@PaulZahra: Check my objection... what do you want the behaviour to be if `scannedItemHeight` is null? Do you really want it not to filter at all? (If so, you should update your question to clarify that.) – Jon Skeet May 11 '16 at 10:26
@JonSkeet I never saw anyone wanted to use it like that. Usually if parameter is null people want to ignore it. All in all, OP asks _why_ first query returns no results and second one does - so I think I have answered this question. – Evk May 11 '16 at 10:28
@Evk: Well that's what I'd expect from a query with the C# where clause of `where st.Height == scannedItemHeight` - I'd expect to see results where that expression would return `true` in C#. (And yes, I've seen that in various places.) The OP never says they want to remove the filter entirely when the value is null. If that's what they want, they should say so explicitly IMO. We'll see if they come back to clarify. – Jon Skeet May 11 '16 at 10:37

null vs default(decimal?) set to null - giving different results

2 Answers2