如何用LINQ查询包含指定全部jsonb嵌套字段的Advisor实体？

针对你用EF Core处理PostgreSQL jsonb列的需求，这里提供两种可行的实现方式：

方法一：循环检查每个目标Id #

通过遍历queryParams.Directions中的每个Id，逐个添加Where条件，确保每个Id都存在于Advisor的TripCountries集合中：

var targetIds = queryParams.Directions;
if (targetIds == null || targetIds.Length == 0)
{
    // 空数组时返回所有顾问或空集合，按需调整
    return _dbContext.Advisors;
}

var query = _dbContext.Advisors.AsQueryable();

foreach (var id in targetIds)
{
    var currentId = id; // 避免循环闭包引用问题
    query = query.Where(advisor => EF.Functions.JsonContains(
        advisor.ExpertiseArea,
        JsonSerializer.Serialize(new { TripCountries = new[] { new { Id = currentId } } })
    ));
}

// 执行查询
var matchedAdvisors = await query.ToListAsync();

原理说明 #

EF.Functions.JsonContains会调用PostgreSQL的@>操作符，检查jsonb列是否包含指定的JSON片段。这里我们构造一个仅包含目标Id的TripCountries元素，以此判断该Id对应的国家是否存在于顾问的专长区域中。

方法二：使用JSONPath表达式批量检查 #

利用EF Core的JsonPathExists函数，结合JSONPath表达式一次性检查所有目标Id是否都存在：

var targetIds = queryParams.Directions;
if (targetIds == null || targetIds.Length == 0)
{
    return _dbContext.Advisors;
}

var query = _dbContext.Advisors
    .Where(advisor => targetIds.All(targetId => 
        EF.Functions.JsonPathExists(
            advisor.ExpertiseArea,
            $"$.TripCountries[*].Id ? (@ == {targetId})"
        )
    ));

var matchedAdvisors = await query.ToListAsync();

原理说明 #

JSONPath表达式$.TripCountries[*].Id ? (@ == {targetId})会扫描TripCountries数组中的所有Id字段，判断是否存在等于目标Id的元素。targetIds.All()确保所有目标Id都满足该条件。

注意事项 #

1. 确保你的EF Core版本在5.0及以上，这两个函数对PostgreSQL jsonb列的支持是从该版本开始完善的。
2. 若使用第一种方法，必须将循环变量赋值给局部变量，避免闭包导致的查询逻辑错误。
3. 空数组的处理逻辑可根据业务需求调整（例如返回所有顾问或空集合）。

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