nHibernate complex join

Question

I have a need to perform a complex left join on a table and am unsure how to write the code using a criteria query. Currently I have:

 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>();
    }

which produces:

SELECT * FROM [dbo].[RezolutionConfig] this_ inner join [dbo].[RezolutionConfigCategory] rcc1_ on this_.[RezolutionConfigCategoryId]=rcc1_.[RezolutionConfigCategoryId] inner join [dbo].[RezolutionProductType] rpt2_ on this_.[RezolutionProductTypeId]=rpt2_.[RezolutionProductTypeId] left outer join [dbo].[RezolutionManagerConfig] rmc3_ on this_.[RezolutionConfigID]=rmc3_.[RezolutionConfigID] left outer join [dbo].[Manager] m4_ on rmc3_.[ManagerID]=m4_.[ManagerID] WHERE rcc1_.[RezolutionConfigCategoryId] = 1 and rpt2_.[RezolutionProductTypeId] = 1 and m4_.[ManagerID] = 9135

What I need to produce is

SELECT * FROM [dbo].[RezolutionConfig] this_ inner join [dbo].[RezolutionConfigCategory] rcc1_ on this_.[RezolutionConfigCategoryId]=rcc1_.[RezolutionConfigCategoryId] inner join [dbo].[RezolutionProductType] rpt2_ on this_.[RezolutionProductTypeId]=rpt2_.[RezolutionProductTypeId] left outer join [dbo].[RezolutionManagerConfig] rmc3_ on this_.[RezolutionConfigID]=rmc3_.[RezolutionConfigID] left outer join [dbo].[Manager] m4_ on rmc3_.[ManagerID]=m4_.[ManagerID] and m4_.[ManagerID] = 9135 WHERE rcc1_.[RezolutionConfigCategoryId] = 1 and rpt2_.[RezolutionProductTypeId] = 1

Answer 1

NHibernate doesn't support adding a constraint to a join as you would like: left outer join [dbo].[Manager] m4_ on rmc3_.[ManagerID]=m4_.[ManagerID] and m4_.[ManagerID] = 9135. I think your best approach will be to write the query in HQL and use a subquery to constrain Manager.