NHibernate分页标准与fetchmode渴望。 （使用流利的NH） [英] NHibernate paging criteria with fetchmode eager. (using fluent NH)

问题描述

问题：如何得到一个急切的加载标准，返回分页结果的根实体与所有子集合设置fetchmode = eager。

我试图得到一个10项分页结果集与预先加载的子集合。问题是，查询做了一个选择前10包裹整个选择。导致它只返回前10个结果，包括所有连接的记录。如果第一个实体有10个子对象，那么我的结果集将返回1个实体，并加载10个子对象。我需要的实体和儿童收集返回水分（懒惰）。如果我关闭延迟加载并运行这个查询，我会得到结果集中每个关联的n + 1个查询。

这是我的基本查询过程：

  criteria = context.Session.CreateCriteria< Associate>（）; 
 criteria.SetMaxResults（10）; //硬编码用于测试
 criteria.SetFirstResult（1）; //硬编码用于测试
 criteria.SetFetchMode（Roles，NHibernate.FetchMode.Eager）; 
 criteria.SetFetchMode（Messages，NHibernate.FetchMode.Eager）; 
 criteria.SetFetchMode（DirectReports，NHibernate.FetchMode.Eager）; 
 criteria.SetResultTransformer（new DistinctRootEntityResultTransformer（））; 
返回criteria.List< Associate>（）; 
 
 $ b $ public AssociateMap（）
 {
 ReadOnly（）; 
 Id（x => x.AssociateId）; 
 Map（x => x.FirstName）; 
 Map（x => x.LastName）; 
 Map（x => x.ManagerId）; 
 Map（x => x.Department）; 
 Map（x => x.Email）; 
 Map（x => x.JobTitle）; 
 $ b $ Map（x => x.LastFirstName）.Formula（LTRIM（RTRIM（LastName））+'，'+ LTRIM（RTRIM（FirstName）））; 
 
 HasMany（x => x.Messages）.KeyColumn（AssociateId）。Inverse（）。Cascade.All（）; 
 HasMany（x => x.Roles）.Element（RoleKey）; 
 HasMany（x => x.DirectReports）.KeyColumn（ManagerId）。Cascade.None（）。ForeignKeyConstraintName（FK_Associate_Manager）; 
 // HasMany（x => x.DirectReports）.Element（ManagerId）。CollectionType（typeof（Domain.Associate））; 
 
 
 
  

解决方案

<解决方案最终使用子查询来设置最大结果。我使用Subqueries.PropertyIn添加了子查询。我将标准克隆到限制器，因为我在代码中添加了标准表达式。所以我需要将这些标准克隆到子查询中，所以前10名选择将在IN语句中。现在我可以急切地加载子集合，并将分页添加到根实体，以获得10个元素，而没有笛卡儿或n + 1的问题。

  // criteria = context.Session.CreateCriteria< Associate>（ ）; 
 //将标准更改为DetachedCriteria。 
 criteria = DetachedCriteria.For< Associate>（）; 
 
 DetachedCriteria限制器= CriteriaTransformer.Clone（条件）; 
 limiter.SetProjection（Projections.Id（））; 
 limiter.SetMaxResults（10）; 
 criteria.Add（Subqueries.PropertyIn（AssociateId，limiter））; 
 
 criteria.SetFetchMode（Roles，NHibernate.FetchMode.Eager）; 
 criteria.SetFetchMode（Messages，NHibernate.FetchMode.Eager）; 
 criteria.SetFetchMode（DirectReports，NHibernate.FetchMode.Eager）; 
 criteria.SetResultTransformer（new DistinctRootEntityResultTransformer（））; 
返回criteria.List< Associate>（）; 
  

Question: How to get an eager loaded criteria to return paged results on the root entity with all child collections set fetchmode = eager.

I am trying to get a 10 item paged result set with eager loaded child collections. The problem is the query does a select top 10 wrapped around the entire select. The causes it to return only the first 10 results including all joined records. If the first entity has 10 child objects then my result set will return 1 entity with 10 child objects loaded. I need the entities and child collections returned hydrated (lazy off). If I turn lazy loading off and run this query I get the n+1 query for each associate in result set.

This is my basic query process:

criteria = context.Session.CreateCriteria<Associate>();
criteria.SetMaxResults(10); //hardcoded for testing
criteria.SetFirstResult(1); //hardcoded for testing
criteria.SetFetchMode("Roles", NHibernate.FetchMode.Eager);
criteria.SetFetchMode("Messages", NHibernate.FetchMode.Eager);
criteria.SetFetchMode("DirectReports", NHibernate.FetchMode.Eager);
criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());
return criteria.List<Associate>();


public AssociateMap()
    {
        ReadOnly();
        Id(x => x.AssociateId);
        Map(x => x.FirstName);
        Map(x => x.LastName);
        Map(x => x.ManagerId);
        Map(x => x.Department);
        Map(x => x.Email);
        Map(x => x.JobTitle);

        Map(x => x.LastFirstName).Formula("LTRIM(RTRIM(LastName)) + ', ' + LTRIM(RTRIM(FirstName))");

        HasMany(x => x.Messages).KeyColumn("AssociateId").Inverse().Cascade.All();
        HasMany(x => x.Roles).Element("RoleKey");
        HasMany(x => x.DirectReports).KeyColumn("ManagerId").Cascade.None().ForeignKeyConstraintName("FK_Associate_Manager");
        //HasMany(x => x.DirectReports).Element("ManagerId").CollectionType(typeof(Domain.Associate));


    }

解决方案

The solution ended up using a subquery to set the max results. I added the subquery using Subqueries.PropertyIn. I am cloning the "criteria" to "limiter" because I added criterion expression in code not shown. So I need to clone these criterion into the subquery so the top 10 select will be in the "IN" statement. Now I can eager load the child collections and add pagination to the root entity to get 10 enties back without issues with cartesian or n+1. I will try to follow up with more complete and organized code.

//criteria = context.Session.CreateCriteria<Associate>(); 
//changed criteria to DetachedCriteria.
criteria = DetachedCriteria.For<Associate>();

DetachedCriteria limiter = CriteriaTransformer.Clone(criteria); 
limiter.SetProjection(Projections.Id());
limiter.SetMaxResults(10);
criteria.Add(Subqueries.PropertyIn("AssociateId", limiter));

criteria.SetFetchMode("Roles", NHibernate.FetchMode.Eager); 
criteria.SetFetchMode("Messages", NHibernate.FetchMode.Eager); 
criteria.SetFetchMode("DirectReports", NHibernate.FetchMode.Eager); 
criteria.SetResultTransformer(new DistinctRootEntityResultTransformer()); 
return criteria.List<Associate>(); 

这篇关于NHibernate分页标准与fetchmode渴望。 （使用流利的NH）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！