c# - nHibernate 复杂连接

Question

我需要对表执行复杂的左联接，但不确定如何使用条件查询编写代码。目前我有:

 public IList<RezolutionConfig> GetSearchConfigByManagerCategoryProduct(int ManagerId, int ProductTypeId, int ConfigCategoryId)
    {
        ICriteria criteria = Session.GetISession().CreateCriteria(typeof(RezolutionConfig))
            .CreateAlias("RezolutionConfigCategory", "rcc")
            .Add(Expression.Eq("rcc.id", ConfigCategoryId))
            .CreateAlias("RezolutionProductType","rpt")
            .Add(Expression.Eq("rpt.id", ProductTypeId))
            .CreateAlias("RezolutionManagerConfigs", "rmc", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
            .CreateAlias("rmc.Manager", "m", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
            .Add(Expression.Eq("m.id", ManagerId));

        return criteria.List<RezolutionConfig>();
    }

产生:

选择 *FROM [dbo].[RezolutionConfig] 这个_内部加入 [dbo].[RezolutionConfigCategory] ​​rcc1_ on this_.[RezolutionConfigCategoryId]=rcc1_.[RezolutionConfigCategoryId]inner join [dbo].[RezolutionProductType] rpt2_ on this_.[RezolutionProductTypeId]=rpt2_.[RezolutionProductTypeId]左外连接 [dbo].[RezolutionManagerConfig] rmc3_ on this_.[RezolutionConfigID]=rmc3_.[RezolutionConfigID]左外连接 [dbo].[Manager] m4_ on rmc3_.[ManagerID]=m4_.[ManagerID] WHERE rcc1_.[RezolutionConfigCategoryId] = 1和 rpt2_.[RezolutionProductTypeId] = 1和 m4_.[ManagerID] = 9135

我需要制作的是

选择 *FROM [dbo].[RezolutionConfig] 这个_内部加入 [dbo].[RezolutionConfigCategory] ​​rcc1_ on this_.[RezolutionConfigCategoryId]=rcc1_.[RezolutionConfigCategoryId]inner join [dbo].[RezolutionProductType] rpt2_ on this_.[RezolutionProductTypeId]=rpt2_.[RezolutionProductTypeId]左外连接 [dbo].[RezolutionManagerConfig] rmc3_ on this_.[RezolutionConfigID]=rmc3_.[RezolutionConfigID]左外连接 [dbo].[Manager] m4_ on rmc3_.[ManagerID]=m4_.[ManagerID] and m4_.[ManagerID] = 9135WHERE rcc1_.[RezolutionConfigCategoryId] = 1和 rpt2_.[RezolutionProductTypeId] = 1

Answer 1

NHibernate 不支持向连接添加约束，如您所愿:left outer join [dbo].[Manager] m4_ on rmc3_.[ManagerID]=m4_.[ManagerID] and m4_.[ManagerID ] = 9135。我认为您最好的方法是在 HQL 中编写查询并使用子查询来约束管理器。