Querying via NHibernate without an N+1 - sample included

Question

I have an N+1 problem and I'm not sure how to solve it.

A fully-reproducible sample may be found at the bottom of this question. So if you are willing, please create the database, set up the NUnit test and all the accompanying classes, and try to eliminate the N+1 locally. This is the anonymized version of a real problem I encountered. For all you know, this code is crucial in helping launch the next space shuttle to the moon. I won't deny it if asked.

To summarize the problem: I am trying to query a table structure that is drawn below. The only weird thing to note about this table structure is that questions have choices, which then have sub-questions, which then have sub-choices. You can assume only 2 levels of question->choice->question->choice.

SiteSurveyQuestion
|
+---Site
|
+---Survey
|
+---Question
    |
    +---Choice
    +---Choice
    +---Choice
        |
        +---Question
        +---Question
        +---Question
            |
            +---Choice
            +---Choice
            +---Choice

I've tried everything I know to try.

In the mappings, I have tried a bunch of referencing fields as .Not.LazyLoad() to no real success.

I have also tried modifying the query by adding many combinations of .Fetch() and .FetchMany() and .ThenFetchMany() and even tried running multiple .ToFuture() queries. These do make real changes to the SQL query, but not the final result I'm looking for.

The query as it is boiled down, is "get me a list of all questions for this survey on this site, including all sub-questions". Here is the query:

using (var session = sessionFactory.OpenSession())
{
    var questionsForSurvey = session.Query<SiteSurveyQuestion>()
        .Where(x => x.Site.Id == 1 && x.Survey.Id == 1)
        .ToArray();
}

So to finally ask the question: how can I fix this N+1 problem? I would be happy with any of the following

(Preferred) A fix in the class mappings to eager load everything
(2nd choice) Sprinking the Query with fetch's or query hints using the LINQ provider
(3rd choice) mix of the above
(4th choice) being told it's impossible and a limitation of NHibernate
(5th choice) Solution in HQL

I do not want an HQL solution because I won't learn anything about what I'm doing wrong with my mapping and/or querying - I feel like I'm missing something fundamental, and I don't even know where to look.

Sample instructions:

