NHibernate：选择一个到多个左连接 - 从父节点获取X最新 [英] NHibernate: Select one to Many Left Join - Take X latest from Parent

问题描述

我有以下对象：

$ p $ 父
public virtual Guid Id {get;组; }
public virtual DateTime TimeStamp {get;组; }
公共虚拟IList< Child>孩子{get;组; }

小孩
公共虚拟Guid Id {get;组; }
public virtual string Name {get;组; }


我使用Fluent来映射一对多，如下所示：

  .Override< Parent>（obj => obj.HasMany（x => x.Childs）
 .Cascade.All（）
 .Not.Inverse（）
 .Not.KeyNullable（）
 .Not.KeyUpdate（））
  

我需要通过TimeStamp获得所有父母与孩子之间的日期顺序。

我想这样做（maxCapacity是int ）：

  QueryOver< Parent>（）其中（x => x.TimeStamp> from）
 .And（x => x.TimeStamp&to）.OrderBy（x => x.TimeStamp）.Desc 
 .Left.JoinQueryOver< Child>（x => x.Childs）
 .TransformUsing（new DistinctRootEntityResultTransformer（））
 .Take（maxCapacity）.List（）; 
  

由于 Take（maxCapacity）的结果不符合我的预期不是父结果，而是包含父和子的总查询结果。

如何获取最新的X已经转换的父行？

谢谢。

解决方案

$ b

• 加载根实体（Parent）和


• 的列表让NHibernate加载他们的孩子 - 分开的SQL查询。为避免1 + N问题，我们可以使用智能映射功能：
  
  $ b p> 19.1.5。使用批量抓取

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

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

  
  blockquote>
  
  

  所以，查询应该是这样的：
  
  

    session.QueryOver< Parent>（）
   .Where（x => x.TimeStamp> from）
  。并且（x => x.TimeStamp< to）.OrderBy（x => x.TimeStamp）.Desc 
   //.Left.JoinQueryOver< ChildChild>（x => x.Childs）
   // .TransformUsing（new DistinctRootEntityResultTransformer（） ）
   .Skip（start）//分页
   .Take（maxCapacity）
   .List< Parent>（）; 
    

  和父映射应该是这样的：
  
  

   < class name =Parent> 
   ... 
  < bag name =Childsbatch-size =3> 
   ... 
  < / bag> 
  < / class> 
    

  请检查以下内容：
  
  

  
  
  • 如何在不重复NHibernate的情况下加载关联
  
  
  • NHibernate的QueryOver与提取导致多个SQL查询和数据库匹配
  
  
  • 这是正确的方式来加载NHibernate中的子集合
  
  

  
  

  I have the following objects:

  Parent
     public virtual Guid Id { get; set; }
     public virtual DateTime TimeStamp { get; set; }
     public virtual IList<Child> Childs { get; set; }
  
  Child
     public virtual Guid Id { get; set; }
     public virtual string Name { get; set; }
  

  I use Fluent to Map One To Many as follows:

  .Override<Parent>(obj =>obj.HasMany(x => x.Childs)
                                  .Cascade.All()
                                  .Not.Inverse()
                                  .Not.KeyNullable()
                                  .Not.KeyUpdate())
  

  I need to get up to all Parent with Childs between dates order by TimeStamp.

  I am trying to do it as follows (maxCapacity is int):

  QueryOver<Parent>().Where(x => x.TimeStamp > from)
              .And(x => x.TimeStamp < to).OrderBy(x => x.TimeStamp).Desc
              .Left.JoinQueryOver<Child>(x => x.Childs)
              .TransformUsing(new DistinctRootEntityResultTransformer())
              .Take(maxCapacity).List();
  

  The result is not what I expected since the Take(maxCapacity) is not on the parent result but on the total query result which includes parent and child.

  How can I get the latest X already transformed Parent rows?

  Thanks.

  解决方案

  The way I would go here is:

  • load the list of root entity (Parent) and
  • let NHibernate load their children lazily - in separated SQL query.

  To avoid 1 + N issue, we can use smart mapping feature:

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

  So, the query should be like this:

  session.QueryOver<Parent>()
      .Where(x => x.TimeStamp > from)
      .And(x => x.TimeStamp < to).OrderBy(x => x.TimeStamp).Desc
      //.Left.JoinQueryOver<Child>(x => x.Childs)
      // .TransformUsing(new DistinctRootEntityResultTransformer())
      .Skip(start) // paging
      .Take(maxCapacity)
      .List<Parent>();
  

  And the parent mapping should be like:

  <class name="Parent">
      ...
      <bag name="Childs" batch-size="3">
          ...
      </bag>
  </class>
  

  Please, check also these:

  • How to Eager Load Associations without duplication in NHibernate?
  • NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
  • Is this the right way to eager load child collections in NHibernate

  这篇关于NHibernate：选择一个到多个左连接 - 从父节点获取X最新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！