.NET 3.1升级到8后EF查询使用list.Contains报SQL语法错误

将解决方案从.NET 3.1升级到.NET 8后，使用EF（Entity Framework）和Specification模式时，每次在Specification中使用list.Contains都会出现查询错误。

引发问题的代码示例 #

spec.AndCriteria(u => u.AppUserRoles.Any(ur => roles.Contains(ur.RoleId)));

转换后的LINQ表达式 #

DbSet<AppUser>()
   .Where(u => u.AppUserRoles
      .Any(ur => <>c__DisplayClass0_0.roles.Contains(ur.RoleId)))

生成的错误SQL语句 #

DECLARE @__roles_0 nvarchar(4000) = N'[1,2,3,5,6,7,8]';

SELECT [a].[Id], [a].[AccessFailedCount], [a].[ConcurrencyStamp], [a].[Email], [a].[EmailConfirmed], [a].[LastConnection], [a].[LockoutEnabled], [a].[LockoutEnd], [a].[MemberId], [a].[NormalizedEmail], [a].[NormalizedUserName], [a].[PasswordHash], [a].[PhoneNumber], [a].[PhoneNumberConfirmed], [a].[SecurityStamp], [a].[TwoFactorEnabled], [a].[UserName]
FROM [AspNetUsers] AS [a]
WHERE EXISTS (
    SELECT 1
    FROM [AspNetUserRoles] AS [a0]
    WHERE [a].[Id] = [a0].[UserId] AND [a0].[RoleId] IN (
        SELECT [r].[value]
        FROM OPENJSON(@__roles_0) WITH ([value] int '$') AS [r]
    ))

MSSMS执行错误 #

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '$'.

应用内错误信息 #

SqlException: Incorrect syntax near '$'.
Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__209_0(Task<SqlDataReader> result)

roles是整数ID列表，当前查询失效的原因是什么？

使用的包版本：

• microsoft.entityframeworkcore.design\8.0.0\
• microsoft.entityframeworkcore.sqlserver\8.0.0\
• microsoft.entityframeworkcore.tools\8.0.0\

核心原因 #

EF Core 8默认会将Contains查询中的整数列表序列化为JSON数组，再通过OPENJSON解析，但生成的WITH ([value] int '$')语法仅支持SQL Server 2016及以上版本。如果你的数据库版本低于2016，就会触发语法错误——低版本SQL Server不支持该OPENJSON用法，甚至可能完全不支持OPENJSON函数。

另外，Specification模式的变量捕获方式也可能导致EF无法正确识别roles变量，进而引发查询转换逻辑异常，生成无效SQL。

解决方案 #

1. 升级SQL Server版本
   若数据库版本低于SQL Server 2016，升级到支持OPENJSON的版本（2016及以上），即可兼容EF Core 8的默认查询转换逻辑。

2. 强制EF Core生成传统IN子句
   在DbContext的OnConfiguring方法中配置SQL Server选项，强制将Contains转换为传统的IN子句，避免使用OPENJSON：

   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   {
       optionsBuilder.UseSqlServer("你的数据库连接字符串", options =>
       {
           // 设置兼容级别为SQL Server 2016
           options.UseCompatibilityLevel(130);
           // 忽略JSON相关的版本兼容警告
           options.ConfigureWarnings(warnings => warnings.Ignore(SqlServerEventId.JsonColumnIncompatibleWithOldVersion));
       });
   }
   

   也可通过设置批量大小，让EF在列表长度达标时自动生成IN子句：

   optionsBuilder.UseSqlServer("你的数据库连接字符串", options =>
   {
       // 当列表元素数量≤1000时，生成IN子句
       options.MaxBatchSize(1000);
   });
   

3. 调整Specification的变量捕获逻辑
   确保roles变量被EF正确识别，可将其转换为数组或在Specification内部定义局部变量：

   var roleIds = roles.ToArray();
   spec.AndCriteria(u => u.AppUserRoles.Any(ur => roleIds.Contains(ur.RoleId)));
   

内容的提问来源于stack exchange，提问作者Tyler Durden