Remove parentheses by allowing providers to specify SQL operator precedence and associativity

[roji]

We currently generate parentheses in SQL around most binary expression, LIKE etc. Allow providers to provide the exact precedence of operators/expressions, to remove unnecessary parentheses.

The default relational behavior would still be conservative - as today - and defensively add parentheses in many cases. This makes it an opt-in provider feature.

[ErikEJ]

Would that mainly be about aesthetics?

[roji]

Yes, this is about removing unneeded parentheses. Where there's lots of binary operators, we generate quite a lot of these.

[axelgenus]

It's not an aesthetic issue alone. I am experiencing SQL Server returning "Some part of your SQL statement is nested too deeply" because of this.

I guess I need to use an interceptor to hijack and modify (regex?) the query before is actually sent to SQL Server.

[roji]

@axelgenus have you verified that simply removing the parentheses makes the error go away? If so, can you submit a code sample showing that?

[axelgenus]

@axelgenus have you verified that simply removing the parentheses makes the error go away?

Yes, removing the parentheses makes the error go away.

My test case is quite simple: in one of my projects I use path-like fields to define the ownership of each data row. When a client ask for some data, the web service extract the authorizations from cache and generate a dynamic LINQ lambda expression which, in turn, is applied to EF Core queries. The result SQL statement is something like this:

SELECT [...] WHERE ((((...(Owner LIKE 'keyword1/%' OR (Owner LIKE 'keyword2/%' OR [...] (Owner LIKE 'keywordN/%'))

which can be rewritten as:

SELECT [...] WHERE (Owner LIKE 'keyword1/%' OR Owner LIKE 'keyword2/%' OR [...] Owner LIKE 'keywordN/%')

Of course the count of nested parentheses depends on the assigned authorizations to the specific user. Some users have A LOT of authorizations (> 1k). I defined the tables with a clustered index on the field defining the rows ownership and the LIKE statements make use of the index so the performances are really good. Unfortunately SQL Server supports a limited number of nested parentheses.

I guess that EF Core introduces redundant parentheses mostly because this is the most safe way to handle SQL code generation.

If so, can you submit a code sample showing that?

I'll prepare a test project to reproduce the issue.

[smitpatel]

That is a skewed binary tree causing nesting. It has already been fixed in 7.0

[axelgenus]

That is a skewed binary tree causing nesting. It has already been fixed in 7.0

Is it possible to fix the behaviour in EFCore 6.0? I was planning on writing a command interceptor to remove redundant parentheses but if there is a "native" way I can make it work on current version of EFCore it would be better.

Can you please point me to the commit with the fix?

[ajcvickers]

Duplicate of #26767