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 Department
. Location
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?