NHibernate CompositeId => too many queries

Question

I'm currently writing an application that accesses a legacy database. I'm using nhibernate as my ORM.

There are three tables in the DB that represent an (almost) classic many-to-many relationship. With the sight difference that the linktable also contains extra data.

The code looks something like this:

public class User
{
  public virtual string Login { get; set;}  
  public virtual string Name { get; set;}  
  public virtual IList<UserRole> UserRoles { get; set;}  
}

public class Role
{
  public virtual int Id { get; set;}  
  public virtual string Description { get; set;}  
  public virtual IList<UserRole> UserRoles { get; set;}  
}

public class UserRole
{
  public virtual User User { get; set;}  
  public virtual Role Role { get; set;}  
  public virtual bool Active { get; set;}  
}

public class UserMap : ClassMap<User>
{
  public UserMap()
  {
    Table("Users");
    Id(u => u.Login).Column("USER_LOGIN").GeneratedBy.Assigned();
    Map(u => u.Name).Column("USER_NAME");

    HasMany(u => u.UserRoles).KeyColumn("USER_LOGIN");
  }
}

public class RoleMap : ClassMap<Role>
{
  public RoleMap()
  {
    Table("Roles");
    Id(r => r.Id).Column("ROLE_ID").GeneratedBy.Assigned();
    Map(r => r.Description).Column("ROLE_DESCR");

    HasMany(r => r.UserRoles).KeyColumn("ROLE_ID");
  }
}


public class UserRoleMap : ClassMap<UserRole>
{
  public UserRoleMap()
  {
    Table("UserRoles");

    CompositeId()
      .KeyReference(x => x.User, "USER_LOGIN")
      .KeyReference(x => x.Role, "ROLE_ID");

    Map(x => x.Active).Column("ROLE_IS_ACTIVE");
  }
}

So a User can have multiple Roles and a Role has multiple Users. Like I said, classic many-to-many. But, the UserRoles table also contains a field "active", which my application needs in order to operate correctly.

Everything works correctly, but IMHO nhibernate generates way too many queries. When I select a user and access its roles, these are the queries that show up in Nhibernate Profiler:

SELECT user0_.USER_LOGIN       as USER_LOGIN0_0_,
  user0_.USER_NAME          as USER_NAME_0_
FROM Users user0_
WHERE user0_.USER_ID = 'testuser'
-- Fetch the user

SELECT userrole0_.USER_LOGIN as USER_LOGIN1_,
  userrole0_.ROLE_ID         as ROLE_ID1_,
  userrole0_.ROLE_ID         as ROLE_ID1_0_,
  userrole0_.USER_LOGIN      as USER_LOGIN1_0_,
  userrole0_.ROLE_IS_ACTIVE  as ROLE_IS_ACTIVE1_0_
FROM UserRoles userrole0_
WHERE userrole0_.USER_LOGIN = 'testuser'
-- Fetch the roles for that user (why are some fields selected twice?)
-- returns three rows: roleids: 1, 2 and 3

SELECT userrole0_.ROLE_ID    as ROLE_ID1_0_,
  userrole0_.USER_LOGIN      as USER_LOGIN1_0_,
  userrole0_.ROLE_IS_ACTIVE  as ROLE_IS_ACTIVE1_0_
FROM UserRoles userrole0_
WHERE userrole0_.USER_LOGIN = 'testuser'
      and userrole0_.ROLE_ID = 1

SELECT userrole0_.ROLE_ID    as ROLE_ID1_0_,
  userrole0_.USER_LOGIN      as USER_LOGIN1_0_,
  userrole0_.ROLE_IS_ACTIVE  as ROLE_IS_ACTIVE1_0_
FROM UserRoles userrole0_
WHERE userrole0_.USER_LOGIN = 'testuser'
      and userrole0_.ROLE_ID = 2

SELECT userrole0_.ROLE_ID    as ROLE_ID1_0_,
  userrole0_.USER_LOGIN      as USER_LOGIN1_0_,
  userrole0_.ROLE_IS_ACTIVE  as ROLE_IS_ACTIVE1_0_
FROM UserRoles userrole0_
WHERE userrole0_.USER_LOGIN = 'testuser'
      and userrole0_.ROLE_ID = 3
-- Fetch all rows again separately but with full key?!?!!?

So, Nhibernate starts with fetching my User: OK Next, it fetches the roles for that user: OK But then, each row returned by that second query is retrieved from the DB again! I don't know why that happens, since the data returned from the second query actually contains enough data for NHibernate to populate my entire UserRole object.

Is there anyone who can:

1. explain to me why that happens
2. help me figure out how to prevent this. i.e. I'd like to just tell NHibernate that the additional queries on the UserRoles table are not necessary.

Thanks a lot! Regards, ldx

Answer 1

You can change the fetching behaviour in NHibernate for lazy loaded collections. You can do that in your mapping by specifying the fetch attribute:

<many-to-one name="UserRoles" column="ROLE_ID" class="Roles" fetch="select" lazy="false" not-null="true" cascade="save-update" />

I believe you can do this with Fluent as well, but I do not know the method by heart.

You can also override the fetching behaviour that you've specified in your mappping, by setting it explicitly in your ICriteria

criteria.SetFetchMode ("UserRoles", FetchMode.Join);

Answer 2

Not a direct answer to your question...

It may be easier to map it as component from one side and an inverse many-to-many from the other:

user:

<bag name="UserRoles" table="UserRoles">
  <key name="USER_LOGIN"/>
  <composite-element>
    <many-to-one name="Role" column="ROLE_ID"/>
    <property name="Active" />
  </composite-element>
</bag>

Role:

<bag name="Users" inverse="true" table="UserRoles">
  <key name="ROLE_ID"/>
  <many-to-many class="User" column="USER_LOGIN"/>
</bag>

• You don't need the composite id
• You don't need to map the UserRoles separately
• You can navigate in both directions (but you see the Active flag only from User.UserRoles)

Answer 3

1. it appens probably because you haven't specify in your mapping class to not use lazy load for your relationship.

2. as the point 1 you can specify to not use lazy load in your mapping class like

   public MyClassMap()

   {

   Table("...");

   Id(...);

   Map(...);

   HasMany(x => x....).KeyColumn("...").Not.LazyLoad();

   }

In your query, if you are using QueryOver Statement, you can also use the .Future(); instead of .List();

I hole it's helpful.