Description
Hello,
the NHibernate.Criterion.ConditionalProjection currently supports only 1 "when" and the "else" of the known SQL switch-case-expression.
About switch-case, see https://www.w3schools.com/sql/sql_case.asp
Is there a specific reason why thats not available (compatibility?) and could it be please implemented in a future (soon) release?
Below i've stated a working example solution (i think).
Our use-case:
We want to "order by" our Enums with translation (from culture resources), so we use switch-case to map the Enum-value to its culture specific sorting index (determined by the enum value's translation).
Current state:
... ORDER BY case (when _this.BigEnum = @p10 then @p11 else (when _this.BigEnum = @p12 then @p13 else (when _this.BigEnum = @p14 then @p15 else (... else (.... else(... ) ) ) )
"p10" is the enum value ("Value10" or "Wert10" (german)) and "p11" its sorting index (10).
The problem:
This way, the maximum case-intendation of 10 for MS-SQL server and 18 for SQLite is reached really fast and aborted with an exception ("parser stack overflow").
Multiple "when" parts without intendation would solve that.
The possible solution:
I've already took some investigation in how to produce SQL with multiple "when-then" using Criteria with our own "ConditionalsProjection". See atttachement.
NHibernate Criterion ConditionalsProjection multi-when-case.zip
... ORDER BY case (when _this.BigEnum = @p10 then @p11 when _this.BigEnum = @p12 then @p13 when _this.BigEnum = @p14 then @p15 when ... then when... then... else ... )
My further questions:
- Is the provided solution working for other aspects/requirements (compatibility) i don't see?
- Are there further changes in other files required? (registrations, tests?)
I hope that's enough information to specify my point ;)
Thank you very much and have a nice day.
Edit: fixed missing file in attachement! Sorry /fail