Hello,
Before posting I've tried searching for the answer in this forum with no
result.
I've many-to-many between two tables. The example schema is:
TABLE Users
UserId,
UserName
TABLE Roles
RoleId,
RoleName
TABLE UserRoles
UserId,
RoleId
Entity classes are:
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;}
}
The Fluent.NHibernate mapping is as following:
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");
}
}
Now,
I like to get list of user ids that belong to the role with id that is 15.
What I built is this query:
IList<int> list = Session.QueryOver<UserEntity>()
.Inner.JoinQueryOver<RoleEntity>(u => u.Roles)
.Where(r => r.Id == 15)
.Select(u => u.Id)
.List<int>();
In the NHibernate profile I see the following query been generated:
SELECT this_.UserId as y0_
FROM dbo.Users this_
inner join dbo.UserRoles assignedus3_
on this_.UserId = assignedus3_.UserId
inner join dbo.Roles userinfoen1_
on assignedus3_.RoleId = userinfoen1_.RoleId
WHERE userinfoen1_.RoleId = 15 /* @p0 */
and I like to have it as:
SELECT this_.UserId as y0_
FROM dbo.Users this_
inner join dbo.UserRoles assignedus3_
on this_.UserId = assignedus3_.UserId
WHERE assignedus3_.RoleId = 15 /* @p0 */
Can anybody advise a change in mapping or query building that will result
in the above query and save me one obsolete join?
Thank you,
LeoY
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
