score:2

Accepted answer

You are experiencing EF Core query translation bug, tracked by #12729: Flatten CASE expressions to avoid error "Case expressions may only be nested to level 10". As we can see, it's already fixed in the EF Core repository, but the fix will be included in 5.0.0 release (whenever it comes).

It's possible to fix it by hooking into EF Core internal infrastructure, but that requires knowing the exact EF Core version and also use different approaches for different EF Core versions because the infrastructure is changing with almost every even minor release.

As a general workaround I could suggest replacing the single multi-conditional operator expression with sum of multiple single conditional expressions. It won't generate the desired flat CASE SQL expression, but at least won't produce the error in question. Something like this:

sortKey =
    (p.CaseState == CaseState.Scheduled.ToString() ? 1 : 0) +
    (p.CaseState == CaseState.Queued.ToString() ? 2 : 0) +
    (p.CaseState == "Investigation" ? 3 : 0) +
    (p.CaseState == "Awaiting Customer" ? 4 : 0) +
    (p.CaseState == "State 5" ? 5 : 0) + 
    (p.CaseState == "State 6" ? 6 : 0) +
    (p.CaseState == "State 7" ? 7 : 0) +
    (p.CaseState == "State 8" ? 8 : 0) +
    (p.CaseState == "State 9" ? 9 : 0) +
    (p.CaseState == "State 10" ? 10 : 0) +
    (p.CaseState == "State 11" ? 11 : 0) +
    (p.CaseState == "Rejected" ? 12 : 0) +
    (p.CaseState == "Blocked" ? 13 : 0) +
    (p.CaseState == "Postponed" ? 14 : 15)

score:0

How about using a string map to convert the states?

var stateMapString = "Investigation    Awaiting CustomerState 5          State 6          State 7          State 8          State 9          State 10         State 11         Rejected         Blocked          Postponed";

var extendedResult = result.Select(p => new
    {
        readModelFields = p,
        sortKey = p.CaseState == CaseState.Scheduled.ToString() ? 1 :
                  p.CaseState == CaseState.Queued.ToString() ? 2 :
                  (p.CaseState != "" && stateMapString.IndexOf(p.CaseState) >= 0)
                        ? stateMapString.IndexOf(p.CaseState)/17+3
                        : 15
    });

If your states might change, you could compute the map string:

var stateStrings = new[] { "Investigation",
                            "Awaiting Customer",
                            "State 5",
                            "State 6",
                            "State 7",
                            "State 8",
                            "State 9",
                            "State 10",
                            "State 11",
                            "Rejected",
                            "Blocked",
                            "Postponed" };
var stateMaxLen = stateStrings.Max(s => s.Length);
var stateMapString = String.Join("", stateStrings.Select(s => s.PadRight(stateMaxLen)));

And just use stateMaxLen in the query to divide the IndexOf result.


Related Query

More Query from same tag