Problem to eliminating 'Sequence contains no elements' error.

[SoftCircuits]

When DbContext.Areas has no records, the following code throws an exception:

int sequence = DbContext.Areas
    .Where(a => a.UserId == userId)
    .Select(a => a.Sequence)
    .Max();

System.InvalidOperationException: 'Sequence contains no elements.'

The standard way of working around this is to use DefaultIfEmpty(). But that doesn't work here either.

int sequence = DbContext.Areas
    .Where(a => a.UserId == userId)
    .Select(a => a.Sequence)
    .DefaultIfEmpty()
    .Max();

System.InvalidOperationException: 'Sequence contains no elements.'

I also tried setting an explicit default value. That gives me a different error.

int sequence = DbContext.Areas
    .Where(a => a.UserId == userId)
    .Select(a => a.Sequence)
    .DefaultIfEmpty(0)
    .Max();

System.InvalidOperationException: 'Processing of the LINQ expression 'DbSet
.Where(a => a.UserId == __userId_0)
.Select(a => a.Sequence)
.DefaultIfEmpty(__p_1)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.'

I also tried a manual workaround. And that doesn't work either.

int sequence = DbContext.Areas
    .Where(a => a.UserId == userId)
    .Select(a => a.Sequence)
    .Concat(new [] { 0 })
    .Max();

System.InvalidOperationException: 'Processing of the LINQ expression 'DbSet
.Where(a => a.UserId == __userId_0)
.Select(a => a.Sequence)
.Concat(__p_1)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.'

Ideally, I would just add DefaultIfEmpty() here and be good to go. This shouldn't be so difficult.

Further technical details

EF Core version: 3.1.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10
IDE: 16.5.1

[ajcvickers]

@maumar Verified that this still fails on recent daily build. Output:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (14ms) [Parameters=[@__userId_0='1'], CommandType='Text', CommandTimeout='30']
      SELECT MAX([t].[Sequence])
      FROM (
          SELECT NULL AS [empty]
      ) AS [empty]
      LEFT JOIN (
          SELECT [a].[Id], [a].[Sequence], [a].[UserId]
          FROM [Areas] AS [a]
          WHERE [a].[UserId] = @__userId_0
      ) AS [t] ON 1 = 1
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'SomeDbContext'.
      System.InvalidOperationException: Sequence contains no elements.
         at lambda_method(Closure , QueryContext , DbDataReader , ResultContext , Int32[] , ResultCoordinator )
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
System.InvalidOperationException: Sequence contains no elements.
   at lambda_method(Closure , QueryContext , DbDataReader , ResultContext , Int32[] , ResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
Unhandled exception. System.InvalidOperationException: Sequence contains no elements.
   at lambda_method(Closure , QueryContext , DbDataReader , ResultContext , Int32[] , ResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Max[TSource](IQueryable`1 source)
   at Program.Main() in /home/ajcvickers/AllTogetherNow/Daily/Daily.cs:line 27

[SoftCircuits]

@smitpatel Since I didn't get any comments to me on this, can I know what happened? Can you tell me the version that might have a fix?

[ajcvickers]

@SoftCircuits It will first appear in 5.0 preview 4, or you can try the daily builds.

[anri-vin]

Is there any chance to see that fix in 3.1 branch? By the way it's an LTS release and should still get bugfixes like this one. I don't want to switch to 5.0 to get this thing working.

[Markeli]

I agree with anri-vin. We can't upgrade EF Core to 5.0 because of company policy, while EF 3.1 is LTS. Are there any public rules for determining which defect should be fixed in LTS?

[AlissonRS]

Just faced this issue, the workaround for now requires an extra query (.AnyAsync() to check if there is data available) which is not good.

I'd appreciate it if this was fixed in 3.1.