Liqn to sql null-able value in query

Question

I need get all items these have no categories

int? categoryId = null;
var items=db.Items.Where(x=>x.CategoryId==categoryId);

this code generate in where:

where CategoryId=null

instead of

where CategoryId is null

ok, when i write

var items=db.Items.Where(x=>x.CategoryId==null);

in my sql profiler it works:

where CategoryId is null

BUT when i do this HACK it doesn't:

var items=db.Items.Where(x=>x.CategoryId==(categoryId.HasValue ? categoryId : null));

so what's the problem? is there by in L2S?

Update: if categoryId has value it need return something like this:

where CategoryId=1

Answer 1

You might try:

var items = db.Items.Where( x => !x.CategoryId.HasValue );

The problem with the first sample is that LINQ doesn't look at the value of the variable when building the expression. It just sees and equality expression and converts it into the equivalent expression in SQL. LINQ requires the developer to know and understand how it works with respect to checking for null values. I think what I've given you is the simplest way to do it.

Edit: based on your comment. You might construct alternate queries if you're looking for matches to categoryId or a null category id if categoryId has no value. This would be one way of chaining many potential queries together -- and potentially ignoring some comparisions you don't care about in certain situation.

IQueryable<Item> items = db.Items;
if (categoryId.HasValue)
{
    items = items.Where( x => x.CategoryId == categoryId );
}
else
{
    items = items.Where( x => !x.CategoryId.HasValue );
}

Alternatively (for your simple case), using object.Equals seems to compell the LINQ to SQL expression builder to implement IS NULL when the value is null.

var item = db.Items.Where( x => object.Equals( x.CategoryId, categoryId ) );