Quick Fix: Handle the compatibility level change in EF Core 8 related to $ usage in LINQ queries. Check the database version, set the appropriate compatibility level while configuring the DbContext, and consider upgrading the database and server if possible.
The Problem:
An EF Core generated dynamic query is causing a ‘Incorrect syntax near ‘\(\” error when executed. The SQL syntax generated contains the character ‘\)‘ in multiple places, which seems to be the cause of the error. The query involves multiple parameters and JSON data for filtering.
The Solutions:
Solution 1: Check Database Compatibility Level and Set it Accordingly
Determine the compatibility level of your database using the following query:
SELECT compatibility_level
FROM sys.databases
WHERE name = 'mydbname';
If the compatibility level is below 110, update the connection options when registering the context in DI with AddDbContext:
opts
.UseSqlServer(@"<CONNECTION STRING>", o => o.UseCompatibilityLevel(110)) // or 150
Solution 2: Change SQL compatibility level to 140 or higher
If the SQL server compatibility level is set below 140, which is the version that introduced JSON support, it can cause the
“Incorrect syntax near '$'” error when using JSON functions in EF Core queries. To resolve this issue, you can change the SQL server compatibility level to 140 or higher.
To change the SQL server compatibility level, connect to the database server using SSMS (SQL Server Management Studio) or another database management tool. In the Object Explorer, right-click on the database name and select “Properties”. In the “Options” page, under “Compatibility level”, select “SQL Server 2017 (140)” or a higher version.
After changing the compatibility level, the error should disappear, and the query should execute successfully.
Q&A
Why EF Core is producing the ‘$ symbols in generated SQL?
—
Due to breaking change in EF Core 8 related to query translation compatibility level.
EF Core generated the query with ‘$’ symbols which is not valid SQL.
—
EF Core 8 considers compatibility level of the database (e.g. 110).
Is there any mitigation strategy?
—
Yes, we can either change the server version to later (e.g. 2016 or newer) or change query translation compatibility level to 110.
Video Explanation:
The following video, titled "MS SQL - Fix Error - Incorrect syntax near the keyword 'VIEW ...", provides additional insights and in-depth exploration related to the topics discussed in this post.
More Info http://howtodomssqlcsharpexcelaccess.blogspot.ca/2017/03/ms-sql-fix-error-incorrect-syntax-near.html.
