Ef core: Sequence contains no element when doing MaxAsync

Question

I'm using ef core in my asp core API project. I have to find the highest order index.

Example:

Data table: Id, ForeignId, OrderIndex

So I'm doing:

var highestOrderIndex = await _context
                .ExampleDbSet
                .Where(x =>
                    x.ForeignId == foreignId)
                .MaxAsync(x =>
                    x.OrderIndex);

The problem is when the example db set is containing 0 elements. This will throw an exception: Sequence contains no element.

Is there an elegant way to do this? Because I don't want to get all the elements from the database. And it should be async.

Thanks

Answer 1

Actually there is quite elegant (and more performant compared to the suggested in the other answer because it's executing just a single database query) way by utilizing the fact that aggregate methods like Min, Max throw Sequence contains no element exception only when used with non nullable overloads, but nullable overloads simply return null instead.

So all you need is to promote the non nullable property type to the corresponding nullable type. For instance, if the OrderIndex type is int, the only change to your query could be

.MaxAsync(x => (int?)x.OrderIndex);

Note that this will also change the type of the receiving variable highestOrderIndex to int?. You can check for null and react accordingly, or you can simply combine the aggregate function call with ?? operator and provide some default value, for instance

var highestOrderIndex = (await _context.ExampleDbSet
    .Where(x => x.ForeignId == foreignId)
    .MaxAsync(x => (int?)x.OrderIndex)) ?? -1; // or whatever "magic" number works for you

Answer 2

Doing an AnyAsync and then a MaxAsync will result in two separate database calls. You can condense it into one by making sure the sequence contains a "default" minimum value. This is a useful trick anywhere you use the Linq Max/Min methods, not just in database code:

context.ExampleDbSet
    .Where(w => w.ForeignId == foreignId)
    .Select(s => s.OrderIndex)
    .Concat(new[] { 0 })
    .MaxAsync();

Answer 3

Another way with a bit better perfomance than the MaxAsync, if the default value is the one you want to get, if there are no results:

var highestOrderIndex = await _context.ExampleDbSet
    .Where(x => x.ForeignId == foreignId)
    .OrderByDescending(x => x.OrderIndex)
    .Select(x => x.OrderIndex)
    .FirstOrDefaultAsync();

TOP is faster than the aggregate functions, look at the execution plan in your SQL Server.

Answer 4

You can find if any records exist and if they do, then to find the max. Something like this:

var query = _context.ExampleDbSet
                .Where(x => x.ForeignId == foreignId);

var itemsExist = await query.AnyAsync();
int maxOrderIndex = 0;

if(itemsExist)
{
    maxOrderIndex = await query.MaxAsync(x => x.OrderIndex);
}

Here you won't have to retrieve all of the items from the database, only check if a record exists which is much much faster and you can also keep the method async.

Answer 5

You can use DefaultIfEmpty and Select before MaxAsync.

        var highestOrderIndex = await _context
            .ExampleDbSet
            .Where(x =>
                x.ForeignId == foreignId)
            .Select(x => x.OrderIndex)
            .DefaultIfEmpty() // default is 0 if OrderIndex is int or long
             // .DefaultIfEmpty(-1) // default is -1
            .MaxAsync();