使用 nhibernate （和 queryover）急切地获取多个嵌套关联
我有一个包含多个嵌套关联的数据库。基本上，结构如下：
Order -> OrderItem -> OrderItemPlaylist -> OrderPlaylistItem -> Track -> Artist
我需要根据特定日期销售的所有订单生成一份报告，该报告需要遍历所有提到的关联才能生成所需的信息。
尝试将所有表连接在一起将是一种矫枉过正，因为考虑到它将 6 个表连接在一起，这会导致非常大的笛卡尔连接和许多冗余数据。代码如下：
q.Left.JoinQueryOver<OrderItem>(order => order.OrderItems)
.Left.JoinQueryOver<OrderItemPlaylist>(orderItem => orderItem.Playlist)
.Left.JoinQueryOver<OrderItemPlaylistItem>(orderItemPlaylist => orderItemPlaylist.PlaylistItems)
.Left.JoinQueryOver<Track>(orderItemPlaylistItem => orderItemPlaylistItem.Track)
.Left.JoinQueryOver<Artist>(track => track.Artist)
上面的代码有效，但是即使有几个订单，每个订单都有几个订单项目，并且每个播放列表都包含多个曲目，结果也会爆炸到数千条记录，并且随着每个额外的订单呈指数级增长。
知道什么是最好和最有效的方法吗？我目前尝试启用批量加载，这大大减少了数据库查询的数量，但在我看来仍然不是一个好的方法，而更像是一种“简单的解决方法”。
由于数据量巨大，无需在一次 SQL 查询中加载所有数据。我想每个关联一个 SQL 查询就很完美了。理想情况下，首先获取所有订单，然后获取该订单的所有订单项目并将它们加载到关联的集合中，然后加载每个订单项目的播放列表，依此类推。
此外，这不必专门在 QueryOver 中进行，因为我可以访问 .RootCriteria 并使用 Criteria API。
任何帮助将不胜感激！
I have a database which has multiple nested associates. Basically, the structure is as follows:
Order -> OrderItem -> OrderItemPlaylist -> OrderPlaylistItem -> Track -> Artist
I need to generate a report based on all orders sold in a certain date, which needs to traverse into ALL the mentioned associations in order to generate the required information.
Trying to join all tables together would be an overkill, as it would result in an extremely large cartesian join with many redundant data, considering it would be joining 6 tables together. Code below:
q.Left.JoinQueryOver<OrderItem>(order => order.OrderItems)
.Left.JoinQueryOver<OrderItemPlaylist>(orderItem => orderItem.Playlist)
.Left.JoinQueryOver<OrderItemPlaylistItem>(orderItemPlaylist => orderItemPlaylist.PlaylistItems)
.Left.JoinQueryOver<Track>(orderItemPlaylistItem => orderItemPlaylistItem.Track)
.Left.JoinQueryOver<Artist>(track => track.Artist)
The above works, but with even a few orders, each with a few order items, and a playlist each consisting of multiple tracks, the results would explode to thousand records, growing exponentially with each extra order.
Any idea what would be the best and most efficient approach? I've currently tried enabling batch-loading, which greatly scales down the number of database queries but still does not seem to me like a good approach, but more like an 'easy-workaround'.
There is no need for all the data to be loaded in just one SQL query, given the huge amount of data. One SQL query for each association would be perfect I guess. Ideally it would be something where first you get all orders, then you get all the order items for the order and load them in the associated collections, then the playlists for each order item, so on and so forth.
Also, this doesn't have to be specifically in QueryOver, as I can access the .RootCriteria and use the Criteria API.
Any help would be greatly appreciated !
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我相信这就是您正在寻找的
http:// ayende.com/blog/4367/eagerly-loading-entity-associations-efficiently-with-nhibernate
I believe this is what you are looking for
http://ayende.com/blog/4367/eagerly-loading-entity-associations-efficiently-with-nhibernate
如果您更喜欢一个 SQL 查询，您希望它生成什么 SQL 语法？我想如果您要进行一个 SQL 查询，您就无法避免一长串
JOIN。我想我要做的是使用多个查询逐级获取实体。
If you prefer one SQL query, what SQL syntax would you expect this to produce? I guess you can't avoid a long sequence of
JOINs if you're going for one SQL query.I guess what I would do is get the entities level by level, using several queries.
您可能应该首先在 SQL 中尽可能定义查询，然后查看执行计划以找到最佳方法（以及索引是否足够）。
那时，您知道自己要做什么，然后就可以相当容易地尝试在 HQL 或 QueryOver 甚至 LINQ 中编写查询代码，并使用 NHibernate 中的 SQL 编写器或出色的 NHProfiler http://www.nhprof.com。
你可能是对的，最终会出现几个查询。通过使用 Criteria 或 QueryOver 中的“Future”命令，将尽可能多的（彼此不依赖的）批处理为单个行程，从而加快速度。您可以在这里阅读更多相关信息： http://ayende.com/blog/3979/nhibernate-futures
You should probably start off by defining the query as best you can in SQL, and looking at the execution plans to find the very best method (and whether your indexes are sufficiant).
At that point you know what you're shooting for, and then it's reasonably easy to try and code the query in HQL or QueryOver or even LINQ and check the results using the SQL writer in NHibernate, or the excellent NHProfiler http://www.nhprof.com.
You are probably right about ending up with several queries. Speed them up by batching as many as you can (that do not depend on each other) into single trips by using the "Future" command in Criteria or QueryOver. You can read more about that here: http://ayende.com/blog/3979/nhibernate-futures