EF Core error Incorrect syntax near '$' but EF Core generated the query?

Question

Using .NET 8 RC2 and EF Core

I created a dynamic query and executed the query which results in the following error

Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '$'.
Incorrect syntax near '$'.
Incorrect syntax near '$'.

If I run .ToQueryString() to see the query generated I get this.

DECLARE @__startDate_0 smalldatetime = '2023-10-26T00:00:00';
DECLARE @__endDate_1 smalldatetime = '2023-10-26T00:00:00';
DECLARE @__siteNos_2 nvarchar(4000) = N'[15,42,56,74,89,98,102,104,109,113,114,115,116,118,120,121,122,123,124,124,125,127,128,129,130,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,156,157,158,159,161,162,163,164,165,167,169,170,171,172,173,175,176,177,178,179,180,181,182,183,185,186,187,188,189,190,191,192,193,194,195,196,197,199,200,201,202,205,206,207,208,209,210,211,212,213,214,215,216,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,244,245,246,247,248,249,250,251,252,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,295,296,296,297,300,301,303,304,305,306,307,308,309,310,311,312,313,316,317,318,319,320,321,322,323,324,325,326,328,331,333,334,335,338,339,340,341,342,343]';
DECLARE @__startTime_3 time = '07:00:00';  
DECLARE @__endTime_4 time = '22:00:00';  
DECLARE @__upperLimit_5 smallint = CAST(30000 AS smallint);  
DECLARE @__lowerLimit_6 smallint = CAST(0 AS smallint);
DECLARE @__stationIds_7 nvarchar(4000) = N'[117,116,98]';
DECLARE @__courseIds_8 nvarchar(4000) = N'[7]';

SELECT [o].[order_no], [o].[Bumped], [o].[Check_no], [o].[checkback], [o].[Course], [o].[Expoed], [o].[item_count], [o].[manual_hold], [o].[ODate], [o].[Order_DateTime], [o].[Order_Index], [o].[Server_no], [o].[Site_no], [o].[StartTime], [o].[Station_no], [o].[Table_no]
FROM [Orders] AS [o]
WHERE [o].[Order_DateTime] IS NOT NULL AND CONVERT(date, [o].[Order_DateTime]) >= @__startDate_0 AND CONVERT(date, [o].[Order_DateTime]) <= @__endDate_1 AND [o].[Site_no] IN (
SELECT [s].[value]
FROM OPENJSON(@__siteNos_2) WITH ([value] smallint '$') AS [s]  
) AND [o].[item_count] > CAST(0 AS tinyint) AND CONVERT(time, [o].[Order_DateTime]) >= @__startTime_3 AND CONVERT(time, [o].[Order_DateTime]) <= @__endTime_4 AND [o].[Expoed] <= @__upperLimit_5 AND [o].[Expoed] >= @__lowerLimit_6 AND CAST([o].[Station_no] AS smallint) NOT IN (
SELECT [s0].[value]
FROM OPENJSON(@__stationIds_7) WITH ([value] smallint '$') AS [s0]
 ) AND CAST([o].[Course] AS smallint) IN (
SELECT [c].[value]
FROM OPENJSON(@__courseIds_8) WITH ([value] smallint '$') AS [c]
)

I can see the '$' which I am assuming EF Core is complaining about but EF core generated the query so how would I fix this?

Answer 1

This is due to breaking change in the query translation for EF Core 8 - see Contains in LINQ queries may stop working on older SQL Server versions.

Check the compatibility level of the database:

SELECT compatibility_level
FROM sys.databases
WHERE name = 'mydbname';

I see some databases are 110 and newer ones we created are 150

Pass the values to the context options (when registering in DI with AddDbContext for example):

opts
   .UseSqlServer(@"<CONNECTION STRING>", o => o.UseCompatibilityLevel(110)) // or 150

See the Mitigations part of the breaking change doc.

Also I would argue that you should consider upgrading the database (and server if needed) to support new features (see ALTER DATABASE SET COMPATIBILITY_LEVEL).

Answer 2

If your level of compatibilty on the database is just low because of some historical reasons, but you are actually running SQL Server 2016 or newer, you can do:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 140;

Would solve the problem at the root without changing code in - how many? - projects.