Why do these C# LINQ Queries to find a Minimum or Maximum with Where criteria fail?

I am working on a project where I want to run queries on a column in a database to find (a) the largest value less than or equal to a user-input minimum value, and the smallest value greater than or equal to a user-input maximum value. Also, I needed to handle the potential case where there might not be a result returned from one or both of these, so I specified a default value to be returned instead. However, when I ran this code, I got two cryptic errors in the Visual Studio output window:

Exception thrown: 'System.InvalidOperationException' in Microsoft.EntityFrameworkCore.Relational.dll
Exception thrown: 'System.InvalidOperationException' in System.Private.CoreLib.dll

Apparently, they indicate that something in the 2 queries shown below caused a problem. But despite these errors, the application itself did not crash -- it just stopped executing silently. I eventually found another way to do what I needed to do, but I'm left with the question -- What's wrong with these 2 statements?

fromValue = dbContext.SourceTable.Where(x => x.SourceColumn <= targetFromValue)
                                .Select(x => x.SourceColumn).DefaultIfEmpty(0).Max();

toValue = dbContext.SourceTable.Where(x => x.SourceColumn >= targetToValue)
                              .Select(x => x.SourceColumn).DefaultIfEmpty(99999).Min();

CodePudding user response：

You are talking about a database, which implies you are using LINQ-To-Entities, not LINQ-To-Objects.

DefaultIfEmpty may not be translatable to SQL.

What you can do is:

toValue = dbContext.SourceTable
    .Where(x => x.SourceColumn >= targetToValue)
    .Min<SourceTableType, int?>(x => x.SourceColumn)

this might be even better for you, as then the "no value found" case is null, which is easier to handle :)

CodePudding user response：

Requirement 1 find the largest value less than or equal to a user-input minimum value

To make it easier to understand what I'm talking about, let's assume you have a table of Products:

class Product
{
    public int Id {get; set;}
    public string Name {get; set;}
    public decimal Price {get; set;}
    ...
}

And you want the largest price that is not larger than €10.00 (= smaller or equal)

decimal limitPrice = 10.00;

decimal largestProductPriceNotLargerThanLimitPrice = dbContext.Products
    .Select(product => product.Price)
    .Where(price => price <= limitPrice)
    .Max();

In words: from each Product in the table of Products, select its Price. From the resulting sequence of Prices keep only those Prices that are smaller or equal than the limitValue. From the remaining sequence of Prices keep the largest one.

There is a problem: if there are no product prices that are smaller than the limitPrice, then Max does not work. If you think this might be a problem, then use MaxBy instead of Max. This will return null if the collection is empty.

decimal? largestProductPriceNotLargerThanLimitPriceOrDefault = dbContext.Products
    .Select(product => product.Price)
    .Where(price => price <= limitPrice)
    .MaxBy(price => price);

If you are thinking of using this in more places, consider to create an extension method for this. If you are not familiar with extension methods, read Extension Methods Demystified

public TProperty MaxNotLarger<Tsource, TProperty>(
    this IEnumerable<TSource> source,
    Func<TSource, TProperty> propertySelector,
    TProperty limitValue)
{
    return MaxNotLarger(source, propertySelector, limitValue, null);
    // null: use the default comparer for TProperty
}

public TProperty MaxNotLarger<Tsource, TProperty>(
    this IEnumerable<TSource> source,
    Func<TSource, TProperty> propertySelector,
    TProperty limitValue,
    IComparer<TProperty> comparer)
{
    // TODO: check for correct input parameters
    if (comparer == null) comparer = Comparer<TProperty>.Default;

    return source.Select(sourceItem => propertySelector(sourceItem))
                 .Where(property => comparer.Compare(property, limitValue) <= 0)
                 .Max(comparer);
}

Usage:

decimal limitValue = 10.00;
IEnumerable<Product> products = ...

decimal largestProductPriceNotLargerThanLimitValue = products.MaxNotLarger(
    product => product.Price, limitValue);

Or if you want the largest Product.Id not larger than the limitValue:

decimal largestProductIdNotLargerThanLimitValue = product.MaxNotLarger(
    product.Product.Id, limitValue);

You can intertwine this with other Linq methods:

int brandIdPhilips = dbContext.Brands.Where(brand => brand.Name == "Philips")
                                     .Select(brand => brand.Id)
                                     .FirstOrDefault();
var result = dbContext.Products
    .Where(product => product.BrandId = brandIdPhilips)
    .MaxNotLarger(limitValue);

Once you've got this one, the "smallest value not smaller than limitValue" is easy