如何用Fluent NHibernate实现带权限过滤的条件关联查询？

数据结构 #

public class Permission {
    public Guid Id {get; set;}
    public string Name  {get; set;}
}

public class MainType {
    public Guid Id {get; set;}
    public string Name {get; set;}
    public string GlobalKey {get; set;} // 对应查询中的GlobalKey条件
    public List<Permission> Permissions {get; set;}
    public List<SubType> SubTypes {get; set;}
}

public class SubType {
    public Guid Id {get; set;}
    public string Name  {get; set;}
    public Modifier Modifier {get; set;}
    public MainType MainType {get; set;}
}

public class Modifier {
    public Guid Id {get; set;}
    public string Name  {get; set;}
    public List<Permission> Permissions {get; set;}
}

需求 #

• 返回所有至少拥有一个用户权限的MainType
• 同时返回这些MainType下，其关联Modifier至少拥有一个用户权限的SubType
• 若MainType无符合条件的SubType，仅返回MainType本身

现有问题查询 #

当前的QueryOver查询在SubType的Modifier无匹配权限时返回null，不符合需求：

ICriterion permissionFilter = Restrictions.Where<DataPermission>(x => x.Id.IsIn(userPermissions));

Permission permissionOnMainType = null;
Permission permissionOnSubType = null;
MainType mainType = null;
SubType subType = null;
Modifier modifier = null;

IFutureEnumerable<MainType> mainTypes = session.QueryOver(() => mainType)
                .Where(x => x.GlobalKey == mainTypeKey)
                .Left.JoinQueryOver(() => mainType.Permissions, () => permissionOnMainType, permissionFilter)
                .Left.JoinQueryOver(() => mainType.SubTypes, () => subType)
                .Left.JoinQueryOver(() => subType.Modifier, () => modifier)
                .Left.JoinQueryOver(() => modifier.Permissions, () => permissionOnSubType, permissionFilter)
                .TransformUsing(Transformers.DistinctRootEntity)
                .Future();

正确的QueryOver实现 #

对应你提供的SQL子查询逻辑，我们可以用QueryOver的子查询来筛选符合条件的SubType，再关联到MainType：

// 提取用户权限ID列表
var userPermissionIds = userPermissions.Select(p => p.Id).ToList();

// 1. 构建子查询：筛选Modifier拥有用户权限的SubType
SubType subTypeAlias = null;
Modifier modifierAlias = null;
Permission subPermissionAlias = null;

var eligibleSubTypesSubquery = QueryOver.Of(() => subTypeAlias)
    .JoinAlias(() => subTypeAlias.Modifier, () => modifierAlias)
    .JoinAlias(() => modifierAlias.Permissions, () => subPermissionAlias)
    .Where(() => subPermissionAlias.Id.IsIn(userPermissionIds))
    .Select(() => subTypeAlias.Id);

// 2. 主查询：筛选拥有权限的MainType，并左连接符合条件的SubType
MainType mainTypeAlias = null;
Permission mainPermissionAlias = null;

var result = session.QueryOver(() => mainTypeAlias)
    .Where(x => x.GlobalKey == mainTypeKey)
    // 确保MainType自身拥有用户权限
    .JoinAlias(() => mainTypeAlias.Permissions, () => mainPermissionAlias)
    .Where(() => mainPermissionAlias.Id.IsIn(userPermissionIds))
    // 左连接到符合条件的SubType
    .Left.JoinQueryOver(() => mainTypeAlias.SubTypes, () => subTypeAlias)
    .WithSubquery.WhereProperty(() => subTypeAlias.Id).In(eligibleSubTypesSubquery)
    // 去重，避免因多个SubType导致MainType重复
    .TransformUsing(Transformers.DistinctRootEntity)
    .Future<MainType>();

说明 #

• 子查询单独筛选出所有符合权限要求的SubType ID，避免主查询中多表左连接导致的空值问题
• 主查询先通过内连接确保MainType自身权限匹配，再通过左连接关联符合条件的SubType，保证无匹配SubType时MainType仍能返回
• DistinctRootEntity用于处理一个MainType关联多个符合条件SubType时的重复问题

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