Linq expression with nullable

Question

Looks like stupid question, but I just dont get it. My entity:

public class Page
{
    public int Id { get; set; }
    //...
    public int? ParentId { get; set; }
}

In controller:

db.Pages.First(x => x.ParentId == null);

Works as expected (returns some element). But:

int? test = null;
db.Pages.First(x => x.ParentId == test);

Throws Sequence contains no elements

What do I miss?

Answer 1

I believe there's an oddity around nulls with some LINQ providers. Try:

var query = db.Pages.First(x => (test != null && x.ParentId == test) ||
                                (test == null && x.ParentId == null));

Alternatively, use different queries for the different situations:

var query = test == null ? db.Pages.First(x => x.ParentId == null)
                         : db.Pages.First(x => x.ParentId == test);

Basically this is because SQL treats NULL as unequal to itself, so:

WHERE X = Y

will still fail if both X and Y are null values. Using the == null part (with a literal null) forces a conversion to ISNULL or whatever the SQL equivalent is.

I agree it's a pain, and someone else may have a better workaround, but this may help you get going.

Answer 2

You could do something like this as a workaround:

int? test = null;
if(test.HasValue) {
 db.Pages.First(x => x.ParentId == test.Value);
} else {
 db.Pages.First(x => x.ParentId == null);
}

I'm assuming since int? is actually a Nullable<int> our linq-to-entities provider isn't comparing things right.

Answer 3

Try this (modified according to gdoron's comment. It now is exactly what gideon posted, so please accept his instead of mine):

int? test = null;
if(test.HasValue) {
    db.Pages.First(x => x.ParentId == test.Value);
} else {
    db.Pages.First(x => x.ParentId == null);
}