nhibernate3 查询 - 带有集合的集合
我的网页中有一些下拉菜单。它们是相互链接的，并且具有类似的双向链接类结构。
换句话说：Alpha 类有一个 Beta 类列表，而 Beta 类又具有 Charlie 类列表。每个 Beta 类也有自己的 Alpha 列表（它所属的），每个 Charlie 类都有自己的 Beta 列表。
我正在使用 NHibernate 3 以及流畅的 nhibernate 和自动映射。
现在。如果我只是运行 a，
session.CreateCriteria<Alpha>().SetMaxResults(1000).List<Alpha>();
当我循环遍历集合时，我会遇到 N+1 问题。
在我看来，以下 SQL 应该是向数据库查询的所有内容，
select top 1000 * from Alpha
select top 1000 * from Beta
select top 1000 * from Charlie
select * from Alpha2Beta
select * from Beta2Charlie
但是我如何编写查询才能使其工作？
I have a few dropdowns in my webpage. These are linked and have a similar class structure with bi-directional linking.
In other words: class Alpha has a list of class Beta which in turn has a list of class Charlie. Each class Beta also has its own list of Alpha (the ones it belongs to) and each class Charlie has its own list of Beta.
I am using NHibernate 3 with fluent nhibernate and automappings.
Now. If I simply would run a
session.CreateCriteria<Alpha>().SetMaxResults(1000).List<Alpha>();
I get the N+1 problem when I loop over the collections.
The way I see it the following SQL's should be all that's queried to the database
select top 1000 * from Alpha
select top 1000 * from Beta
select top 1000 * from Charlie
select * from Alpha2Beta
select * from Beta2Charlie
But how do I write the query for this to work??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Ayende 在他的博客中展示了一个很好的技巧。我还没有亲自尝试过，因为我决定改变我的 BL 以避免这个问题，所以对此持保留态度。
您应该能够使用 NHibernate Futures 单独加载集合并让 NHibernate 连接实体。由于这不是一个轻松的主题，因此您最好阅读他的博客发布。
There's a nice trick Ayende showed in his blog. I haven't tried it personally as I decided to change my BL to avoid this problem, so take this with a grain of salt.
You should be able to load collections separately and let NHibernate connect entities, using NHibernate Futures. Since it's not a light subject it's better that you read his blog post.
如果您使用 Criteria，则需要包含动态获取 方法调用。
If you're using Criteria you'll need to include Dyanmic Fetching method calls.
据我所知，您无法通过查询级别来帮助解决此问题，就像使用连接获取一样。但是，如果您更改映射并将关联的默认获取模式设置为“子查询”，您可能会感到惊喜：
来自Hibernate 文档（与 NHibernate 同样有效）：
这意味着，当需要第一个关联时，NHibernate 将调用用于获取根实体的查询，而不是加载一个关联，然后加载根实体的所有实例的关联数据 。
也就是说，如果您要加载 1K 个实体，并且您希望每个关联包含多个记录，那么您可能只会从 (SELECT N+1)^ 进行选择 2 到“天哪”我刚刚将整个数据库加载到内存中”。;-)
（请注意，如果您执行此操作并有一个加载 Alpha 列表的场景，但只需要单个 Alpha 的关联 Beta，您仍然需要加载所有但实际上，我发现这是一种非常罕见的情况，因此通常 subselect fetch 非常适合我。）
As far as I know, there's no way you can help this on a query by query level, like you can with join fetching. However, if you change the mappings and set the default fetch mode for the associations to be "subquery", you might be pleasantly surprised:
From the Hibernate Documentation (works equally well with NHibernate):
What this means is that when the first association is required, NHibernate will, instead of loading one association, recall the query you used to get the root entity, then load the association data for all instances of the root entity type that were returned by the query.
That said, if you're loading 1K entities and you expect the associations to have more than a couple of records each, you're probably just going to go from a (SELECT N+1)^2 to a "holy crap I just loaded the entire database into memory". ;-)
(Note that if you do this and have a scenario where you load the Alpha list, but only need the associated Betas for a single Alpha, you're still going to load all of them and there's nothing you can do about that. In practice though, I've found this to be a very rare scenario, so usually subselect fetch suits me very well.)