NHibernate QueryOver 与 Fetch 产生多个 sql 查询和数据库命中 [英] NHibernate QueryOver with Fetch resulting multiple sql queries and db hits

问题描述

我正在尝试选择一个实体并获取相关列表:

 Session.QueryOver().Fetch(x => x.UsersInRole).Eager.列表();

这会导致大量数据库命中.第一个是这样的:

<块引用>

 SELECT ... FROM UserRoles左外连接 UsersInRoles on ...

还有数百个单独的查询，看起来像:

<块引用>

 SELECT ... FROM UsersInRoles左外连接 UserRoles on ...WHERE UserRoles.UserId=?

映射如下:

公共类 UserRoleMap : ClassMap;{公共用户角色映射(){Id(x => x.Id);地图(x => x.RoleName)；HasManyToMany(x => x.UsersInRole).逆().LazyLoad().Table("UsersInRoles");}}

解决方案

我想说，这种行为是我们应该预料到的.让我们有一个场景，其中我们在系统中有 2 个用户和 2 个角色

User1 - Role1//只有 Role1User2 - Role1//现在我们看到 Role2 比 User1 多用户 2 - 角色 2

假设第一个查询将仅检索 User1 及其多对多关系 Role1.目前我们在ISession中只有User1，所以Role1的Users集合是incomplete(我们目前不能重用加载到 ISession 中的对象).但是人们怎么知道我们在哪里呢?为 Role1 加载的所有数据是否在会话中?

必须发出新查询，加载 Role1 的数据.通过这种方式，我们最终可以处理这些查询...

我认为最好的解决方案(我在几乎所有场景中都使用它)是 batch-size 设置:19.1.5.使用批量获取

HasManyToMany(x => x.UsersInRole)....BatchSize(25)

用 .BatchSize(25) 标记所有集合映射，甚至对 Class 映射也这样做.这将导致超过 1 个 SQL 脚本，但最后 不 超过 1 + (2-4)，具体取决于批处理大小和页面大小.

I'm trying to select an entity and fetch a related list:

    Session.QueryOver<UserRole>()
           .Fetch(x => x.UsersInRole).Eager
           .List();

Which results in a lot of database hits. The first one is something like:

 SELECT ... FROM UserRoles
 left outer join UsersInRoles on ...

And hundreds more seperate queries which looks something like:

 SELECT ... FROM UsersInRoles
 left outer join UserRoles on ...
 WHERE UserRoles.UserId=?

The mapping is as following:

public class UserRoleMap : ClassMap<UserRole>
{
    public UserRoleMap()
    {
        Id(x => x.Id);
        Map(x => x.RoleName);
        HasManyToMany(x => x.UsersInRole)
        .Inverse()
        .LazyLoad()
        .Table("UsersInRoles");
    }
}

解决方案

I would say, that this behaviour is what we should expect. Let's have a scenario, in which we have in the system 2 users and 2 roles

User1 - Role1 // has only Role1
User2 - Role1 // now we see that Role2 has more then User1
User2 - Role2

Let's say, that the first query, will retrieve only User1 and its many-to-many relation Role1. What we have in the ISession at the moment is only User1, so the set of Users for Role1 is incomplete (we cannot reuse objects loaded into ISession at the moment). But how should one know where we are? That all data loaded for Role1 is or is not in the session?

New query, loading the data for Role1 must be issued. And this way, we can at the end have dosens of these queries...

What I see as the best solution (I am using it in almost all scenarios) is the batch-size setting: 19.1.5. Using batch fetching

HasManyToMany(x => x.UsersInRole)
  ...
  .BatchSize(25)

Mark all your collection maps with .BatchSize(25) and do that even for the Class map as well. That will cause more then 1 SQL Script, but at the end not more then 1 + (2-4) dependent on the batch-size and page size.

这篇关于NHibernate QueryOver 与 Fetch 产生多个 sql 查询和数据库命中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！