MongoDB中如何基于多组LocationFilter过滤用户集合？

问题背景 #

我们有如下User集合的类定义：

public class User
{
    public Guid UserId { get;set; }
    public Guid CompanyId { get;set; }
    public Guid AddressTypeId { get;set; }
    public Guid LocationId { get;set; }
    public Guid DepartmentId { get;set; }
}

需要实现一个查询API，要求返回指定CompanyId且匹配任意一组LocationFilter的用户列表，LocationFilter定义如下：

public class LocationFilter
{
    public Guid AddressTypeId { get;set; }
    public ICollection<Guid> LocationIds { get;set; }
    public ICollection<Guid> DepartmentIds { get;set; }
}

查询API签名：

Task<List<User>> GetUserLocationList(
  Guid companyId, 
  List<LocationFilter> locationFilters);

已有单组LocationFilter的查询代码：

var companyIdFilter = Builders<User>.Filter
  .Eq(user => user.CompanyId, companyId);
var addressTypeIdFilter = Builders<User>.Filter
  .Eq(user => user.AddressTypeId, addressTypeId);
var locationIdFilter = Builders<User>.Filter
  .In(user => user.LocationId, locationIds);
var departmentIdFilter = Builders<User>.Filter
  .In(user => user.DepartmentId, departmentIds);

var andFilter = Builders<User>.Filter
  .And(
    companyIdFilter, 
    addressTypeIdFilter,
    locationIdFilter,
    departmentIdFilter);

var result = await collection.Find(andFilter).ToListAsync();

现在需要扩展为支持多组LocationFilter的查询，每组LocationFilter是独立的匹配规则（用户只需满足其中任意一组即可），如何用MongoDB的Filter Builder实现？该方案是否可行？

实现方案 #

核心思路是：

• 所有用户必须满足CompanyId匹配的基础条件
• 每组LocationFilter对应一组(AddressTypeId匹配) AND (LocationId在指定列表) AND (DepartmentId在指定列表)的过滤规则
• 最终查询条件为：CompanyId匹配 AND (规则1 **OR** 规则2 **OR** ... **OR** 规则N)

具体代码实现如下：

public async Task<List<User>> GetUserLocationList(Guid companyId, List<LocationFilter> locationFilters)
{
    // 1. 构建CompanyId的基础过滤条件
    var companyFilter = Builders<User>.Filter.Eq(u => u.CompanyId, companyId);

    // 2. 为每个LocationFilter生成对应的子过滤规则
    var filterGroups = new List<FilterDefinition<User>>();
    foreach (var filter in locationFilters)
    {
        var addressTypeFilter = Builders<User>.Filter.Eq(u => u.AddressTypeId, filter.AddressTypeId);
        var locationFilter = Builders<User>.Filter.In(u => u.LocationId, filter.LocationIds);
        var departmentFilter = Builders<User>.Filter.In(u => u.DepartmentId, filter.DepartmentIds);
        
        // 单组LocationFilter的AND组合
        var groupFilter = Builders<User>.Filter.And(addressTypeFilter, locationFilter, departmentFilter);
        filterGroups.Add(groupFilter);
    }

    // 3. 将所有子规则用OR组合
    var combinedLocationFilter = Builders<User>.Filter.Or(filterGroups);

    // 4. 最终过滤条件：CompanyId匹配 AND 任意一组LocationFilter匹配
    var finalFilter = Builders<User>.Filter.And(companyFilter, combinedLocationFilter);

    // 5. 执行查询
    return await collection.Find(finalFilter).ToListAsync();
}

可行性分析 #

该方案完全可行：

1. MongoDB的查询引擎原生支持嵌套的AND/OR过滤组合，能够高效解析并执行这种条件
2. 逻辑完全符合业务需求：用户必须属于指定公司，同时满足任意一组地址-位置-部门的匹配规则
3. 代码结构清晰，复用了单组过滤的逻辑，易于维护和扩展
4. 如果LocationFilter的数量较多，可通过创建CompanyId、AddressTypeId、LocationId、DepartmentId的复合索引来优化查询效率

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