NHibernate QueryOver Left outer join with conditions from a list

763 views
Skip to first unread message

bas

unread,
Jul 3, 2019, 7:26:17 AM7/3/19
to nhusers

I'm trying to create a query with NHibernate QueryOver using a left outer join which uses a collection as it's condition. A User can have different roles within an DepartmentLocation objects are added by users and are not department specific. Users of all departments can add a location as their FavouriteLocation.

My classes look like this:

public class Location
{
    public virtual long Id { get; set;
    public virtual string Name { get; set; }
    public virtual IList<FavouriteLocation> Favourites { get; set; }
}

public class User
{
    public virtual long Id { get; set;
    public virtual string Firstname { get; set; }
    public virtual string Lastname { get; set; }
    public IList<UserDepartmentRole> DepartmentRoles { get; set; }
}

public class FavouriteLocation
{
    public virtual long Id { get; set; }
    public virtual Location Location { get; set; }
    public virtual User { get; set; }
}

public class Department
{
    public virtual long Id { get; set; }
    public virtual string Name { get; set; }
}

public class UserDepartmentRole 
{
    public virtual long Id { get; set; }
    public virtual Guid RoleGuid { get; set; }
    public virtual User User { get; set; }
    public virtual Department Department { get; set }
    public virtual bool IsActive { get; set; }
}

I have a function which executes a query to retrieve a Location object with it's favourites stored by users of a specific department. I have tried this with the following query, but when the Location is not stored as a favourite the query returns null.

public Location GetLocation(long departmentId, long locationId)
{
    UserDepartmentRole departmentRoleAlias = null;
    FavouriteLocation favouriteAlias = null;

    var departmentUserSubQuery = QueryOver.Of<User>()
        .JoinAlias(user => user.DepartmentRoles, () => departmentRoleAlias)
        .Where(() => departmentRoleAlias.Department.Id == departmentId)
        .Where(() => departmentRoleAlias.IsActive)
        .Select(user => user.Id);

    var location = Session.QueryOver<Location>()
        .JoinAlias(l => l.Favourites, () => favouriteAlias, JoinType.LeftOuterJoin)
        .WithSubQuery.WhereProperty(() => favouriteAlias.User.Id)
        .In(departmentUserSubQuery)
        .Where(l => l.Id == locationId)
}

How can I solve this?


Reply all
Reply to author
Forward
0 new messages