Make query with one join instead of two

Question

I've very simple data schema of two tables with many-to-many relationship:

CREATE TABLE Users
(
 UserId int,
 UserName varchar
)

CREATE TABLE Roles
(
 RoleId int,
 RoleName varchar
)

CREATE TABLE UserRoles
(
 UserId int,
 RoleId int
)

The data model and fluent mapping are also straightforward:

class UserEntity
{
  public virtual int Id {get; set;}
  public virtual string Name {get; set;}
  public virtual IList<RoleEntity> Roles {get; set;}
}
class RoleEntity
{
  public virtual int Id {get; set;}
  public virtual string Name {get; set;}
  public virtual IList<UserEntity> Users {get; set;}
}

class UserEntityMap : ClassMap<UserEntity>
{
  public UserEntityMap()
  {
    Table("Users");
    Id(x => x.Id).Column("UserId");
    Map(x => x.Name).Column("UserName");
    HasManyToMany(x => x.Roles)
       .Table("dbo.UserRoles").ParentKeyColumn("UserId").ChildKeyColumn("RoleId");
  }
}
class RoleEntityMap : ClassMap<RoleEntity>
{
  public RoleEntityMap()
  {
    Table("Roles");
    Id(x => x.Id).Column("RoleId");
    Map(x => x.Name).Column("RoleName");
    HasManyToMany(x => x.Users)
       .Table("dbo.UserRoles").ParentKeyColumn("RoleId").ChildKeyColumn("UserId");
  }
}

I like to query for all UserId that belong to role with RoleId 15 using NH Query. I do the following:

IList<int> list = Session.QueryOver<UserEntity>()
  .Inner.JoinQueryOver<RoleEntity>(u => u.Roles)
  .Where(r => r.Id == 15)
  .Select(u => u.Id)
  .List<int>();

The NHibernate Profiler shows that resulted SQL query is:

SELECT this_.UserId as y0_
FROM   dbo.Users this_
       inner join dbo.UserRoles userroles3_
         on this_.UserId = userroles3_.UserId
       inner join dbo.Roles user1_
         on userroles3_.RoleId = user1_.RoleId
WHERE  user1_.RoleId = 15 /* @p0 */

Please, advise how I can change the mapping or the query to have resulted SQL to have a single join, like this:

SELECT this_.UserId as y0_
FROM   dbo.Users this_
       inner join dbo.UserRoles userroles3_
         on this_.UserId = userroles3_.UserId
WHERE  userroles3_.RoleId = 15 /* @p0 */

*Do not use ManyToMany, would be my suggestion. Check http://stackoverflow.com/a/22563990/1679310* — Radim Köhler, Apr 22 '15 at 06:43
I've to disagree with you. The statement about many-to-many relation frequency is bogus and has no proof what-so-ever. Also, the post you reference doesn't allow me to access a collection of roles by a two SELECT queries (second implicitly invoked by NH) — Leo Y, Apr 22 '15 at 11:47

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

I've found this post on StackOverflow that contains possible solution. The solution is to add an entity for UserRoles table and simply query over it like

IList<int> list = Session.QueryOver<UserRolesEntity>()
  .Where(ur => ur.RoleId == 15)
  .Select(ur => ur.UserId)
  .List<int>();

edited May 23 '17 at 11:58

Community

1
1

answered Apr 22 '15 at 11:12

Leo Y

659
7
22

Funny, you've solved it by avoiding the many-to-many and explicit mapping of the pairing object... funny ;) – Radim Köhler Apr 22 '15 at 13:36
As I mentioned earlier, it isn't a solution I was looking for because it is adding a new entity. As well, this solution doesn't make use of User or Role entities at all. It doesn't however, in any way support the previously discussed subject. It only shows that NH doesn't have hint/rule mechanism for tuning underlying SQL queries. – Leo Y Apr 22 '15 at 14:25
Another issue with this approach is that usually the linked tables don't have dedicated id field which requires using NH composite keys... – Leo Y Apr 25 '15 at 11:01

Make query with one join instead of two

1 Answers1