Monday, August 8, 2011

Max problem with empty IQueryable

Max receives an error on IQueryable if it has no rows

decimal ny = _repoProduct.GetAll()
    .Where(x => x.ProductId != x.ProductId)
    .Max(x => x.MinimumPrice);

On IQueryable obtained from array or anything in-memory list, this is the error:

System.InvalidOperationException: Sequence contains no elements

On IQueryable obtained from NHibernate:
NHibernate.Exceptions.GenericADOException: Could not execute query[SQL: SQL not available] ---> System.ArgumentNullException: Value cannot be null.

On IQueryable obtained from Entity Framework:
System.InvalidOperationException: The cast to value type 'Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

The easiest way to rectify that problem is to make the MinimumPrice in your model class as nullable decimal. But that could be a problem if your views has dependent logic on the nullness of the property of your model. Another approach is to cast the target element to nullable type before passing it to aggregate function.

decimal nx = _repoProduct.GetAll()
    .Where(x => x.ProductId != x.ProductId)
    .Select(x => new { TheMinimumPrice = (decimal?)x.MinimumPrice })
    .Max(x => x.TheMinimumPrice) ?? 0;

Since we have only one element on Select, the Linq can be shortened to:
decimal nx = _repoProduct.GetAll()
    .Where(x => x.ProductId != x.ProductId)
    .Select(x => (decimal?)x.MinimumPrice)
    .Max() ?? 0;

No comments:

Post a Comment