Comparing a nullable column throws "Unable to cast the type..." exception

Question

My entity NewsItem has a nullable foreign key property: LibraryID of type int?.

My issue is when I query the property and compare it with any value except null, I get exceptions.

Initially my code was:

int? lid = ...
var results = context.NewsItems
    .Where(n => n.LibraryID == lid);

but it gives me no results at all, no matter what lid is.

So, I tried:

var results = context.NewsItems
    .Where(n => n.LibraryID.Equals(lid));

gives exception:

Unable to create a constant value of type 'System.Object'. Only primitive types or enumeration types are supported in this context.

and then I tried:

var results = context.NewsItems
    .Where(n => lid.Equals(n.LibraryID));

and got:

Unable to cast the type 'System.Nullable`1' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.

and this:

var results = context.NewsItems
    .Where(n => object.Equals(lid, n.LibraryID));

gives same exception as the last one.

Now I was desperate, so I tried to complicate stuff (like other forums suggested, for example here):

var results = context.NewsItems
    .Where(n => (lid == null ? n.LibraryID == null : n.LibraryID == lid));

but still getting same exception.

So... any SIMPLE workarounds?

Answer 1

How about

var results = context.NewsItems
    .Where(n => lid.HasValue ? lid.Value == n.LibraryId.Value : (!n.LibraryId.HasValue) );