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