Sunday 1 April 2012

QueryOver restriction using an alias

Adding a restriction on a where clause from an alias can be tricky to understand using QueryOver. The SQL we are trying to achieve is:-
from projects p
inner  join user u on p.UserId = u.UserId
where m.UserId = 100 or p.OwnerId = 100
Our goal is to achieve the following:-
  • We have a project that has a owner and a list of managers.
  • Both managers and owners are users
  • We want to get all projects for a user that they are the owner OR the manager
  • We want to use QueryOver
The domain:-
public class User {
    long Id;
    string Name;
}

public class Project {
    long Id;
    User Owner;
    IList<User> Managers;
}
The query is built like this:-
User manager = null;

var query = session
  .QueryOver<Project>()
  .JoinAlias(j => j.Managers, () => manager)
  .Where(w => manager.Id == user1.Id || w.Owner.Id == user1.Id)
  .List<Project>();
The above query joins Project to Managers using an alias manager. This allows us to then use the alias manager in the where statement e.g.
.Where(w => manager.Id == user1.Id || w.Owner.Id == user1.Id)
This works correctly but could have a small glitch. What happens if our domain contains a user that is a owner BUT is not a manager? The problem is the SQL will be created using an inner join. This problem can be fixed by simply adding a LEFT join to the query.
.JoinAlias(j => j.Managers, () => manager).Left
It should be noted that this was an answer I gave to a recent StackOverFlow question.

No comments:

Post a Comment