Entity Framework Bug: Exception with PredicateBuilder and comparing nulls

Question

I was able to get my method (below) to run in LinqPad, but when switching to my actual code (using Entity Framework), I get this error:

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

The error occurs if I uncomment either of the two commented lines here (the actual error doesn't occur until the last line in the method is run):

public List<LotEquipmentScan> GetLotEquipmentScans(string maximoAssetNumber, decimal? manufacturerId, decimal? partNumber)
{
    var predicate = PredicateBuilder.True<LotEquipmentScanRecord>();

    // Note: Can't use == in where clause because of the possibility of nulls. That's why we're using object.Equals().
    if (maximoAssetNumber != "x") { predicate = predicate.And(scan => object.Equals(scan.MaximoAssetNumber, maximoAssetNumber)); }
    //if (manufacturerId != -1) { predicate = predicate.And(scan => object.Equals(scan.RawMaterialLabel.ManufacturerId, manufacturerId)); }
    //if (partNumber != -1) { predicate = predicate.And(scan => object.Equals(scan.RawMaterialLabel.PartNumber, partNumber)); }

    return Db.LotEquipmentScanRecords.AsExpandable().Where(predicate).ToList().Map();
}

I believe this is happening because manufacturerId and partNumber are nullable decimals. The issue is that those variables can be null, and we even want to filter the results by them being null. Does this just not work with EF, or is there an elegant way around this?

EDIT

To be clear, when manufacturerId is passed in as null, using this line returns five rows (which I can verify by looking at the DB):

if (manufacturerId != -1) { predicate = predicate.And(scan => object.Equals(scan.RawMaterialLabel.ManufacturerId, manufacturerId)); }

Using this line, no rows are returned:

if (manufacturerId != -1) { predicate = predicate.And(scan => scan.RawMaterialLabel.ManufacturerId == manufacturerId); }

The problem is when I pass in a good manufacturerId, then I get the error above.

score 3 · Accepted Answer · edited Dec 22 '12 at 00:31

3

Edit/Note from Bob Horn: This answer was accepted because of the EDIT below, specifying a bug in EF. The first part of this answer didn't work.

By using object.Equals(object, object) as method of comparing two value types (Nullables are value types too), you are implicitly boxing them. Entity Framework does not support this.

Try using the == operator instead:

// Since scan.RawMaterialLabel.ManufacturerId and manufacturerId are both Nullable<T> of the 
// same type the '==' operator should assert value equality, whether they have a value, or not.

// (int?)1 == (int?)1
// (int?)null == (int?)null
// (int?)1 != (int?)null

predicate = predicate.And(scan => scan.RawMaterialLabel.ManufacturerId == manufacturerId);

For value types the == operator asserts value equality, similarly to what the object.Equals() does for reference types.

EDIT:

Upon further investigation, there seems to be a bug in older versions of EF (pre EF5).

Hacky fix with your current version:

predicate = predicate.And(scan => 
   manufacturerId.HasValue
     ? scan.RawMaterialLabel.ManufacturerId == manufacturerId
     : scan.RawMaterialLabel.ManufacturerId == null);

But if your application allows for it, upgrade to EF5.

edited Dec 22 '12 at 00:31

Bob Horn

33,387
34
113
219

answered Dec 21 '12 at 21:38

Yannick Motton

34,761
4
39
55

What about the case where manufacturerId is null and I want to find rows where manufacturerId is null? – Bob Horn Dec 21 '12 at 21:43
If the `ManufacturerId` property is equally nullable, you should be able to use the `==` operator. – Yannick Motton Dec 21 '12 at 21:48
I think that did it. I needed to use manufacturerId.Value, not just manufacturerId. Testing for accuracy now... – Bob Horn Dec 21 '12 at 21:51
Oops, well actually, if you are comparing two Nullables, then comparing with `Value` actually does *not* make sense. If however `scan.RawMaterialLabel.ManufacturerId` is *not* nullable, then it does make sense. But then you cannot search for null rows, as the conceptual model does not allow for it. – Yannick Motton Dec 21 '12 at 21:53
Unfortunately, `scan.RawMaterialLabel.ManufacturerId` is indeed nullable. We're dealing with third party data. – Bob Horn Dec 21 '12 at 21:55
In that case, comparing `scan.RawMaterialLabel.ManufacturerId == manufacturerId` should definitely work. Which issue were you running into (ref. the //Note: )? – Yannick Motton Dec 21 '12 at 21:56
Rows weren't being returned unless I used `object.Equals()`. Using `==`, no rows were returned. – Bob Horn Dec 21 '12 at 22:05
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/21544/discussion-between-yannick-motton-and-bob-horn) – Yannick Motton Dec 21 '12 at 22:12

Entity Framework Bug: Exception with PredicateBuilder and comparing nulls

1 Answers1