Comparing a column with a column value of zero excludes the exception "Unable to distinguish type ..."

Question

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

My problem is when I request a property and compare it with any value except null , I get exceptions.

Originally my code:

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

but it gives me no results no matter what lid .

So I tried:

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

gives an exception:

Unable to create a constant value of type "System.Object". In this context, only primitive or enumeration types are supported.

and then I tried:

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

and received:

Cannot enter type "System.Nullable`1" to enter "System.Object". LINQ to Entities only supports EDM listing of primitive or enumerated types.

and this:

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

gives the same exception as the last.

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

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

but still getting the same exception.

So ... any EASY workarounds?

Answer 1

Hmm, this first fragment should work. I used values ​​like nullables. The first thing I would do is check sanity to make sure LibraryID really int? instead of long? or similar.

Other than this, you can try the following:

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

Or to avoid ?: In the request:

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

Answer 2

What about

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

Answer 3

According to the MSDN docs (which I finally found), Where () will only filter your collection. If you want to see if there are any actual results, allow the lazy execution of the filtered query using .ToList (), GetEnumerator, or listing the collection using foreach;

This method is implemented using deferred execution. An immediate return value is an object that stores all the information that is needed to complete an action. The query represented by this method is not executed until the object is enumerated, either by calling GetEnumerator directly or using foreach in Visual C # or For Each in Visual Basic.

http://msdn.microsoft.com/en-us/library/bb534803.aspx

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

Answer 4

 var results = context.NewsItems .Where(n => n.LibraryID.HasValue && n.LibraryID.Value == lid.Value ); 

edit:

The previous filter was based on my understanding of what you would like to filter in order to get a specific value. Updated will filter null or value.

  var results = context.NewsItems .Where(n => !n.LibraryID.HasValue || n.LibraryID.Value == lid.Value ); 

Answer 5

It seems that EF does not find the correct operator overload. Therefore, it produces incorrect results if you set lid = null .

Use linq for objects, adding AsEnumerable() to your query, and everything is fine:

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