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.

Bob Horn · Accepted Answer · 2012-12-22 00:31:57Z

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, 2012 at 0:31

Bob Horn

34.5k36 gold badges122 silver badges238 bronze badges

answered Dec 21, 2012 at 21:38

Yannick Motton

36.2k4 gold badges41 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Bob Horn Over a year ago

What about the case where manufacturerId is null and I want to find rows where manufacturerId is null?

Yannick Motton Over a year ago

If the ManufacturerId property is equally nullable, you should be able to use the == operator.

Bob Horn Over a year ago

I think that did it. I needed to use manufacturerId.Value, not just manufacturerId. Testing for accuracy now...

Yannick Motton Over a year ago

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.

Bob Horn Over a year ago

Unfortunately, scan.RawMaterialLabel.ManufacturerId is indeed nullable. We're dealing with third party data.

|

Collectives™ on Stack Overflow

Entity Framework Bug: Exception with PredicateBuilder and comparing nulls

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related