Inefficient query with conditional expressions

[Greg]

When using conditional expressions with the following pattern

... cond1 ? val1 : cond2 ? val2 : cond3 ? val3 : ...

or with expression syntax:

... Expression.Condition(cond1, val1,
      Expression.Condition(cond2, val2,
        Expression.Condition(cond3, val3 ...
        )
      )
    ) ...

this generates something like the following:

... CASE WHEN cond1 THEN val1 ELSE (CASE WHEN cond2 THEN val2 ELSE (CASE WHEN cond3 THEN val3 ... END) END) END) ...

There is a T-SQL limit that CASE statements can only be nested 10 times that is very easy to hit by this pattern. However the nested statements can be collapsed into a single statement:

... CASE WHEN cond1 THEN val1 WHEN cond2 THEN val2 WHEN cond3 THEN val3 ... END ...

which no longer triggers this limit. Entity Framework actually does this.
A possible workaround is to use:

... (cond1 ? val1 : null) ?? (cond2 ? val2 : null) ?? (cond3 ? val3 : null) ...

this bloats the query. Another possible workaround is to use ExtensionMethods.SQL which is not very linqish and error-prone.

[Viktor Zhivkov]

Hi Greg,

I am sorry for the inconvenience you have experienced.
Can you define your use case scenario where you need more than 10 chained conditional operators?
Are you using these in your WHERE filter or in SELECT clause?

We will need this information in order to have better context when analyzing and evaluating this limitation and the way we can overcome it in our product.

I will make sure to contact you back when we have any status update on the described issue.

Regards,
Viktor Zhivkov
Telerik

 

 

[Greg]

Hi!

I have an arbitrary (but very small) number of status codes and the localized strings for these are in resx dictionaries. I need server-side paging of records ordered by the localized status name where the culture is determined by the client request. This is implemented by pre-sorting the status codes and substituting them with the index in the sorted array. E.g. case status when 'code1' then 0 when 'code2' then 1 ... end. So to answer your question I'm using these in the OrderBy clause.
This has been working nice for quite a long time up to the point where I actually used it for a status code that has more than 10 possible values. I also use hierarchical codes e.g. 3 status types with 4 subcodes each (for a total of 12 cases) that would use a single level of nesting. I cannot devise a case when more than 10 levels of nesting is a must and even if I could these can be collapsed into fewer levels with a trade-off of increased variable count and a longer command text.

[Greg]

I'm using it in the ORDER BY clause. It's basically a mapping of status codes to indexes where the index is the position of the specific status code when sorted by it's localized string representation. This is implemented by chained conditionals e.g. c.status == 6 ? 0 : c.status = 2 ? 1 : c.status=5 ? 2 : ... These codes have a very limited set of possible values (a handful at most), but that is still enough to trigger this problem. I also use composite status codes e.g. 3 status types with 4 substatus codes each (a total of 12 cases), which would require a single level of nesting. I cannot devise a scenario where more than a couple of levels are needed, but even if there are it could also be written as a flat case at the expense of additional query variables and longer command text. That is if the current OA implementation would not generate one additional nesting level per case.

[Greg]

Sorry, double post, the first returned a 500 error. Please remove the second.

[Viktor Zhivkov]

Hello Greg,

Based on your description I can see three possible ways to work around the issue:

1. Implement SQL Function that calculates the status and define a mapped C# function that can be used in LINQ
2. Postpone the status calculation to be executed in-memory if it is possible. If you have to load some localized strings based on these values you may be able to utilize Level 2 Caching in order to limit the number of queries required.
3. Use as you have suggested "[raw sql statements]".SQL<int>() method to define a "SQL island" inside your query.

If you are unable to implement any of these suggestions or have any questions, please let us know.

Regards,
Viktor Zhivkov
Telerik