NHibernate筛选的子级集合延迟加载，即使指定了急切的获取 [英] NHibernate Filtered Child Collection Lazy Loaded even with eager fetch specified
问题描述
我正试图找出为什么即使急于加载子集并且生成的SQL是正确的子集也不会进行过滤而返回.
Im trying to find out why a child collection is being returned without filtering even when eager loading the collection and the generated SQL is correct.
类的流利映射为:
public class OptionIdentifierMap : ClassMap<OptionIdentifier>
{
public OptionIdentifierMap()
: base("OptionIdentifier")
{
//Id Mapping Removed
HasMany<OptionPrice>(x => x.OptionPrices)
.KeyColumn("OptionIdentifier_id")
.Cascade.None();
}
}
public class OptionPriceMap : ClassMap<OptionPrice>
{
public OptionPriceMap()
: base("OptionPrice")
{
//Id Mapping removed
References(x => x.Option)
.Column("OptionIdentifier_id")
.Cascade.None()
.ForeignKey("FK_OptionPrice_OptionIdentifier_id_OptionIdentifier_Id")
.Not.Nullable();
References(x => x.Increment)
.Column("PricingIncrement_id")
.Cascade.None()
.ForeignKey("FK_OptionPrice_PricingIncrement_id_PricingIncrement_Id")
.Not.Nullable();
Map(x => x.Price).Not.Nullable();
}
}
和PricingIncrement映射
and PricingIncrement mapping
public class PricingIncrementMap : ClassMap<PricingIncrement>
{
public PricingIncrementMap()
: base("PricingIncrement")
{
Map(x => x.IncrementYear);
HasMany<OptionPrice>(x => x.Options)
.KeyColumn("PricingIncrement_id")
.Cascade.None().Inverse();
}
}
实体是:
public class PricingIncrement : Entity
{
public PricingIncrement()
{
Options = new List<OptionPrice>();
}
public virtual int IncrementYear { get; set; }
public virtual IList<OptionPrice> Options { get; set; }
}
public class OptionPrice : Entity
{
public OptionPrice()
{
}
public virtual OptionIdentifier Option { get; set; }
public virtual PricingIncrement Increment { get; set; }
public virtual float Price { get; set; }
}
public class OptionIdentifier : Entity
{
public OptionIdentifier()
{
OptionPrices = new List<OptionPrice>();
}
public virtual IList<OptionPrice> OptionPrices { get; set; }
}
我正在尝试查询具有针对特定PricingIncrement的optionprice值的所有OptionIdentifier. nhibernate根据我的条件生成的SQL查询是:
Im trying to query All the OptionIdentifier that have an optionprice value for an specific PricingIncrement. The SQL Query that nhibernate generates from my criteria is:
SELECT this_.Id as Id37_4_,
.......
FROM OptionIdentifier this_ inner join OptionPrice op2_ on this_.Id = op2_.OptionIdentifier_id
inner join PricingIncrement i3_ on op2_.PricingIncrement_id = i3_.Id
WHERE (this_.IsDeleted = 0)
and this_.Id in (7)
and i3_.IncrementYear = 2015
我用来建立此查询的条件是:
The criteria I'm using to build this query is:
ICriteria pagedCriteria = this.Session.CreateCriteria<OptionIdentifier>()
.CreateAlias("OptionPrices", "op", JoinType.InnerJoin)
.CreateAlias("op.Increment", "i", JoinType.InnerJoin)
.SetFetchMode("op", FetchMode.Eager)
.SetFetchMode("i", FetchMode.Eager)
.Add(Restrictions.Eq("i.IncrementYear", 2015))
.Add(Expression.In("Id", idList.ToList<int>()))
.SetResultTransformer(CriteriaSpecification.DistinctRootEntity);
当查看SQL Profiler时，查询将执行并且结果是正确的，我从与OptionIdentifier匹配的4个可用行中得到与条件匹配的OptionPrice表中每个子项的一行(在我的情况下为1)在PricingIncrement中有4行，在OptionPrice中有4行，对于OptionIdentifier_id 7的每个PricingIncrement，有1行.
When looking at SQL Profiler, the query executes and the result is correct, i get one row for each child in the OptionPrice table that matches the criteria, in my case one, from the available 4 rows that match the OptionIdentifier (there are 4 rows in PricingIncrement and 4 in OptionPrice one for each PricingIncrement for the OptionIdentifier_id 7)
但是当我尝试迭代该集合以获取某些值时，出于某种原因，nhibernate正在加载子集合，就像指定了延迟加载一样，并加载完整的4个子行.阅读文档FetchMode应该可以解决此问题，从而防止nhibernate延迟加载子集合.类似于N + 1常见问题.
But when i try to iterate the collection to get some values, for some reason nhibernate is loading the child collection, as if lazy load was specified, and loading the full 4 child rows. Reading the documentation FetchMode is supposed to fix this preventing nhibernate to lazy load child collections. Similar to a N+1 common issue.
我检查了SQL事件探查器以查看发生了什么，当我尝试访问子集合时，nhibernate生成的查询没有原始过滤器来填充子集合.如果我不访问集合，则不会生成查询.
I checked the SQL Profiler to see whats happening and nhibernate is generating queries without the original filter to fill the child collection when i try to access it. If i dont access the collection no query is generated.
进行一些测试时，我尝试了不同的联接类型和提取模式，并且到目前为止，在没有休眠加载所有元素的情况下迭代集合的唯一方法是在联接类型LeftOuterJoin中指定，但这意味着有所不同.
Doing some testing i tried different join types and fetch modes, and so far the only way to iterate the collection without having hibernate load all the elements is to specify in the join type LeftOuterJoin, but this means something different.
我试图搜索类似的问题，但是所有人都说，渴望加载应该起作用，或者提到我应该使用过滤器.到目前为止，我还没有找到任何答案.
I tried to search for issues similar but all of them say that eager loading should work, or mention that i should use filters. And so far i havent found any answer.
非常感谢您的帮助.
推荐答案
我想分享我的方法，也许不是答案...
I would like to share my approach, maybe not the answer...
在创建任何类型的复杂查询(ICriteria，QueryOver)时，我们仅应在 start 模式上使用(LEFT) JOIN. IE.在 many-to-one (流利的References())上.从分页的角度来看，这导致了预期的行数(每个根实体始终只有一个行)
When creating any kind of complex queries (ICriteria, QueryOver) we should use (LEFT) JOIN only on a start schema. I.e. on many-to-one (References() in fluent). That leads to expected row count from the perspective of paging (there is always only ONE row per root Entity)
要避免集合出现1 + N的问题(但实际上甚至是多对一的问题)，我们具有NHiberante强大的功能:
To avoid 1 + N issue with collections (but even with many-to-one in fact) we have the NHiberante powerful feature:
19.1.5. Using batch fetching
NHibernate可以有效地使用批处理获取，也就是说，如果访问一个代理(或集合)，则NHibernate可以加载多个未初始化的代理. ..
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)...
在此处了解更多信息
- How to Eager Load Associations without duplication in NHibernate?
- How to implement batch fetching with Fluent NHibernate when working with Oracle?
因此，在我们的示例中，我们将像这样调整映射:
So, in our case, we would adjust mapping like this:
public PricingIncrementMap()
: base("PricingIncrement")
{
Map(x => x.IncrementYear);
HasMany<OptionPrice>(x => x.OptionPrices)
.KeyColumn("OptionIdentifier_id")
.Cascade.None()
.Inverse() // I would use .Inverse() as well
// batch fetching
.BatchSize(100);
}
II.集合过滤
因此，我们设法避免了1 + N的问题，而且我们仅查询星型模式.现在，我们如何才能加载刚刚过滤的集合中的项目集?好吧，我们拥有原生的NHibernate功能，并且功能非常强大:
II. collection filtering
So, we managed to avoid 1 + N issue, and we also query only star schema. Now, how can we load just filtered set of items of our collection? Well, we have native and again very powerful NHibernate feature:
18.1. NHibernate filters.
NHibernate添加了预定义过滤器条件并将这些过滤器附加到类和集合级别的功能.过滤条件是一种定义限制子句的能力，该限制子句与类和各种集合元素上可用的现有"where"属性非常相似.
NHibernate adds the ability to pre-define filter criteria and attach those filters at both a class and a collection level. A filter criteria is the ability to define a restriction clause very similiar to the existing "where" attribute available on the class and various collection elements...
在此处阅读有关此内容的更多信息:
Read more about it here:
- how to assign data layer-level filters
- Limit collection to retrieve only recent entries for readonly entity
因此，在我们的示例中，我们将定义过滤器
So in our case we would define filter
public class CollFilter : FilterDefinition
{
public CollFilter()
{
WithName("CollFilter")
.WithCondition("PricingIncrement_id = :pricingIncrementId")
.AddParameter("pricingIncrementId",NHibernate.Int32);
}
}
我们将需要再次扩展映射:
And we would need to extend our mapping again:
HasMany<OptionPrice>(x => x.OptionPrices)
.KeyColumn("OptionIdentifier_id")
.Cascade.None()
.Inverse()
// batch fetching
.BatchSize(100)
// this filter could be turned on later
.ApplyFilter<CollFilter>();
现在，在执行查询之前，我们只需要启用该过滤器并提供2015年的正确ID:
Now, before our query will be executed, we just have to enable that filter and provide proper ID of the year 2015:
// the ID of the PricingIncrement with year 2015
var pricingIncrementId thes.Session
.QueryOver<PricingIncrement>()
.Where(x => x.IncrementYear == 2015)
.Take(1)
.Select(x => x.ID)
.SingleOrDefault<int?>();
this.Session
.EnableFilter("CollFilter")
.SetParameter("pricingIncrementId", pricingIncrementId);
// ... the star schema query could be executed here
III.子查询过滤根实体
最后，我们可以使用子查询来限制要与我们的查询一起返回的根实体的数量.
III. Sub-query to filter root entity
Finally we can use sub-query, to restrict the amount of root entities to be returned with our query.
在此处详细了解它:
- Query on HasMany reference
- NHibernate Criteria Where any element of list property is true
因此，我们的子查询可能是
so, our subquery could be
// Subquery
var subquery = DetachedCriteria.For<OptionPrice >()
.CreateAlias("Increment", "i", JoinType.InnerJoin)
.Add(Restrictions.Eq("i.IncrementYear", 2015))
.SetProjection(Projections.Property("Option.ID"));
// root query, ready for paging, and still filtered as wanted
ICriteria pagedCriteria = this.Session.CreateCriteria<OptionIdentifier>()
.Add(Subqueries.PropertyIn("ID", subquery))
.SetResultTransformer(CriteriaSpecification.DistinctRootEntity);
摘要:我们可以使用NHibernate附带的许多功能.他们在那里是有原因的.并与它们一起，我们可以获得稳定可靠的代码，为进一步扩展(首先分页)做好了准备.
Summary: We can use lot of features, which are shipped with NHibernate. They are there for a reason. And with them together we can achieve stable and solid code, which is ready for further extending (paging at the first place)
注意:也许我打错了字...但是总体思路应该很清楚
这篇关于NHibernate筛选的子级集合延迟加载，即使指定了急切的获取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
