EF Core 6中映射JSON_QUERY为DbFunction查询JSON数组的问题咨询

核心原因 #

JSON_VALUE仅支持提取JSON标量值（字符串、数字、布尔值、日期等），JSON_QUERY返回的是JSON数组/对象片段的序列化字符串：

• 将JSON_QUERY返回值映射为string时，直接链式调用.Contains()会被EF Core识别为CLR字符串方法，部分版本的SQL Server提供程序无法完成翻译；即使翻译成功，默认生成的LIKE '%xxx%'逻辑也存在子串误判问题（比如查询"Apple"会匹配到"Pineapple"）
• 将JSON_QUERY返回值改为string[]时，EF Core 6的SQL Server提供程序没有内置JSON数组到CLR数组的直接映射逻辑，会抛出提供程序不支持该返回类型的错误。

以下是两种经过验证的可行方案： #

方案1：轻量模糊匹配（适合低精度要求场景） #

现有JSON_QUERY的映射逻辑无需修改，显式使用EF.Functions.Like指定在数据库端执行字符串匹配即可：

// 基础写法：存在子串误匹配风险
var results = _context.Pages
    .Where(p => EF.Functions.Like(
        MyDbFunctions.JsonQuery(p._PublishedContent, "$.content"), 
        "%Apple%"))
    .ToList();

如果要降低误判概率，可以调整匹配模式为JSON元素的格式特征，匹配被双引号包裹的精确值：

// 优化写法：匹配被双引号包裹的元素，大幅降低误匹配概率
var results = _context.Pages
    .Where(p => EF.Functions.Like(
        MyDbFunctions.JsonQuery(p._PublishedContent, "$.content"), 
        "%\"Apple\"%"))
    .ToList();

该方案生成的SQL逻辑简单，性能较好，但无法100%规避转义字符带来的匹配误差。

方案2：精确元素匹配（生产环境推荐） #

通过映射SQL Server内置的OPENJSON函数，将JSON数组拆解为行集合后做精确匹配，完全避免模糊匹配的误差问题。

第一步：扩展自定义函数类 #

public static class MyDbFunctions
{
    [DbFunction("JSON_VALUE", Schema = "", IsBuiltIn = true)]
    public static string JsonValue(string source, [NotParameterized] string path) => throw new NotSupportedException();

    [DbFunction("JSON_QUERY", Schema = "", IsBuiltIn = true)]
    public static string JsonQuery(string source, [NotParameterized] string path) => throw new NotSupportedException();

    // 新增OPENJSON映射，用于拆解JSON数组
    [DbFunction("OPENJSON", Schema = "", IsBuiltIn = true)]
    public static IQueryable<string> OpenJson(string source, [NotParameterized] string path) => throw new NotSupportedException();
}

第二步：在DbContext中注册函数映射 #

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(() => MyDbFunctions.JsonValue(default(string), default(string)));
    modelBuilder.HasDbFunction(() => MyDbFunctions.JsonQuery(default(string), default(string)));
    // 配置OPENJSON的返回值映射
    modelBuilder.HasDbFunction(() => MyDbFunctions.OpenJson(default(string), default(string)))
        .HasTranslation(args => new SqlFunctionExpression(
            functionName: "OPENJSON",
            arguments: args,
            nullable: true,
            argumentsPropagateNullability: new [] { true, false },
            type: typeof(IQueryable<string>),
            typeMapping: new StringTypeMapping("nvarchar(max)", System.Data.DbType.String)));
}

第三步：编写精确匹配查询 #

var target = "Apple";
var results = _context.Pages
    .Where(p => MyDbFunctions.OpenJson(p._PublishedContent, "$.content")
        .Any(item => item == target))
    .ToList();

生成的SQL为原生精确判断逻辑，性能好且无匹配误差：

SELECT * FROM [Pages] p
WHERE EXISTS (
    SELECT 1 FROM OPENJSON(p.[_PublishedContent], '$.content')
    WHERE [value] = N'Apple'
)

注意事项 #

• 所有JSON路径参数必须保留[NotParameterized]特性，避免EF Core生成参数化路径触发SQL Server JSON语法错误
• 禁止在LINQ中先执行ToList()再在内存中拆分JSON数组，会触发全表加载导致严重性能问题
• SQL Server 2022及以上版本可以额外映射JSON_PATH_EXISTS函数，用更简洁的语法实现同等精确匹配效果，性能表现一致

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