Efcore: Flatten CASE expressions to avoid error "Case expressions may only be nested to level 10"

[bradmarder]

This fails on SQL server, but works for in-memory/SQLite.

System.Data.SqlClient.SqlException (0x80131904): Case expressions may only be nested to level 10.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReSystem.Data.SqlClient.SqlException: Case expressions may only be nested to level 10.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Steps to reproduce

```c#
using System;
using System.Linq;
using System.Threading.Tasks;

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;

namespace EFCoreBugs
{
class Program
{
static async Task Main(string[] args)
{
using (var db = new TestDb())
{
await db.Database.EnsureDeletedAsync();
await db.Database.EnsureCreatedAsync();
}
using (var db = new TestDb())
{
var key = Guid.NewGuid();
db.Todos
.Where(x => key ==
(x.Type == TodoType.foo9
? key
: x.Type == TodoType.foo3
? key
: x.Type == TodoType.foo1
? key
: x.Type == TodoType.foo4
? key
: x.Type == TodoType.foo6
? key
: x.Type == TodoType.foo7
? key
: x.Type == TodoType.foo8
? key
: x.Type == TodoType.foo10
? key
: x.Type == TodoType.foo11
? key
: x.Type == TodoType.foo4
? key
: x.Type == TodoType.foo2
? key
: key))
.ToList();
}

        Console.ReadLine();
    }
}

public class TestDb : DbContext
{
    public DbSet<Todo> Todos { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var log = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => __ == LogLevel.Error, true) });
        optionsBuilder.UseLoggerFactory(log);
        optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=master;Database=bugtest;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
    }
}

public class Todo
{
    public Guid Id { get; set; }
    public TodoType Type { get; set; }
}

public enum TodoType
{
    foo0 = 0,
    foo1 = 1,
    foo2 = 2,
    foo3 = 3,
    foo4 = 4,
    foo6 = 6,
    foo7 = 7,
    foo8 = 8,
    foo9 = 9,
    foo10 = 10,
    foo11 = 11
}

}
```

Further technical details

EF Core version: 2.1.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: win10 x64
IDE: Visual Studio 2017 15.8.0 Preview 4.0

[divega]

@bradmarder do you actually always return key for all the TodoType values? In that case you could probably formulate the query using Enumerable.Contains as a workaround for this limitation.

[divega]

EF Triage: We will use this to track flattening CASE statements.

[bradmarder]

@divega I just returned key for the purposes of demonstrating this issue. In the original code, different values are returned.

[gdunit]

I am having an issue which I believe may be related to this..
In a query that has a lot of .Include() (11 to be exact) and nested .ThenInclude() statements on each include, the following error is thrown:

System.Data.SqlClient.SqlException (0x80131904): Case expressions may only be nested to level 10.
Case expressions may only be nested to level 10.
Case expressions may only be nested to level 10.
Incorrect syntax near 'x5'.
at System.Data.SqlClient.SqlCommand.<>c.b__122_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsyncTState,TResult
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.IncludeCollectionAsync[TEntity,TRelated,TElement](Int32 includeId, INavigation navigation, INavigation inverseNavigation, IEntityType targetEntityType, IClrCollectionAccessor clrCollectionAccessor, IClrPropertySetter inverseClrPropertySetter, Boolean tracking, TEntity entity, Func1 relatedEntitiesFactory, Func3 joinPredicate, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.IncludeCompiler.IncludeLoadTreeNodeBase._AwaitMany(IReadOnlyList1 taskFactories)
at Microsoft.EntityFrameworkCore.Query.Internal.IncludeCompiler._IncludeAsyncTEntity

Is this the same issue, or should I raise a new one?
If this is the same issue, is there a workaround?

[maumar]

duplicate of #2881

[Yen-David]

@smitpatel, it's not really a duplicate. It was fixed in .Net Core 2.2 but appeared again in .Net Core 3.0! You must re-close it (close it again).

[smitpatel]

@Yen-David - Stop spamming all the issue linked with this. This issue is fixed in 5.0 milestone hence it will be released in next version of EF Core (which comes after EF Core 3.1). The fix is available in nightly builds. I am not sure from where you are getting idea that it was fixed in .NET Core 2.2. It was never fixed in any release of EF Core upto 3.1

[Yen-David]

@smitpatel, thank you for fixing it in a nightly build. I am currently staying with .Net Core 2.2 just for this to work. I will upgrade to .Net Core 3.1 by skipping .Net Core 3.0, thus avoiding this bug in 3.0. Trust me. I discovered this bug after my upgrade to 3.0, and rolled back to 2.2 for it to work again. The painful experience cannot be mistaken. I'll always remember it!

[maumar]

@ajcvickers we got a(nother) customer hitting this scenario - should we reconsider this for patch?

[smitpatel]

I looked at the SQL generated in the case. It is just used to convert to specific string based on value. Doing that using a client function can easily give the same result. Certainly generates much shorter SQL and negligible perf difference.

[maumar]

problem is that odata generates that query, and the query string looks fairly innocuous

[ajcvickers]

@maumar @smitpatel I'll remove from the milestone and we can discuss on Friday.

[smitpatel]

This has not been merged to release/3.1