Make a request with one connection instead of two

I have a very simple data schema of two tables with a 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 free mapping are also simple:

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 UserIds that belong to a role with RoleId 15 using NH Query. I am doing 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>();

      

NHibernate Profiler shows that the provided SQL query:

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 query so that SQL gives one connection, for example:

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

      

+3


source to share


1 answer


I found this post on StackOverflow which contains a possible solution. The solution is to add an object for the UserRoles table and just query it like



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

      

0


source







All Articles