在 NHibernate 中分页时，有没有办法覆盖集合的映射 order-by 子句？

发布于 2024-12-06 19:35:33 字数 864 浏览 1 评论 0原文

我有一个带有包集合的实体，我想对整个数据集进行分页。该集合有一个 order-by 子句。当我尝试急切加载集合时，NHibernate 生成的 SQL 速度非常慢，因为它导致 SQL Server 对非常不唯一的属性进行排序，并且表中存在大量数据。

代码：

var session = NHibernateSessionManager.Instance.GetSession();
session.CreateCriteria<Album>()
    .SetFetchMode("Track", FetchMode.Eager)
    .SetMaxResults(1000)
    .SetFirstResult(1)
    .AddOrder(new Order("Id", true))
    .List();

bag 映射（注意 order-by 属性）：

<bag name="Track" inverse="true" lazy="true" batch-size="1000" cascade="none" 
     order-by="TrackNumber ASC">

生成的相关 SQL：

OVER(ORDER BY track2_.TrackNumber, this_.Id) as __hibernate_sort_row

如果我从映射中删除 order-by，那么 SQL 会更改为这样（好多了）：

OVER(ORDER BY this_.Id) as __hibernate_sort_row

所以问题是：有没有办法覆盖或删除映射的 order-by 子句？

原文

I have an entity with a bag collection and I want to page through the entire data set. The collection has an order-by clause. When I try to eager-load the collection, NHibernate is generating SQL that is very very slow because it causes SQL Server to sort on a very non-unique property, and there is a large amount of data in the table.

The code:

var session = NHibernateSessionManager.Instance.GetSession();
session.CreateCriteria<Album>()
    .SetFetchMode("Track", FetchMode.Eager)
    .SetMaxResults(1000)
    .SetFirstResult(1)
    .AddOrder(new Order("Id", true))
    .List();

The bag mapping (note the order-by attribute):

<bag name="Track" inverse="true" lazy="true" batch-size="1000" cascade="none" 
     order-by="TrackNumber ASC">

The relevant SQL generated:

OVER(ORDER BY track2_.TrackNumber, this_.Id) as __hibernate_sort_row

If i remove the order-by from the mapping then the SQL changes to this (much better):

OVER(ORDER BY this_.Id) as __hibernate_sort_row

So the question is: is there a way to override or remove the mapped order-by clause?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淤浪 2024-12-13 19:35:33

经过 Thilak Nathen 和 Firo 的评论后，这个问题的简短答案似乎是：“不，你不能那样做”。长答案是从映射中删除 order-by 属性并根据需要添加它。它存在的原因是它总是需要的，除了这种不寻常的情况。 Firo 建议覆盖工厂配置，我认为这会起作用，因为这是用于报告。对于其他想知道如何操作的人，以下是以编程方式从 xml 映射中删除 order-by 的代码：

Configuration cfg = new Configuration().Configure();
var albumMapping = cfg.ClassMappings.Where(x => x.DiscriminatorValue == "Foo.Album").First();
var trackProperty = albumMapping.GetProperty("Track");
var bagMapping = ((NHibernate.Mapping.Bag)trackProperty.Value);
bagMapping.OrderBy = "";
sessionFactory = cfg.BuildSessionFactory();

After comments from Thilak Nathen and Firo, it looks like the short answer to the question is: "No you can't do that". The long answer is to remove the order-by attribute from the mapping and add it as needed. The reason it was there is that it's always needed, except for this one unusual situation. Firo suggested overriding the factory configuration which I think will work because this is for reporting. For anyone else who wants to know how to do it, here's the code to remove an order-by from an xml mapping programmatically:

Configuration cfg = new Configuration().Configure();
var albumMapping = cfg.ClassMappings.Where(x => x.DiscriminatorValue == "Foo.Album").First();
var trackProperty = albumMapping.GetProperty("Track");
var bagMapping = ((NHibernate.Mapping.Bag)trackProperty.Value);
bagMapping.OrderBy = "";
sessionFactory = cfg.BuildSessionFactory();

回复收藏 0 原文

~没有更多了~