Copy and paste the SQL setup script into your local SQL Server instance, run it.
Create a test project (or if you're lazy, use your existing test project) and add the nuget packages for NHibernate and Fluent NHibernate to the project.
Run the test. You should see:
1. Generated SQL run by NHibernate
2. Output from the test.
Fix mappings/query until N+1 is gone - you will know when you see first a bunch of SQL scripts outputted, then:

```

Site: Site1 Survey: SurveyAboutCats Q: Own A Cat?
    o Yes
        Q: How many cats did you feed yesterday?
            o 1
            o 2-5
            o 6-10
            o 11-20
            o 20+
            o 100+
        Q: How much do you spend on cats annually?
            o 0-100
            o 100-500
            o 500-2000
            o 2000+
    o No
        Q: No cats? What is wrong with you?
            o I am sorry
Site: Site1 Survey: SurveyAboutCats Q: Own A Dog?
    o Yes
    o No

Full sample:

/*
Nuget packages:

  <package id="FluentNHibernate" version="1.3.0.733" targetFramework="net40" />
  <package id="NHibernate" version="3.3.3.4001" targetFramework="net45" />
  <package id="NUnit" version="2.6.2" targetFramework="net40" />

*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Conventions.Helpers;
using FluentNHibernate.Mapping;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Linq;
using NUnit.Framework;

namespace StackOverflow.CryForHelp
{
    [TestFixture]
    public class NHibernateMappingTests
    {
        [Test]
        public void ShouldMapEntitiesWithoutNPlusOneIssue()
        {
            //Arrange
            var connectionString = "Data Source=(local);Initial Catalog=NinetyNineProblemsAndAnNPlusOne;Integrated Security=SSPI;";

            Configuration applicationConfiguration = new Configuration();
            applicationConfiguration.SetProperty("connection.provider", "NHibernate.Connection.DriverConnectionProvider");
            applicationConfiguration.SetProperty("dialect", "NHibernate.Dialect.MsSql2008Dialect");
            applicationConfiguration.SetProperty("connection.driver_class", "NHibernate.Driver.SqlClientDriver");
            applicationConfiguration.SetProperty("default_schema", "dbo");
            applicationConfiguration.SetProperty("format_sql", "format_sql");
            applicationConfiguration.SetProperty("show_sql", "true");
            applicationConfiguration.SetProperty("generate_statistics", "true");
            applicationConfiguration.Configure();
            Configuration fluentConfiguration = null;

            ISessionFactory sessionFactory = Fluently.Configure(applicationConfiguration)
                .Mappings(m =>
                {
                    m.FluentMappings.Conventions.Setup(x => x.Add(AutoImport.Never()));
                    m.FluentMappings.AddFromAssembly(Assembly.GetAssembly(GetType()));
                })

                .ExposeConfiguration(c => fluentConfiguration = c)
                .Database(MsSqlConfiguration.MsSql2008.ConnectionString(connectionString))
                .BuildSessionFactory();

            var mappings = fluentConfiguration.ClassMappings;

            //Act + Assert that we please don't create N+1 queries
            using (var session = sessionFactory.OpenSession())
            {
                var questionsForSurvey = session.Query<SiteSurveyQuestion>()
                    .Where(x => x.Site.Id == 1 && x.Survey.Id == 1)
                    .ToArray();

                foreach (var question in questionsForSurvey)
                {
                    Console.WriteLine("Site: {0} Survey: {1} Q: {2}", question.Site.Name, question.Survey.Name, question.Question.InternalName);

                    foreach (var choice in question.Question.Choices)
                    {
                        Console.WriteLine("\t> " + choice.InternalName);

                        foreach (var subQuestion in choice.Questions)
                        {
                            Console.WriteLine("\t\tQ: " + subQuestion.InternalName);

                            foreach (var subChoice in subQuestion.Choices)
                                Console.WriteLine("\t\t\t> " + subChoice.InternalName);
                        }
                    }
                }
            }
        }
    }


    public class Site
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }

    public class Survey
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }

    public class SiteSurvey
    {
        public virtual Site Site { get; set; }
        public virtual Survey Survey { get; set; }
        public virtual string Status { get; set; }
        public virtual string Name { get; set; }

        public virtual bool Equals(SiteSurvey other)
        {
            if (ReferenceEquals(null, other)) return false;
            if (ReferenceEquals(this, other)) return true;
            return Site.Id == other.Site.Id && Survey.Id == other.Survey.Id;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != this.GetType()) return false;
            return Equals((SiteSurvey) obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return (Survey.Id * 397) ^ Site.Id;
            }
        }
    }

    public class SiteSurveyQuestion
    {
        public virtual Site Site { get; set; }
        public virtual Survey Survey { get; set; }
        public virtual Question Question { get; set; }
        public virtual bool IsActive { get; set; }

        public virtual bool Equals(SiteSurveyQuestion other)
        {
            if (ReferenceEquals(null, other)) return false;
            if (ReferenceEquals(this, other)) return true;
            return Site.Id == other.Site.Id && Survey.Id == other.Survey.Id && Question.Id == other.Question.Id;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != this.GetType()) return false;
            return Equals((SiteSurveyQuestion) obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return Question.Id ^ (((Survey.Id * 397) ^ Site.Id) * 397);
            }
        }
    }

    public class Question
    {
        public virtual int Id { get; set; }
        public virtual string InternalName { get; set; }
        public virtual bool IsActive { get; set; }
        public virtual IEnumerable<Choice> Choices { get; set; }
    }

    public class Choice
    {
        public virtual int Id { get; set; }
        public virtual string InternalName { get; set; }
        public virtual bool IsActive { get; set; }
        public virtual IEnumerable<Question> Questions { get; set; }
    }

    public class SurveyMap : ClassMap<Survey>
    {
        public SurveyMap()
        {
            Table("Surveys");
            Id(x => x.Id, "SurveyId").GeneratedBy.Identity().UnsavedValue(0);
            Map(x => x.Name).Not.Nullable();
        }
    }

    public class SiteMap : ClassMap<Site>
    {
        public SiteMap()
        {
            Table("Sites");
            Id(x => x.Id, "SiteId").GeneratedBy.Identity().UnsavedValue(0);
            Map(x => x.Name, "Name").Not.Nullable();
        }
    }

    public class SiteSurveyMap : ClassMap<SiteSurvey>
    {
        public SiteSurveyMap()
        {
            Table("SiteSurveys");
            CompositeId()
                .KeyReference(x => x.Site, "SiteId")
                .KeyReference(x => x.Survey, "SurveyId");

            Map(x => x.Status).Not.Nullable();
            Map(x => x.Name).Not.Nullable();
        }
    }

    public class SiteSurveyQuestionMap : ClassMap<SiteSurveyQuestion>
    {
        public SiteSurveyQuestionMap()
        {
            Table("SiteSurveyQuestions");
            CompositeId()
                .KeyReference(x => x.Site, "SiteId")
                .KeyReference(x => x.Survey, "SurveyId")
                .KeyReference(x => x.Question, "QuestionId");

            Map(x => x.IsActive, "ActiveFlag").Not.Nullable();
        }
    }

    public class QuestionMap : ClassMap<Question>
    {
        public QuestionMap()
        {
            Table("Questions");
            Id(x => x.Id, "QuestionId").GeneratedBy.Identity().UnsavedValue(0);
            Map(x => x.InternalName);
            Map(x => x.IsActive, "ActiveFlag");

            HasMany(x => x.Choices).KeyColumn("QuestionId").AsBag().Cascade.AllDeleteOrphan().Inverse().Not.LazyLoad();

        }
    }

    public class ChoiceMap : ClassMap<Choice>
    {
        public ChoiceMap()
        {
            Table("Choices");
            Id(x => x.Id, "ChoiceId").GeneratedBy.Identity().UnsavedValue(0);
            Map(x => x.InternalName);
            Map(x => x.IsActive, "ActiveFlag");
            HasMany(x => x.Questions)
                .KeyColumn("ChoiceId")
                .AsBag()
                .Cascade
                .AllDeleteOrphan()
                .Inverse();
        }
    }
}

/*









use [master]
GO

CREATE DATABASE [NinetyNineProblemsAndAnNPlusOne]
GO

USE [NinetyNineProblemsAndAnNPlusOne]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Sites](
    [SiteId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NOT NULL,
 CONSTRAINT [XPKSites] PRIMARY KEY CLUSTERED 
(
    [SiteId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [XAK1Sites] UNIQUE NONCLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Surveys](
    [SurveyId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](500) NOT NULL,
    [Status] [varchar](12) NOT NULL,
    [SurveyTypeId] [int] NOT NULL,
 CONSTRAINT [XPKSurveys] PRIMARY KEY CLUSTERED 
(
    [SurveyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [XAK1Surveys] UNIQUE NONCLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[SiteSurveys](
    [SiteId] [int] NOT NULL,
    [SurveyId] [int] NOT NULL,
    [Name] [varchar](500) NOT NULL,
    [Status] [varchar](12) NOT NULL,
 CONSTRAINT [XPKSiteSurveys] PRIMARY KEY CLUSTERED 
(
    [SiteId] ASC,
    [SurveyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [XAK1SiteSurveys] UNIQUE NONCLUSTERED 
(
    [SiteId] ASC,
    [SurveyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [XAK2SiteSurveys] UNIQUE NONCLUSTERED 
(
    [SiteId] ASC,
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[SiteSurveyQuestions](
    [SiteId] [int] NOT NULL,
    [SurveyId] [int] NOT NULL,
    [QuestionId] [int] NOT NULL,
    [SurveyQuestionTypeId] [int] NULL,
    [ActiveFlag] [bit] NOT NULL,
    [IsRequired] [bit] NOT NULL,
 CONSTRAINT [XPKSurveyQuestions] PRIMARY KEY CLUSTERED 
(
    [SurveyId] ASC,
    [QuestionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Questions](
    [QuestionId] [int] IDENTITY(1,1) NOT NULL,
    [InternalName] [varchar](100) NOT NULL,
    [ChoiceId] [int] NULL,
    [ActiveFlag] [bit] NOT NULL,
 CONSTRAINT [XPKQuestions] PRIMARY KEY CLUSTERED 
(
    [QuestionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [XAK1QuestionsInternalName] UNIQUE NONCLUSTERED 
(
    [InternalName] ASC,
    [ChoiceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Choices](
    [ChoiceId] [int] IDENTITY(1,1) NOT NULL,
    [QuestionId] [int] NOT NULL,
    [InternalName] [varchar](100) NOT NULL,
    [ActiveFlag] [bit] NOT NULL,
 CONSTRAINT [XPKChoices] PRIMARY KEY CLUSTERED 
(
    [ChoiceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [XAKChoiceIdQuestionId] UNIQUE NONCLUSTERED 
(
    [ChoiceId] ASC,
    [QuestionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [XAKChoiceInternalName] UNIQUE NONCLUSTERED 
(
    [QuestionId] ASC,
    [InternalName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Surveys]  WITH CHECK ADD  CONSTRAINT [VRSurveyStatuses_Surveys] CHECK  (([Status]='Live' OR [Status]='NotLive' OR [Status]='Discontinued'))
GO

ALTER TABLE [dbo].[Surveys] CHECK CONSTRAINT [VRSurveyStatuses_Surveys]
GO

ALTER TABLE [dbo].[SiteSurveys]  WITH CHECK ADD  CONSTRAINT [R289] FOREIGN KEY([SurveyId])
REFERENCES [dbo].[Surveys] ([SurveyId])
GO

ALTER TABLE [dbo].[SiteSurveys] CHECK CONSTRAINT [R289]
GO

ALTER TABLE [dbo].[SiteSurveys]  WITH CHECK ADD  CONSTRAINT [R303] FOREIGN KEY([SiteId])
REFERENCES [dbo].[Sites] ([SiteId])
GO

ALTER TABLE [dbo].[SiteSurveys] CHECK CONSTRAINT [R303]
GO

ALTER TABLE [dbo].[SiteSurveys]  WITH CHECK ADD  CONSTRAINT [VRSurveyStatuses_SiteSurveys] CHECK  (([Status]='Live' OR [Status]='NotLive' OR [Status]='Discontinued'))
GO

ALTER TABLE [dbo].[SiteSurveys] CHECK CONSTRAINT [VRSurveyStatuses_SiteSurveys]
GO


ALTER TABLE [dbo].[SiteSurveyQuestions]  WITH CHECK ADD  CONSTRAINT [QuestionsToSurveyQuestions] FOREIGN KEY([QuestionId])
REFERENCES [dbo].[Questions] ([QuestionId])
GO

ALTER TABLE [dbo].[SiteSurveyQuestions] CHECK CONSTRAINT [QuestionsToSurveyQuestions]
GO

ALTER TABLE [dbo].[SiteSurveyQuestions]  WITH CHECK ADD  CONSTRAINT [SurveysToSurveyQuestions] FOREIGN KEY([SurveyId])
REFERENCES [dbo].[Surveys] ([SurveyId])
GO

ALTER TABLE [dbo].[SiteSurveyQuestions] CHECK CONSTRAINT [SurveysToSurveyQuestions]
GO

ALTER TABLE [dbo].[Questions]  WITH CHECK ADD  CONSTRAINT [R409] FOREIGN KEY([ChoiceId])
REFERENCES [dbo].[Choices] ([ChoiceId])
GO

ALTER TABLE [dbo].[Choices]  WITH CHECK ADD  CONSTRAINT [R408] FOREIGN KEY([QuestionId])
REFERENCES [dbo].[Questions] ([QuestionId])
GO

ALTER TABLE [dbo].[Choices] CHECK CONSTRAINT [R408]
GO


SET ANSI_PADDING OFF
GO

GO

SET IDENTITY_INSERT [dbo].[Sites] ON 
INSERT [dbo].[Sites] ([SiteId], [Name]) VALUES (1, N'Site1')
INSERT [dbo].[Sites] ([SiteId], [Name]) VALUES (2, N'Site2')
SET IDENTITY_INSERT [dbo].[Sites] OFF

SET IDENTITY_INSERT [dbo].[Surveys] ON 
INSERT [dbo].[Surveys] ([SurveyId], [Name], [Status], [SurveyTypeId]) VALUES (1, N'SurveyAboutCats', N'Live', 0)
INSERT [dbo].[Surveys] ([SurveyId], [Name], [Status], [SurveyTypeId]) VALUES (2, N'Crime Survey', N'Live', 0)
SET IDENTITY_INSERT [dbo].[Surveys] OFF

SET IDENTITY_INSERT [dbo].[Questions] ON 
INSERT [dbo].[Questions] ([QuestionId], [InternalName], [ChoiceId], [ActiveFlag]) VALUES (1, N'Own A Cat?', NULL, 1)
INSERT [dbo].[Questions] ([QuestionId], [InternalName], [ChoiceId], [ActiveFlag]) VALUES (2, N'Own A Dog?', NULL, 1)
INSERT [dbo].[Questions] ([QuestionId], [InternalName], [ChoiceId], [ActiveFlag]) VALUES (3, N'Witnessed any crimes recently?', NULL, 1)
INSERT [dbo].[Questions] ([QuestionId], [InternalName], [ChoiceId], [ActiveFlag]) VALUES (4, N'Committed any crimes yourself recently?', NULL, 1)
SET IDENTITY_INSERT [dbo].[Questions] OFF

SET IDENTITY_INSERT [dbo].[Choices] ON 
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (1, 1, N'Yes', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (2, 1, N'No', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (3, 2, N'Yes', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (4, 2, N'No', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (5, 3, N'Yes', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (6, 3, N'Yes but I ain''t no snitch', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (7, 4, N'No', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (8, 4, N'I plead the fifth', 1)
SET IDENTITY_INSERT [dbo].[Choices] OFF

SET IDENTITY_INSERT [dbo].[Questions] ON 
INSERT [dbo].[Questions] ([QuestionId], [InternalName], [ChoiceId], [ActiveFlag]) VALUES (6, N'No cats? What is wrong with you?', 2, 1)
INSERT [dbo].[Questions] ([QuestionId], [InternalName], [ChoiceId], [ActiveFlag]) VALUES (7, N'How many cats did you feed yesterday?', 1, 1)
INSERT [dbo].[Questions] ([QuestionId], [InternalName], [ChoiceId], [ActiveFlag]) VALUES (8, N'How much do you spend on cats annually?', 1, 1)
SET IDENTITY_INSERT [dbo].[Questions] OFF

SET IDENTITY_INSERT [dbo].[Choices] ON 
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (9, 6, N'I am sorry', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (10, 7, N'1', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (11, 7, N'2-5', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (12, 7, N'6-10', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (13, 7, N'11-20', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (14, 7, N'20+', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (15, 7, N'100+', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (16, 8, N'0-100', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (17, 8, N'100-500', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (18, 8, N'500-2000', 1)
INSERT [dbo].[Choices] ([ChoiceId], [QuestionId], [InternalName], [ActiveFlag]) VALUES (19, 8, N'2000+', 1)
SET IDENTITY_INSERT [dbo].[Choices] OFF



INSERT [dbo].[SiteSurveys] ([SiteId], [SurveyId], [Name], [Status]) VALUES (1, 1, N'Site #1 Cat Survey', N'Live')
INSERT [dbo].[SiteSurveys] ([SiteId], [SurveyId], [Name], [Status]) VALUES (1, 2, N'Site #1 Crime Survey', N'Live')
INSERT [dbo].[SiteSurveys] ([SiteId], [SurveyId], [Name], [Status]) VALUES (2, 1, N'Site #2 Cat Survey', N'Live')
INSERT [dbo].[SiteSurveys] ([SiteId], [SurveyId], [Name], [Status]) VALUES (2, 2, N'Site #2 Crime Survey', N'Live')


INSERT [dbo].[SiteSurveyQuestions] ([SiteId], [SurveyId], [QuestionId], [SurveyQuestionTypeId], [ActiveFlag], [IsRequired]) VALUES (1, 1, 1, 0, 1, 0)
INSERT [dbo].[SiteSurveyQuestions] ([SiteId], [SurveyId], [QuestionId], [SurveyQuestionTypeId], [ActiveFlag], [IsRequired]) VALUES (1, 1, 2, 0, 1, 0)

GO
USE [master]
GO












-- */

I'll try to run the unit test provided later but really I don't see how this wouldn't result in an n+1 because you aren't loading anything other than SiteSurveyQuestions. It seems like you would also have to load Questions and Choices as well which it seems like you said you already tried. Another test would be to load all Questions and Choices in the whole database to see if this is avoided but this is obviously not a solution if you have many Questions and Choices in your database. If the result set is small though it could work. — Cole W, Feb 16 '15 at 21:47
Thank you sir for the attention to my question. I have definitely tried doing the full combination of .Fetch(x => x.Question).ThenFetch(x => x.Choices).ThenFetch(x => x.Questions).ThenFetch(x => x.Choices) - which creates a cross product-type explosion in the result set. I left my SO question "blank" without any obvious attempt, because I really hope that I just need to add to the mappings, but I'm not sure this can be solved by ONLY fixing mappings — Peter Seale, Feb 16 '15 at 22:00
Ahh I forgot to answer your question. The code in the FULL sample enumerates through every single object in the aggregate and prints it out, thus causing an N+1. — Peter Seale, Feb 16 '15 at 22:13
Well what I would do is create seperate queries for Question and Choice that relate to the `SiteSurveyQuestion` you want. As a very basic test you could try querying all of the `Questions` and `Choices` with a query like: `session.Query().ToList()` and `session.Query().ToList()` — Cole W, Feb 16 '15 at 22:14

score 3 · Accepted Answer · edited May 23 '17 at 11:50

The solution to 1 + N would've been built on top of a special NHibernate optimization feature (let me cite a bit)

19.1.5. Using batch fetching

NHibernate can make efficient use of batch fetching, that is, NHibernate can load several uninitialized proxies if one proxy is accessed (or collections. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can tune batch fetching: on the class and the collection level.

Batch fetching for classes/entities is easier to understand. Imagine you have the following situation at runtime: You have 25 Cat instances loaded in an ISession, each Cat has a reference to its Owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call cat.Owner on each, NHibernate will by default execute 25 SELECT statements, to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

NHibernate will now execute only three queries, the pattern is 10, 10, 5.

You may also enable batch fetching of collections. For example, if each Person has a lazy collection of Cats, and 10 persons are currently loaded in the ISesssion, iterating through all persons will generate 10 SELECTs, one for every call to person.Cats. If you enable batch fetching for the Cats collection in the mapping of Person, NHibernate can pre-fetch collections:

<class name="Person">
    <set name="Cats" batch-size="3">
        ...
    </set>
</class>

With a batch-size of 3, NHibernate will load 3, 3, 3, 1 collections in four SELECTs. Again, the value of the attribute depends on the expected number of uninitialized collections in a particular Session.

So, that is the DOC. The great on this solution is, that we will have simple queries, and optimization inside of the mapping.

In practice it means, that almost any one-to-many and entity mapping should contain BatchSize(25) (or 50 or 100... play with to find out what suites to you)

To illustrate that, I adjusted one of the mappings above

public QuestionMap()
{
    Table("Questions");
    // here, load this in batches by 25
    BatchSize(25);
    Id(x => x.Id, "QuestionId").GeneratedBy.Identity().UnsavedValue(0);
    Map(x => x.InternalName);
    Map(x => x.IsActive, "ActiveFlag");

    HasMany(x => x.Choices)
        .KeyColumn("QuestionId")
        .AsBag()
        .Cascade
        .AllDeleteOrphan()
        .Inverse()
        // here again
        .BatchSize(25)
        .Not.LazyLoad();
}

Next step, would depend on the life time of the session. If we will use using(var session...){} we are in troubles. The above stuff won't work - outside of the session. All that must be populated via the session. So how to solve it?

The best would be to append some method to iterate though objects and convert them into some "DTO"

using (var session = sessionFactory.OpenSession())
{
    var questionsForSurvey = session.Query<SiteSurveyQuestion>()
        .Where(x => x.Site.Id == 1 && x.Survey.Id == 1)
        .ToArray();

    var result = new List<SiteSurveyQuestionDTO>();
    foreach(var s  in questionsForSurvey)
    {
       // here we can touch all the inner properties and collections
       // so NHibernate will load all needed data in batches
       var dto = s.doSomething();
       result.Add(dto);
    }
}

My preferred way would be to implement IClonable and inside of the .Clone() touche what is needed

using (var session = sessionFactory.OpenSession())
{
    var questionsForSurvey = session.Query<SiteSurveyQuestion>()
        .Where(x => x.Site.Id == 1 && x.Survey.Id == 1)
        .ToArray()
        .Select(s => s.Clone() as SiteSurveyQuestion);
}

Check the Prototype pattern. Some more stuff about life cycle. And also, some more about batch fetching

Thanks for the reply. I was completely unaware of the idea of .BatchSize() and in my local testing, that has already reduced the real N+1 to a grand total of 2 batches of queries. Thanks! — Peter Seale, Feb 17 '15 at 17:10

score 2 · Answer 2 · answered Feb 17 '15 at 01:55

2

Here is one way you could avoid fetches from the database when accessing the collections:

var questions = session.Query<Question>()
    .Fetch(x => x.Choices)
    .ToList();

var choices = session.Query<Choice>()
    .Fetch(x => x.Questions)
    .ToList();

var questionsForSurvey = session.Query<SiteSurveyQuestion>()
    .Where(x => x.Site.Id == 1 && x.Survey.Id == 1)
    .ToArray();

The above obviously isn't ideal because it loads all questions and choices but you need tp link a Question to SiteSurveyQuestion by setting up a collection of SiteSurveyQuestion in Question to filter it like you want. This way you could just load the questions and choices you needed to based on your survey and site id.

answered Feb 17 '15 at 01:55

Cole W

15,123
6
51
85

Thanks for the reply. In our real system we have 10000+ Choices, so in my case I can't load the entire table. – Peter Seale Feb 17 '15 at 17:09
@PeterSeale Really the above was a simple example of how to avoid the n+1. To avoid querying all questions and choices you would just need to modify the mapping for question slightly as mentioned above. If modified you could add a Where clause to the above queries and only include the results you wanted. I'm guessing it would be more efficient than batching if done properly. – Cole W Feb 17 '15 at 17:56
Cole I am still holding out hope that I can set up a perfect mapping that will eliminate the need for Fetch() calls at query time. See my answer to this question for my best specific answer as of now, feel free to modify it. – Peter Seale Feb 17 '15 at 19:52

score 0 · Answer 3 · answered Feb 17 '15 at 19:51

Using Radim's answer about setting batch sizes above, plus adding explicit eager loading for the Site and Survey entities, got me to a reasonably good 6 queries in one batch up-front, plus 2 additional queries in a second batch when accessing the first sub-question.

The N+1 has become N/300 + 1 which ends up being ... a grand total of 8 queries in 2 batches.

Here is what I changed (look for lines with //new comments):

public class SiteSurveyQuestionMap : ClassMap<SiteSurveyQuestion>
{
    public SiteSurveyQuestionMap()
    {
        Table("SiteSurveyQuestions");
        CompositeId()
            .KeyReference(x => x.Site, "SiteId")
            .KeyReference(x => x.Survey, "SurveyId")
            .KeyReference(x => x.Question, "QuestionId");

        References(x => x.Site, "SiteId"). Not.LazyLoad();  //new
        References(x => x.Survey, "SurveyId").Not.LazyLoad();  //new
        References(x => x.Question, "QuestionId").Not.LazyLoad();  //new

        Map(x => x.IsActive, "ActiveFlag").Not.Nullable();
    }
}

//and so on

public class QuestionMap : ClassMap<Question>
{
    public QuestionMap()
    {
        Table("Questions");
        Id(x => x.Id, "QuestionId").GeneratedBy.Identity().UnsavedValue(0);
        Map(x => x.InternalName);
        Map(x => x.IsActive, "ActiveFlag");

        HasMany(x => x.Choices)
            .KeyColumn("QuestionId")
            .BatchSize(300)  //new
            .AsBag()
            .Cascade
            .AllDeleteOrphan()
            .Inverse()
            .Not.LazyLoad();

    }
}

public class ChoiceMap : ClassMap<Choice>
{
    public ChoiceMap()
    {
        Table("Choices");
        Id(x => x.Id, "ChoiceId").GeneratedBy.Identity().UnsavedValue(0);
        Map(x => x.InternalName);
        Map(x => x.IsActive, "ActiveFlag");
        HasMany(x => x.Questions)
            .KeyColumn("ChoiceId")
            .BatchSize(300)  //new
            .AsBag()
            .Cascade
            .AllDeleteOrphan()
            .Inverse();
    }
}

With the above changes, the SQL created by NHibernate is acceptable.

If anyone can improve upon this answer, please do.

Querying via NHibernate without an N+1 - sample included

3 Answers3

19.1.5. Using batch fetching

Linked