NHibernate N + 1提取问题 [英] NHibernate N+1 fetch problem

问题描述

我有一个看起来像这样的实体和流畅的映射.

I have a entity and fluent mapping that look like this.

public class Client : EntityWithTypedId<long>
{               
    [Length(Max=50)]
    public virtual string GivenName { get; set; }

    public virtual IList<Address> Addresses { get; set; }
}

public class ClientMap : ClassMap<Client> 
{       
    public ClientMap() 
    {
        Schema("dbo");
        Table("Client");            
        Id(x => x.Id, "ClientId").GeneratedBy.Identity();           
        Map(x => x.GivenName, "GivenName");             
        HasManyToMany(x => x.Addresses)
            .FetchType.Join()
            .Cascade.AllDeleteOrphan()
            .Table("ClientAddress")
            .ParentKeyColumn("ClientId")
            .ChildKeyColumn("AddressId")
            .AsBag();
    }           
}

然后我像这样执行ICriteria查询

I then execute an ICriteria query like this

return Session.CreateCriteria<Client>()
    .CreateAlias("Organisation", "o").SetFetchMode("o", FetchMode.Join)
    .CreateAlias("Addresses", "a").SetFetchMode("a", FetchMode.Join)
    .Add(expression)
    .AddOrder(Order.Asc("Surname")).AddOrder(Order.Asc("GivenName"))
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .SetMaxResults(pageSize)
    .SetFirstResult(Pagination.FirstResult(pageIndex, pageSize))
    .Future<Client>();

使用NHProf，我可以看到它执行了这样的查询，该查询应返回所有客户端详细信息和地址

Using NHProf I can see it executes a query like this which should return all client details and addresses

SELECT   top 20 this_.ClientId       as ClientId5_2_,
                this_.GivenName      as GivenName5_2_,
                addresses4_.ClientId as ClientId,
                a2_.AddressId        as AddressId,
                a2_.AddressId        as AddressId0_0_,
                a2_.Street           as Street0_0_,
                a2_.Suburb           as Suburb0_0_,
                a2_.State            as State0_0_,
                a2_.Postcode         as Postcode0_0_,
                a2_.Country          as Country0_0_,
                a2_.AddressTypeId    as AddressT7_0_0_,
                a2_.OrganisationId   as Organisa8_0_0_,
                o1_.OrganisationId   as Organisa1_11_1_,
                o1_.Description      as Descript2_11_1_,
                o1_.Code             as Code11_1_,
                o1_.TimeZone         as TimeZone11_1_
FROM     dbo.Client this_
         inner join ClientAddress addresses4_
           on this_.ClientId = addresses4_.ClientId
         inner join dbo.Address a2_
           on addresses4_.AddressId = a2_.AddressId
         inner join dbo.Organisation o1_
           on this_.OrganisationId = o1_.OrganisationId
WHERE    (o1_.Code = 'Demo' /* @p4 */
          and (this_.Surname like '%' /* @p5 */
                or (this_.HomePhone = '%' /* @p6 */
                     or this_.MobilePhone = '%' /* @p7 */)))
ORDER BY this_.Surname asc,
         this_.GivenName asc

将按预期返回所有记录

但是如果我再写类似的代码

However if i then write code like

foreach(var client in clients)
{
   if (client.Addresses.Any())
   { 
       Console.WriteLn(client.Addresses.First().Street);
   }
}

我仍然遇到N + 1的问题，它在每个地址上进行选择.如何避免这种情况?

I still get an N+1 issue where it does a select on each address. How can I avoid this?

推荐答案

我认为您误解了这里发生的事情...将不同的结果转换器与分页结合使用几乎总是不正确的.考虑一下，鉴于上面的查询，您只会得到交叉乘积的前20行.我猜想，由于这个原因，您列表中的几个客户没有填充他们的收藏夹，这导致您的N + 1问题.

I think you're misunderstanding what's going on here...it's almost always incorrect to use the distinct result transformer in conjunction with paging. Think about it, you're only getting the first 20 rows of a cross-product given that query above. I'm guessing that several of your clients at the end of the list aren't having their collections populated because of this, leading to your N+1 issue.

如果需要执行分页操作，请考虑在集合映射上使用batch-size提示，以帮助最大程度地减少N + 1问题.

If you need to do the paging operation, consider using the batch-size hint on your collection mapping to help minimize the N+1 issue.

注意:如果您的典型用例是一次显示20页，请将batch-size设置为此值.

Note: if your typical use case is to show pages of 20 at a time, set the batch-size to this value.

这篇关于NHibernate N + 1提取问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！