nHIbernate query with conditional join

Question

I'm trying to get an nHibernate query to work without success. Here is my data structure:

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 List<Permission> Permissions {get; set;}
}

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

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

So the idea is, that a user has certain permissions and can only see data, where the permissions match.

So as a result I want all MainTypes, which have at least one of the users permissions and all Subtypes where the Modifier has at least one of the Users permissions.

If no Subtypes match the condition, I want only the MainType without the subtype. And exactly this is where I fail.

Can sombody help me to write that query with fluent nHibernate?

What I have so far is:

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

This query returns me a MainType with a SubType as long as the Modifier of the Subtype has the required permissions. If not, null is returned which is not what I want.

Is this even possible with nHibernate?

My SQL query, which does what I want, looks like this:

SELECT * FROM MainType mt
LEFT JOIN MainType_Permission_Map mtpm ON mtpm.MainType_Id = mt.Id
LEFT JOIN Permission mtp ON mtp.Id = mtpm.Permission_Id
LEFT JOIN (
    SELECT st.* FROM SubType st
    LEFT JOIN Modifier m ON m.Id = st.Modifier_Id
    LEFT JOIN Modifier_Permission_Map mpm ON mpm.Modifier_Id = m.Id
    LEFT JOIN DataPermission stp ON stp.Id = mpm.Permission_Id
    WHERE stp.Id IN ('userPermissions')
) subquery ON subquery.MainType_Id = s.Id
WHERE mtp.id IN ('userPermissions')

Answer 1

Well, I haven't gotten an answer here but this is how I solved it The detached criteria creates a subquery which I can use in the left join

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;

DetachedCriteria subquery = DetachedCriteria.For<Modifier>("mod")
                    .CreateAlias("pde.Permissions", "p", JoinType.LeftOuterJoin)
                    .Add(Restrictions.In("p.GlobalKey", userPermissions))
                    .SetProjection(Projections.Property("pde.Id"));

DetachedCriteria subtypeFilter = DetachedCriteria.For<SubType >("subtype")
                .Add(Subqueries.PropertyIn("subtype.Modifier.Id", subquery))
                .SetProjection(Projections.Distinct(Projections.Property("subtype.Id")));

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