通过NHibernate查询，不包含N + 1 - 样本 [英] Querying via NHibernate without an N+1 - sample included

查看：135
本文介绍了通过NHibernate查询，不包含N + 1 - 样本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个N + 1的问题，我不知道如何解决它。



一个完全可重现的样本可以在这个问题的底部找到。所以，如果你愿意，请创建数据库，设置NUnit测试和所有伴随的类，并尝试在本地消除N + 1。这是我遇到的一个真实问题的匿名版本。你知道，这个代码对于帮助发射下一个航天飞机到达月球是至关重要的。如果被问到，我不会否认。

总结问题：我试图查询下面绘制的表结构。关于这个表格结构的唯一奇怪的事情是，问题有选择，然后有子问题，然后有子选择。你可以假设只有2个层次的问题 - >选择 - >问题 - >选择。
+ ---网站
|
+ ---调查
|
+ ---问题
|
+ ---选择
+ ---选择
+ ---选择
|
+ ---问题
+ ---问题
+ ---问题
|
+ ---选择
+ ---选择
+ ---选择

我尝试了一切我所知道的尝试。

在映射中，我尝试了大量的引用字段作为 .Not.LazyLoad（）没有真正的成功。

我也试着修改查询 .Fetch（）和 .FetchMany（）和 .ThenFetchMany（） code>甚至尝试运行多个 .ToFuture（）查询。这些 do 对SQL查询进行了实际更改，但并不是我要查找的最终结果。



是在本网站上给我一份本调查所有问题的清单，包括所有的子问题。这里是查询：

