Search code examples
c#nhibernatemany-to-manyqueryover

NHibernate QueryOver with Many-to-Many


I'm in the process of learning QueryOver, but I can't figure out how to do a simple many to many query.

I have three tables, Users, Roles, and a junction UserRoles.

Users       UserRoles         Roles
=======     =============     =========
UserId      UserId            RoleId
Username    RoleId            Role
Firstname   Date

My tables are joined like this:

Users.UserId -> UserRoles.UserId
Roles.RoleId -> UserRoles.RoleId 

I'm trying to select the role for a spefic username using QueryOver basically the alternative for:

SELECT 
    dbo.Roles.Role 
FROM dbo.Roles 
INNER JOIN dbo.UserRoles 
    ON dbo.Roles.RoleId = dbo.UserRoles.RoleId 
INNER JOIN dbo.Users 
    ON dbo.UserRoles.UserId = dbo.Users.UserId 
WHERE (Username = @Username)

Classes:

public class Roles
{
    public virtual int RoleId { get; set; }
    public virtual string Role { get; set; }
}
public class UserRoles
{
    public virtual int UserId { get; set; }
    public virtual int RoleId { get; set; }
    public virtual DateTime Date { get; set; }
}
public class Users
{
    public virtual int UserId { get; set; }
    public virtual string Username { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
    public virtual string Email { get; set; }
    public virtual DateTime Date { get; set; }
}

Mappings:

<class name="Roles">
<id name="RoleId">
  <generator class="native" />
</id>
<property name="Role" />
</class>

<class name="UserRoles">
<property name="UserId" />
<property name="RoleId" />
<property name="Date" />
</class>

<class name="Users">
<id name="UserId">
<generator class="native" />
</id>
<property name="Username" />
<property name="FirstName" />
<property name="LastName" />
<property name="Email" />
<property name="Date" />
</class>

I've tried something, but I'm not even close:

var result = session.QueryOver<Users>()
            .Right.JoinQueryOver<Roles>(x => x.UserId )
            .Where(c => c.RoleId == roleid)
            .List();

Solution

  • This join should just work actually. I think you want the users and not the roles. so you might want to use something like:

    var result = session.QueryOver<Users>()
                .Right.JoinQueryOver<Roles>(x => x.UserId )
                .Where(c => c.RoleId == roleid)
    .TransformUsing(Transformers.DistinctRootEntity)
    .List();