Setting fetchmode in NHibernate is not eliminating SELECT N+1 issue

Question

Hello, I'm struggling with something fairly basic. I have a one-to-many relationship and I'm setting the fetchmode to inner join in my Criteria query. I see the resulting SQL includes the join, but it also lazily fetches the child entities. What am I doing wrong?

Mappings (Industry has many Manufacturers):

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();
      }
}

Query:

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

Resulting SQL from NHProf (I would expect statement #1 to be the only statement):

-- 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

I have reproduced your scenario with a few minor changes and the result is as expected. Check the following:

Domain classes:

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; }
}

Mapping:

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();
    }
}

Query:

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

UPDATE:

Query ordering by Manufacturers.Name:

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