Query Over performs multiple queries

[Ben]

My model looks like this...

User - UserRoleAssignment - Role

And is mapped using the following Fluent NHibernate mappings...

public UserMap()
{
    Id(x => x.Id).GeneratedBy.HiLo("10000");
    Map(x => x.UserName).Unique().Not.Nullable().Length(255).Default(string.Empty);
 
    HasMany(x => x.RoleGrants).KeyColumn("User_id").Cascade.None().Access.CamelCaseField();
}

public UserRoleAssociationMap()
{
    Id(x => x.Id).GeneratedBy.HiLo("10000");
 
    Map(x => x.ValidFrom);
    Map(x => x.ValidTo);
 
    References(x => x.Role).Column("Role_id").Not.Nullable().Fetch.Join().UniqueKey("UserRoleKey").ForeignKey("fk_userrolegrant_role");
    References(x => x.User).Column("User_id").Not.Nullable().Fetch.Join().UniqueKey("UserRoleKey").ForeignKey("fk_userrolegrant_user");
}

public RoleMap()
{
    Id(x => x.Id).GeneratedBy.HiLo("10000");
    Map(x => x.Name).Unique().Length(50);
}

I've got the following query....

var directUsers =
               session.QueryOver(() => userRoleAssociation).
               JoinAlias(() => userRoleAssociation.User, () => user).
               Where(() => userRoleAssociation.Role == role).
               And(() => (userRoleAssociation.ValidFrom == null || userRoleAssociation.ValidFrom <= currentDateTime) && (userRoleAssociation.ValidTo == null || userRoleAssociation.ValidTo >= currentDateTime)).
               And(() => (user.ValidFrom == null || user.ValidFrom <= currentDateTime) && (user.ValidTo == null || user.ValidTo >= currentDateTime)).
               Select(x => x.User).List<User>();

This basically results in multiple queries. It first selects all the user IDs which match the critera in the QueryOver query and then performs a single select for eachID in the list.

It also then traverses all the users relationships and every other relationship in the model until it's built a massive tree of nearly every single entity related to the users.

What is the problem with my QueryOver approach that's making it perform 2 seperate queries and why is NHibernate traversing the whole model?

Help Please
Thanks
Ben

[Richard Brown (gmail)]

Hi Ben,

 

I think the underlying ICriteria projection selects only the ID’s of the users, not the whole object.  This means you’ll then get a select n+1 when you loop through the results.  However, I think the following would work ok:

 

var directUsers =

    session.QueryOver(() => userRoleAssociation)

        ...

        .Fetch(() => userRoleAssociation.User).Eager // might not need this for the example mapping

        .List()

        .Select(ura => ura.User); // this is linq-to-objects

 

Note, this example brings back the UserRoleAssociation objects with the User object eagerly populated.  The last line is a LINQ extension method (in-memory) to extract the list of Users.

 

In addition, your mapping suggests that the eager-fetch may also not be required (since you specify a fetch in there), so the query simply becomes:

 

var directUsers =

    session.QueryOver(() => userRoleAssociation)

        ...

        .List()

        .Select(ura => ura.User); // this is linq-to-objects

 

Let me know if that helps.

 

Richard

From: Ben

Sent: Thursday, April 07, 2011 1:58 PM

To: nhu...@googlegroups.com

Subject: [nhusers] Query Over performs multiple queries

 

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.