流利的nHibernate在单个查询中获取HasMany项目 [英] Fluent nHibernate Getting HasMany Items In Single Query
问题描述
我有一个主题地图,其中有很多帖子,即...(在底部HasMany(x => x.Posts))
public TopicMap()
{
Cache.ReadWrite()。IncludeAll();
Id(x => x.Id);
Map(x => x.Name);
*许多其他法线贴图*
参考文献(x => x.Category).Column(Category_Id);
引用(x => x.User).Column(MembershipUser_Id);
引用(x => x.LastPost).Column(Post_Id)。Nullable();
HasMany(x => x.Posts)
.Cascade.AllDeleteOrphan()。KeyColumn(Topic_Id)
.Inverse();
*和其他一些HasManys *
}
I写了一个查询,获取最新的分页主题,循环显示数据和一些帖子数据(如子帖子的数量等)。这里是查询
public PagedList< Topic> GetRecentTopics(int pageIndex,int pageSize,int amountToTake)
{
//获取延迟行数
var rowCount = Session.QueryOver< Topic>()
.Select(Projections .RowCount())
.Cacheable()。CacheMode(CacheMode.Normal)
.FutureValue< int>();
$ b var results = Session.QueryOver< Topic>()
.OrderBy(x => x.CreateDate).Desc
.Skip((pageIndex - 1)* pageSize )
.Take(pageSize)
.Cacheable()。CacheMode(CacheMode.Normal)
.Future< Topic>()。ToList();
var total = rowCount.Value;
if(total> amountToTake)
{
total = amountToTake;
}
//返回一个分页列表
返回新的PagedList< Topic>(results,pageIndex,pageSize,total);
$ b当我在这上面使用SQLProfiler的时候,数据库命中来抓取父主题的所有帖子。因此,如果我有10个主题,我抓到了10个数据库命中,因为它抓住了职位。
我可以改变这个查询抓住帖子,以及在一个单一的查询?我想某种联接?
解决方案您可以使用 Fetch.xxx
在 HasMany
属性映射上。可用选项是 Fetch.Join()
, Fetch.Select()
和 Fetch.SubSelect ()
。在NHibernate的文档中可以找到更多关于每种类型的信息。 a>。
HasMany(x => x.Posts)
.Cascade.AllDeleteOrphan()。KeyColumn Topic_Id)
.Fetch.Join()
.Inverse();
I have a Topic map which has many posts in it i.e… (At the bottom HasMany(x => x.Posts))
public TopicMap()
{
Cache.ReadWrite().IncludeAll();
Id(x => x.Id);
Map(x => x.Name);
*lots of other normal maps*
References(x => x.Category).Column("Category_Id");
References(x => x.User).Column("MembershipUser_Id");
References(x => x.LastPost).Column("Post_Id").Nullable();
HasMany(x => x.Posts)
.Cascade.AllDeleteOrphan().KeyColumn("Topic_Id")
.Inverse();
*And a few other HasManys*
}
I have written a query which gets the latest paged topics, loops through and displays data and some posts data (Like the count of child posts etc..) . Here is the query
public PagedList<Topic> GetRecentTopics(int pageIndex, int pageSize, int amountToTake)
{
// Get a delayed row count
var rowCount = Session.QueryOver<Topic>()
.Select(Projections.RowCount())
.Cacheable().CacheMode(CacheMode.Normal)
.FutureValue<int>();
var results = Session.QueryOver<Topic>()
.OrderBy(x => x.CreateDate).Desc
.Skip((pageIndex - 1) * pageSize)
.Take(pageSize)
.Cacheable().CacheMode(CacheMode.Normal)
.Future<Topic>().ToList();
var total = rowCount.Value;
if (total > amountToTake)
{
total = amountToTake;
}
// Return a paged list
return new PagedList<Topic>(results, pageIndex, pageSize, total);
}
When I use SQLProfiler on this, as I loop over the topics is does a db hit to grab all Posts from the parent topic. So if I have 10 topics, I get 10 DB hits as it grabs the posts.
Can I change this query to grab the posts as well in a single query? I guess some sort of Join?
解决方案 You can define eager fetching using Fetch.xxx
on your HasMany
property mapping. Available options are Fetch.Join()
, Fetch.Select()
and Fetch.SubSelect()
. More info on each type of fetching can be found on NHibernate's documentation.
HasMany(x => x.Posts)
.Cascade.AllDeleteOrphan().KeyColumn("Topic_Id")
.Fetch.Join()
.Inverse();
这篇关于流利的nHibernate在单个查询中获取HasMany项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!