在 NHibernate 中设置 fetchmode 并不能消除 SELECT N+1 问题

Question

我正在努力解决一些相当基本的问题。我有一对多关系，并且在 Criteria 查询中将 fetchmode 设置为内部联接。我看到生成的 SQL 包含联接，但它也延迟获取子实体。我做错了什么？

映射（行业有许多制造商）：

public class IndustryMap : ClassMap<Industry>
{
    public IndustryMap()
    {
        Id(industry => industry.ID);
        Map(industry => industry.Name);

        HasMany(x => x.Manufacturers)
            .KeyColumn("IndustryID")
            .AsSet()
            .Access.PascalCaseField(Prefix.Underscore)
            .LazyLoad();
    }
}

public class ManufacturerMap: ClassMap<Manufacturer>
{
      public ManufacturerMap()
      {
          Id(manufacturer=> manufacturer.ID);
          Map(manufacturer => manufacturer.Name);

          References(manufacturer => manufacturer.Industry, "IndustryID")
              .LazyLoad();
      }
}

查询：

var industries = this.Session.CreateCriteria<Industry>()
                                .CreateAlias("Manufacturers", "manu", JoinType.InnerJoin)
                                .AddOrder(new Order("Name", true))
                                .SetResultTransformer(new DistinctRootEntityResultTransformer())
                                .List<Industry>();

NHProf 生成的 SQL（我希望语句 #1 是唯一的语句）：

-- statement #1
SELECT   this_.Id           as Id5_1_,
         this_.LastUpdated  as LastUpda2_5_1_,
         this_.Name         as Name5_1_,
         manu1_.Id          as Id6_0_,
         manu1_.LastUpdated as LastUpda2_6_0_,
         manu1_.Name        as Name6_0_,
         manu1_.IndustryID  as IndustryID6_0_
FROM     Dealer.[Industry] this_
         inner join Dealer.[Manufacturer] manu1_
           on this_.Id = manu1_.IndustryID
ORDER BY this_.Name asc

-- statement #2
SELECT manufactur0_.IndustryID  as IndustryID1_,
       manufactur0_.Id          as Id1_,
       manufactur0_.Id          as Id6_0_,
       manufactur0_.LastUpdated as LastUpda2_6_0_,
       manufactur0_.Name        as Name6_0_,
       manufactur0_.IndustryID  as IndustryID6_0_
FROM   Dealer.[Manufacturer] manufactur0_
WHERE  manufactur0_.IndustryID = '529fde0e-dccf-456a-ab69-4a4b662aa0d2' /* @p0 */

-- statement #3
SELECT manufactur0_.IndustryID  as IndustryID1_,
       manufactur0_.Id          as Id1_,
       manufactur0_.Id          as Id6_0_,
       manufactur0_.LastUpdated as LastUpda2_6_0_,
       manufactur0_.Name        as Name6_0_,
       manufactur0_.IndustryID  as IndustryID6_0_
FROM   Dealer.[Manufacturer] manufactur0_
WHERE  manufactur0_.IndustryID = '529fde0e-dccf-456a-ab69-4a4b662aa0d3' /* @p0 */

-- statement #4
SELECT manufactur0_.IndustryID  as IndustryID1_,
       manufactur0_.Id          as Id1_,
       manufactur0_.Id          as Id6_0_,
       manufactur0_.LastUpdated as LastUpda2_6_0_,
       manufactur0_.Name        as Name6_0_,
       manufactur0_.IndustryID  as IndustryID6_0_
FROM   Dealer.[Manufacturer] manufactur0_
WHERE  manufactur0_.IndustryID = '529fde0e-dccf-456a-ab69-4a4b662aa053' /* @p0 */

-- statement #5
SELECT manufactur0_.IndustryID  as IndustryID1_,
       manufactur0_.Id          as Id1_,
       manufactur0_.Id          as Id6_0_,
       manufactur0_.LastUpdated as LastUpda2_6_0_,
       manufactur0_.Name        as Name6_0_,
       manufactur0_.IndustryID  as IndustryID6_0_
FROM   Dealer.[Manufacturer] manufactur0_
WHERE  manufactur0_.IndustryID = '529fde0e-dccf-456a-ab69-4a4b662aa245' /* @p0 */

Answer 1

我已经重现了您的场景，并进行了一些细微的更改，结果符合预期。检查以下内容：

域类：

public class Industry
{
    public virtual int ID { get; set; }
    public virtual string Name { get; set; }
    public virtual ICollection<Manufacturer> Manufacturers { get; set; }
}

public class Manufacturer
{
    public virtual int ID { get; set; }
    public virtual string Name { get; set; }
    public virtual Industry Industry { get; set; }
}

映射：

public class IndustryMap : ClassMap<Industry>
{
    public IndustryMap()
    {
        Id(industry => industry.ID);
        Map(industry => industry.Name);

        HasMany(x => x.Manufacturers)
            .KeyColumn("IndustryID")
            .AsSet()
            .Inverse()
            //.Access.PascalCaseField(Prefix.Underscore)
            .LazyLoad();
    }
}

public class ManufacturerMap : ClassMap<Manufacturer>
{
    public ManufacturerMap()
    {
        Id(manufacturer => manufacturer.ID);
        Map(manufacturer => manufacturer.Name);

        References(manufacturer => manufacturer.Industry, "IndustryID")
            .LazyLoad();
    }
}

查询：

var industries = session.CreateCriteria<Industry>()
                .SetFetchMode("Manufacturers", FetchMode.Eager) //this is it
                            .List<Industry>();

更新：

按制造商名称查询排序：

var industries = session.CreateCriteria<Industry>()                          
                    .SetFetchMode("Manufacturers", FetchMode.Eager) //this is it
                      .CreateAlias("Manufacturers","manu")
                      .AddOrder(Order.Asc("manu.Name")
                                .List<Industry>();