How to make EF core translate custom sort into a plain 'When Then' instead of hierarchical

c# entity-framework-core linq sql-server

Question

The problem is - I get the following errror:

Case expressions may only be nested to level 10.

when I try using the following LINQ code with EF core

var extendedResult = result.Select(p => new
    {
        readModelFields = p,
        sortKey =
            p.CaseState == CaseState.Scheduled.ToString() ? 1 :
            p.CaseState == CaseState.Queued.ToString() ? 2 :
            p.CaseState == "Investigation" ? 3 :
            p.CaseState == "Awaiting Customer" ? 4 :
            p.CaseState == "State 5" ? 5 :
            p.CaseState == "State 6" ? 6 :
            p.CaseState == "State 7" ? 7 :
            p.CaseState == "State 8" ? 8 :
            p.CaseState == "State 9" ? 9 :
            p.CaseState == "State 10" ? 10 :
            p.CaseState == "State 11" ? 11 :
            p.CaseState == "Rejected" ? 12 :
            p.CaseState == "Blocked" ? 13 :
            p.CaseState == "Postponed" ? 14 :
            15
    });

    return IsAscending(query.SortDirection)
        ? extendedResult.OrderBy(p => p.sortKey).Select(p => p.readModelFields)
        : extendedResult.OrderByDescending(p => p.sortKey).Select(p => p.readModelFields);

And the issue is caused by the way SQL genertes SQL (here's a result)

exec sp_executesql N'SELECT [o].[Id], [o].[Owner], [o].[CaseState],  [o].[LastActionDate], [o].[CountryCode], [o].[Name], [o].[Risk]
FROM [dbo].[OpenCases] AS [o]
ORDER BY CASE
    WHEN [o].[CaseState] = N''Scheduled'' THEN 1
    ELSE CASE
        WHEN [o].[CaseState] = N''Queued'' THEN 2
        ELSE CASE
            WHEN [o].[CaseState] = N''Investigation'' THEN 3
            ELSE CASE
                WHEN [o].[CaseState] = N''Awaiting Customer'' THEN 4
                ELSE CASE
                    WHEN [o].[CaseState] = N''State 6'' THEN 6
                    ELSE CASE
                        WHEN [o].[CaseState] = N''State 7'' THEN 7
                        ELSE CASE
                            WHEN [o].[CaseState] = N''State 8'' THEN 8
                            ELSE CASE
                                WHEN [o].[CaseState] = N''State 9'' THEN 9
                                ELSE CASE
                                    WHEN [o].[CaseState] = N''State 10'' THEN 10
                                    ELSE CASE
                                        WHEN [o].[CaseState] = N''State 11'' THEN 11
                                        ELSE CASE
                                            WHEN [o].[CaseState] = N''Rejected'' THEN 12
                                            ELSE CASE
                                                WHEN [o].[CaseState] = N''Blocked'' THEN 13
                                                ELSE CASE
                                                    WHEN [o].[CaseState] = N''Postponed'' THEN 14
                                                    ELSE 15
                                                END
                                            END
                                        END
                                    END
                                END
                            END
                        END
                    END
                END
            END
        END
    END
END DESC
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=2147483647

What I want instead - is EF to build a query like this:

...
ORDER BY 
CASE
   WHEN CaseState = "Investigation" THEN 3
   WHEN CaseState = "State 5" THEN 5
   WHEN CaseState = "State 6" THEN 6
   WHEN CaseState = "State 7" THEN 7
   .......
   WHEN CaseState = "Postponed" THEN 14
END desc
1
1
1/1/2020 11:17:19 AM

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)
1
1/1/2020 4:00:07 PM

Popular Answer

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 Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow