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
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)
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.