$ $ p $ $ $ $ c $使用（var session = sessionFactory.OpenSession（））
{
var （）= xSite.Id == 1&& x.Survey.Id == 1）
.ToArray（） ;





$ b

所以最后问问题：如何解决这个N + 1问题？我会很满意以下任何一种：

$ ul
<首选项>修复类映射，以便加载所有内容

  • （第二选择）使用LINQ提供程序使用提取或查询提示来收集查询

  • （第三选择）混合上述
  • （第四选择）被告知这是不可能的和NHibernate的限制

  • （第五选择）HQL中的解决方案


    我不想要一个HQL解决方案，因为我不知道我在做什么我的映射和/或查询错误 - 我觉得我错过了一些基本的东西 ，我什至不知道在哪里看。





    示例说明：


    1. 将SQL安装脚本复制并粘贴到本地SQL Server实例中，运行它。 创建一个测试项目懒惰，使用你现有的测试项目），并添加NHibernate和流利NHibernate的nuget包到项目。

    2. 运行测试。您应该看到：


      1. 由NHibernate运行的生成的SQL

      2. 从测试中输出

      >


    3. 修正映射/查询，直到N + 1消失 - 当您首先看到输出的一堆SQL脚本时，您将知道：
      4. >

      ```

       网站：Site1 Survey：SurveyAboutCats Q： ？ 
       o是
      问：你昨天喂了多少只猫？ $ b $博1 $ b $博2-5 $ b $博6-10 $ b $博11-20 $ b $博20+ $ b $博100+ 
      问：你花了多少钱猫每年？ 
       o 0-100 
       o 100-500 
       o 500-2000 
       o 2000+ 
       o不$ 
      问：没有猫？你怎么了？ 
       o对不起
       Site：Site1 Survey：SurveyAboutCats问：拥有一只狗？ 
      o是
      o否





      完整示例：

        / * 
       Nuget包：
       
       < package id =NHibernateversion =3.3.3.4001targetFramework =net45/> 
        
       * / 
       
      使用System; 
       using System.Collections.Generic; 
      使用System.Linq; 
      使用System.Reflection; 
      使用FluentNHibernate.Cfg; 
      使用FluentNHibernate.Cfg.Db; 
      使用FluentNHibernate.Conventions.Helpers; 
      使用FluentNHibernate.Mapping; 
      使用NHibernate; 
      使用NHibernate.Cfg; 
      使用NHibernate.Linq; 
      使用NUnit.Framework; 
       
      命名空间StackOverflow.CryForHelp 
       {
       [TestFixture] 
       public class NHibernateMappingTests 
       {
       [Test] 
       public void ShouldMapEntitiesWithoutNPlusOneIssue （）
       {
       //排列
       var connectionString =Data Source =（local）; Initial Catalog = NinetyNineProblemsAndAnNPlusOne; Integrated Security = SSPI;; 
       
      配置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（）; 
      配置fluentConfiguration = null; 
       
       ISessionFactory sessionFactory = Fluently.Configure（applicationConfiguration）
       .Mappings（m => 
       {
       m.FluentMappings.Conventions.Setup（x => x .Add（AutoImport.Never（）））; 
      .FluentMappings.AddFromAssembly（Assembly.GetAssembly（GetType（）））; 
      }）
       
       .ExposeConfiguration（c = > fluentConfiguration = c）
       .Database（MsSqlConfiguration.MsSql2008.ConnectionString（connectionString））
       .BuildSessionFactory（）; 
       
       var mappings = fluentConfiguration.ClassMappings; 
       
       // Act +断言我们不要使用（var session = sessionFactory.OpenSession（））
       {
       var questionsForSurvey创建N + 1个查询
       = session.Query< SiteSurveyQuestion>（）
       .Where（x => x.Site.Id == 1&& x.Survey.Id == 1）
       .ToArray（）; 
       
       foreach（在questionsForSurvey中的问题）
       {
       Console.WriteLine（Site：{0} Survey：{1} Q：{2}，question.Site。名称，question.Survey.Name，question.Question.InternalName）; 
       
       foreach（question.Question.Choices中的var选项）
       {
       Console.WriteLine（\t>+ choice.InternalName）; 
       $ b foreach（options中的var subQuestion）
       {
       Console.WriteLine（\t\tQ：+ subQuestion.InternalName）; 
       $ b foreach（subQuestion.Choices中的subChoice）
       Console.WriteLine（\t\t\t）+ subChoice.InternalName; 
       
       
       
       
       
       
       
       $ b公共类网站
       { 
       public virtual int Id {get;组; } 
       public virtual string Name {get;组; } 
      } 
       
       public class Survey 
       {
       public virtual int Id {get;组; } 
       public virtual string Name {get;组; } 
      } 
       
       public class SiteSurvey 
       {
       public virtual Site Site {get;组; } 
       public virtual Survey Survey {get;组; } 
       public virtual string Status {get;组; } 
       public virtual string Name {get;组; } 
       
       public virtual bool Equals（SiteSurvey other）
       {
       if（ReferenceEquals（null，other））return false; 
       if（ReferenceEquals（this，other））返回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））返回true; 
       if（obj.GetType（）！= this.GetType（））return false; 
       return Equals（（SiteSurvey）obj）; 
       
       $ b public public int GetHashCode（）
       {
       unchecked 
       {
       return（Survey.Id * 397）^ Site.Id ; 
       
       
       
       
       public class SiteSurveyQuestion 
       {
       public virtual Site Site {get;组; } 
       public virtual Survey Survey {get;组; } 
       public virtual Question question {get;组; } 
       public virtual bool IsActive {get;组; } 
       $ b $ public virtual bool Equals（SiteSurveyQuestion other）
       {
       if（ReferenceEquals（null，other））return false; 
       if（ReferenceEquals（this，other））返回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））返回true; 
       if（obj.GetType（）！= this.GetType（））return false; 
       return Equals（（SiteSurveyQuestion）obj）; 
       
       
       
       
       
       
       
       
       
       $（$（$（$ Survey $） 397）^ Site.Id）* 397）; 
       
       
       
       
       public class Question 
       {
       public virtual int Id {get;组; } 
      公共虚拟字符串InternalName {get;组; } 
       public virtual bool IsActive {get;组; } 
       public virtual IEnumerable< Choice>选择{get;组; } 
      } 
       
       public class Choice 
       {
       public virtual int Id {get;组; } 
      公共虚拟字符串InternalName {get;组; } 
       public virtual bool IsActive {get;组; } 
       public virtual IEnumerable< Question>问题{get;组; } 
      } 
       
       public class SurveyMap：ClassMap< Survey> 
       {
       public SurveyMap（）
       {
      表（调查）; 
       Id（x => x.Id，SurveyId）。GeneratedBy.Identity（）。UnsavedValue（0）; 
       Map（x => x.Name）.Not.Nullable（）; 
      } 
      } 
       
       public class SiteMap：ClassMap< Site> 
       {
       public SiteMap（）
       {
      表（网站）; 
       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（）
       {
      表（问题）; 
       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（）
       {
      表（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（）
      。反转（）; 
       
       
       
       
       
       
       
       
       
       
       
       
       
       $ b使用[master] 
       GO 
       
      创建数据库[NinetyNineProblemsAndNnNPlusOne] 
       GO 
       
       USE [NinetyNineProblemsAndNnNPlusOne] 
       GO 
       
       SET ANSI_NULLS ON 
       GO 
       
       SET QUOTED_IDENTIFIER ON 
       GO 
       
       SET ANSI_PADDING ON 
       GO 
       
       CREATE TABLE [dbo]。[站点]（
       [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 ] 。[调查]（
       [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 
       $ ON $ [
      ] ON [PRIMARY] 
       
       GO 
       
       
       CREATE TABLE [dbo]。[SiteSurveys]（
       [SiteId] [int] NOT NULL，
       [SurveyId] [int] NOT NULL，
       [名称] [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] [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] 
       [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] 
       
       
       
       
       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] [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]。[Choi ces]（
       [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]。[调查] （[Status] ='Live'或[Status] ='NotLive'或[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 ] [$ Status] ='Live'或[Status] ='NotLive'或[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]）
       
       
       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]。 [选择]（[ChoiceId]）
       GO 
       
       ALTER TABLE [dbo]。[选项] WITH CHECK ADD CONSTRAINT [R408] FOREIGN KEY（[QuestionId]）
       REFERENCES [ dbo]。[Questions]（[QuestionId]）
       GO 
       
       ALTER TABLE [dbo]。[选项] CHECK CONSTRAINT [R408] 
       GO 
       
       
       SET ANSI_PADDING OFF 
       GO 
       
       
       
       SET IDENTITY_INSERT [dbo]。[Sites] ON 
       INSERT [dbo]。[Sites]（[SiteId]，[ （2，N'Site2'）
       SET IDENTITY_INSERT [dbo]。[站点]（[SiteId]，[Name]）VALUES ]调查] [调查]（[SurveyId]，[名称]，[状态]，[调查]关于
       
       SET IDENTITY_INSERT [调查] 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] 。[问题]（[QuestionId]，[InternalName]，[ChoiceId]，[ActiveFlag]）VALUES（2，N'Own A Dog？'，NULL，1）
       INSERT [dbo]。 [QuestionId]，[InternalName]，[ChoiceId]，[ActiveFlag]）VALUES（ 3，N'Witnessed任何犯罪最近？'，NULL，1）
       INSERT [dbo]。[Questions]（[QuestionId]，[InternalName]，[ChoiceId]，[ActiveFlag]）VALUES（4，N' '，NULL，1）
       SET IDENTITY_INSERT [dbo]。[Questions] OFF 
       
       SET IDENTITY_INSERT [dbo]。[选项] ON 
       INSERT [ dbo] [选项]（[ChoiceId]，[QuestionId]，[InternalName]，[ActiveFlag]）VALUES（1，1，N'Yes'，1）
       INSERT [dbo]。[Choices] （[ChoiceId]，[QuestionId]，[QuestionId]，[InternalName]，[ActiveFlag]）VALUES（2,1，N'No'，1）
       INSERT [dbo] 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 a''t no s （7，4，N'No'，1）$ b $ INSERT [dbo]。[选项]（[ChoiceId]，[QuestionId]，[InternalName]，[ActiveFlag] b INSERT [dbo]。[Choices]（[ChoiceId]，[QuestionId]，[InternalName]，[ActiveFlag]）VALUES（8，4，N'I请求第五个，1）
       SET IDENTITY_INSERT [dbo ] [选项] OFF 
       
       SET IDENTITY_INSERT [dbo]。[Questions] ON 
       INSERT [dbo]。[Questions]（[QuestionId]，[InternalName]，[ChoiceId] ActiveFlag]）VALUES（6，N'No猫？你有什么问题？'，2,1）
       INSERT [dbo]。[Questions]（[QuestionId]，[InternalName]，[ChoiceId]，[ActiveFlag]）VALUES（7，N'How many cats （1，1）
       INSERT [dbo]。[Questions]（[QuestionId]，[InternalName]，[ChoiceId]，[ActiveFlag]）VALUES（8，N'How每年花在猫身上？'，1，1）
       SET IDENTITY_INSERT [dbo]。[Questions] OFF 
       
       SET IDENTITY_INSERT [dbo]。[Choices] ON 
       INSERT [dbo ] [选项]（[ChoiceId]，[QuestionId]，[InternalName]，[ActiveFlag]）VALUES（9，6，N'I sorry'，1）
       INSERT [dbo]。[Choices]（ [ChoiceId]，[QuestionId]，[InternalName]，[ActiveFlag]）VALUES（10，7，N'1'，1）
       INSERT [dbo] [选项]（[ChoiceId]，[QuestionId]， [InternalName]，[ActiveFlag]）VALUES（11,7，N'2-5'，1）
       INSERT [dbo]。[Choices]（[ChoiceId]，[QuestionId]，[InternalName]，[ActiveFlag ] [VALUE（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] [选项]（[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]，[问号]，[内部名称]，[ActiveFlag]）VALUES（18，8，N'500-2000'，1）
       INSERT [dbo]。[ChoiceId]，[QuestionId]，[InternalName] ，[ActiveFlag]）VALUES（19，8，N'2000 +'，1）
       SET IDENTITY_INSERT [dbo]。[选项] 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 （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调查问卷调查'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 
       
       
       
       
       
       
       
       
       
       
       
       
        -  * /


      解决方案

      <1>一个特殊的NHibernate 优化功能（让我引用一下）

      /nhibernate.info /doc/nh/en/index.html#performance-fetching-batchrel =nofollow noreferrer> 19.1.5。使用批量抓取


      NHibernate可以高效地使用批量抓取，也就是说，NHibernate可以加载一些未初始化的代理，如果一个代理（或集合）批量读取是对懒惰选择读取策略的优化有两种方式可以调整批量读取：在类和集合级别上。

      对于类/实体的批量获取比较容易理解，假设你在运行时有以下情况：在一个ISession中加载了25个Cat实例，每个Cat都有一个对它的Owner，Person的引用，Person类映射为proxy，lazy =true。如果你现在迭代所有的猫，并调用cat.Owner，NHibernate将默认执行25条SELECT语句来检索代理的所有者，你可以通过指定一个批处理大小Person的映射：



       < class name =Personbatch-size = 10\" > ...< /类>




      NHibernate现在只会执行三个查询，模式是10,10 ，5。

      您也可以启用批量获取集合。例如，如果每个Person都有一个惰性的Cat集合，并且当前在ISesssion中加载了10个人，那么通过遍历所有人就会生成10个SELECT，每个调用一个Person.Cats。如果在Person的映射中为Cats集合启用批量抓取，NHibernate可以预取集合： 



       < class name =Person> 
      < set name =Catsbatch-size =3> 
       ... 
      < / set> 
      < / class>




      使用3的批处理大小，NHibernate将加载3，3 ，4个SELECT中的3,1个集合。此外，属性的值取决于特定会话中未初始化集合的预期数量。

      所以，那就是DOC。这个解决方案的好处在于，我们将在映射中具有简单的查询和优化。

      实际上，这意味着几乎任何一对多和实体映射都应该包含 BatchSize（25） code> （或者50或者100 ...和你一起玩，找出你的套房）



      为了说明这一点，调整了上面的映射之一

        public QuestionMap（）
       {
       Table（Questions）; 
       //在这里，分批加载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 here 
       .BatchSize（25）
       .Not.LazyLoad（）; 
      }

      下一步将取决于会话的生命期。如果我们使用 使用（var session ...）{} ，我们就会陷入困境。以上的东西不会工作 - 会议之外。所有必须通过会话填充。那么如何解决它呢？

      最好的方法是追加一些方法来遍历对象并将它们转换成一些DTO
      $使用（var session = sessionFactory.OpenSession（））
      {
      var questionsForSurvey = session.Query< SiteSurveyQuestion>（）
      使用b $ b 

        .Where（x => x.Site.Id == 1&& x.Survey.Id == 1）
       .ToArray（）; 
       
       var result = new List< SiteSurveyQuestionDTO>（）; 
       foreach（var in questionForSurvey）
       {
       //这里我们可以触及所有的内部属性和集合
       //所以NHibernate会加载批量所有需要的数据
       var dto = s.doSomething（）; 
       result.Add（dto）; 
       
       
       
       
       
       
      我的首选方法是实现 IClonable 和 .Clone（） touche需要什么
      使用（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）; 
       
              
      检查原型模式。 关于生命周期的更多内容。还有一些关于批量抓取 
       
      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:
        

      1. Copy and paste the SQL setup script into your local SQL Server instance, run it.
        2. 

      2. 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.
        3. 

      3. Run the test. You should see:

          

        1. Generated SQL run by NHibernate
          2. 

        2. Output from the test.
          3. 

        4. 

      4. Fix mappings/query until N+1 is gone - you will know when you see first a bunch of SQL scripts outputted, then:
        5. 

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












-- */

      

       解决方案 
      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
      

                        
      这篇关于通过NHibernate查询，不包含N + 1  - 样本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
    • 查看全文
    相关文章
    C#/.NET最新文章
    热门教程
    热门工具
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