Conditional Operator Causing Unoptimized Linq Queries

Spread the love

Today, I was debugging a .net core API. It was taking more than a minute to execute for a simple query which was fetching two-three related entities also. When I started going through the console for the generated SQL, I figured out that the query is split into three parts and each part was taking more than thirty seconds. When I read closely the generated queries, I found that the first query was actually a filter over records using ‘Then’ and ‘Else’, and to further fetch related entities the next two queries were used. When I scrutinized the LINQ for that SQL I found that I used a conditional operator in the query and that was causing the issue. So, to resolve that I created two different conditions for my condition.

Before:

Expression<Model, Func<bool>> condition = m => m.value == 1 && (whateverCondition ? m.value2 == 2 : m.value2 == 3)

After:

Expression<Model, Func<bool>> condition = m => m.value == 1;

if (whateverCondition) {
    condition.And(m => m.value2 == 2);
} else {
    condition.And(m => m.value2 == 3);
}

So never use conditional operator in expression for LINQ that will be converted to SQL.