如何在NHibernate DynamicJsonType映射中用IQueryable过滤数据

核心问题 #

你将集合数据序列化为JSON存储在数据库的varchar字段中，NHibernate的DynamicJsonType能正常处理序列化/反序列化，但无法把内存中的LINQ集合操作（如All/Any）正确翻译为针对JSON字段的SQL查询，导致生成的SQL完全不符合过滤需求。

可行解决方案 #

由于数据以JSON字符串形式存储，必须直接针对JSON字段编写SQL级别的过滤逻辑，而非依赖NHibernate对内存集合的LINQ翻译。以下是两种实用方案：

方案1：直接使用原生SQL片段过滤（以SQL Server为例） #

利用SQL Server的JSON处理函数（OPENJSON），拼接过滤条件并注入到NHibernate查询中：

if (filter.Competencias != null && filter.Competencias.Any())
{
    var competenciaIds = filter.Competencias.Select(c => c.Id).ToList();
    // 生成检查所有指定Id是否存在于JSON数组中的SQL条件
    var sqlFilter = string.Join(" AND ", competenciaIds.Select(id => 
        $"EXISTS(SELECT 1 FROM OPENJSON(function0_.competencia_jurisdicao) WITH (Id int) WHERE Id = {id})"));
    
    query = query.Where(Restrictions.Sql(sqlFilter));
}

方案2：封装LINQ扩展方法（保持代码风格一致性） #

自定义扩展方法，将集合过滤逻辑转换为对应的JSON SQL查询，同时支持参数化防止注入：

public static IQueryable<Function> HasAllCompetenciaIds(this IQueryable<Function> query, IEnumerable<int> competenciaIds)
{
    if (!competenciaIds.Any())
        return query;

    // 构建参数化查询，避免SQL注入
    var paramList = competenciaIds.Select((id, idx) => new SqlParameter($"@compId{idx}", id)).ToList();
    var conditionParts = paramList.Select((p, idx) => 
        $"EXISTS(SELECT 1 FROM OPENJSON(competencia_jurisdicao) WITH (Id int) WHERE Id = @compId{idx})");
    
    var combinedCondition = string.Join(" AND ", conditionParts);
    return query.Where($"({combinedCondition})", paramList.ToArray());
}

控制器中调用该扩展方法：

if (filter.Competencias != null)
{
    var targetIds = filter.Competencias.Select(c => c.Id).ToList();
    query = query.HasAllCompetenciaIds(targetIds);
}

原代码失效原因 #

原代码中o.Competencias.All(c => c.Id == competencia.Id)的逻辑，NHibernate会尝试将其翻译为针对数据库集合的SQL，但Competencias是从JSON反序列化得到的内存集合，NHibernate无法关联到数据库中的competencia_jurisdicao字段，最终生成了逻辑错误的子查询。

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