NHibernate:为什么 Linq First() 只使用 FetchMany() 强制所有子和孙集合中的一个项目 [英] NHibernate: Why does Linq First() force only one item in all child and grandchild collections with FetchMany()

问题描述

我有一个 Customer 的规范域，有很多 Orders，每个 Order 有很多 OrderItems>:

I've got a canonical Domain of a Customer with many Orders, with each Order having many OrderItems:

public class Customer
{
  public Customer()
  {
    Orders = new HashSet<Order>();
  }
  public virtual int Id {get;set;}
  public virtual ICollection<Order> Orders {get;set;}
}

订购

public class Order
{
  public Order()
  {
    Items = new HashSet<OrderItem>();
  }
  public virtual int Id {get;set;}
  public virtual Customer Customer {get;set;}
}

订单项

public class OrderItem
{
  public virtual int Id {get;set;}
  public virtual Order Order {get;set;}
}

问题

无论是使用 FluentNHibernate 还是 hbm 文件映射，我都会运行两个单独的查询，它们的 Fetch() 语法是相同的，除了一个包含 .First() 扩展方法.

Problem

Whether mapped with FluentNHibernate or hbm files, I run two separate queries, that are identical in their Fetch() syntax, with the exception of one including the .First() extension method.

返回预期结果:

var customer = this.generator.Session.Query<Customer>()
    .Where(c => c.CustomerID == id)
    .FetchMany(c => c.Orders)
    .ThenFetchMany(o => o.Items).ToList()[0];

只返回每个集合中的一个项目:

var customer = this.generator.Session.Query<Customer>()
    .Where(c => c.CustomerID == id)
    .FetchMany(c => c.Orders)
    .ThenFetchMany(o => o.Items).First();

我想我明白这里发生了什么，即 .First() 方法被应用于前面的每个语句，而不仅仅是最初的 .Where() 子句.鉴于 First() 正在返回客户，这对我来说似乎是不正确的行为.

I think I understand what's going on here, which is that the .First() method is being applied to each of the preceding statements, rather than just to the initial .Where() clause. This seems incorrect behavior to me, given the fact that First() is returning a Customer.

编辑 2011-06-17

经过进一步的研究和思考，我相信根据我的映射，这个方法链有两个结果:

After further research and thinking, I believe that depending on my mapping, there are two outcomes to this Method Chain:

    .Where(c => c.CustomerID == id)
    .FetchMany(c => c.Orders)
    .ThenFetchMany(o => o.Items);

注意:我认为我无法获得子选择行为，因为我没有使用 HQL.

NOTE: I don't think I can get subselect behavior, since I'm not using HQL.

1. 当映射为 fetch="join" 时，我应该在 Customer、Order 和 OrderItem 表之间得到笛卡尔积.
2. 当映射为 fetch="select" 时，我应该获取 Customer 的查询，然后分别获取 Orders 和 OrderItems 的多个查询.

1. When the mapping is fetch="join" I should get a cartesian product between the Customer, Order and OrderItem tables.
2. When the mapping is fetch="select" I should get a query for Customer, and then multiple queries each for Orders and OrderItems.

如何将 First() 方法添加到链中是我无法跟踪应该发生什么的地方.

How this plays out with adding the First() method to the chain is where I lose track of what should be happening.

get 发出的 SQL Query 是传统的左外连接查询，前面有 select top (@p0).

The SQL Query that get's issued is the traditional left-outer-join query, with select top (@p0) in front.

推荐答案

First() 方法被翻译成 SQL(至少是 T-SQL)作为 SELECT TOP 1 ....结合您的连接获取，这将返回一行，其中包含一个客户、该客户的一个订单和该订单的一个项目.您可能认为这是 Linq2NHibernate 中的一个错误，但由于连接获取很少见(我认为您实际上损害了性能，将相同的 Customer 和 Order 字段值作为每个项目的行的一部分跨网络拉入网络)我怀疑团队会修复它.

The First() method is translated into SQL (T-SQL at least) as SELECT TOP 1 .... Combined with your join fetching, this will return a single row, containing one customer, one order for that customer and one item for the order. You might consider this a bug in Linq2NHibernate, but as join fetching is rare (and I think you're actually hurting your performance pulling the same Customer and Order field values across the network as part of the row for each Item) I doubt the team will fix it.

您需要的是单个客户，然后是该客户的所有订单以及所有这些订单的所有项目.这是通过让 NHibernate 运行 SQL 来实现的，该 SQL 将拉出一个完整的 Customer 记录(这将是每个 Order Line 的一行)并构建 Customer 对象图.将 Enumerable 转换为 List 然后获取第一个元素有效，但以下会稍微快一点:

What you want is a single Customer, then all Orders for that customer and all Items for all those Orders. That happens by letting NHibernate run SQL that will pull one full Customer record (which will be a row for each Order Line) and construct the Customer object graph. Turning the Enumerable into a List and then getting the first element works, but the following will be slightly faster:

var customer = this.generator.Session.Query<Customer>()
    .Where(c => c.CustomerID == id)
    .FetchMany(c => c.Orders)
    .ThenFetchMany(o => o.Items)
    .AsEnumerable().First();

AsEnumerable() 函数强制对由 Query 创建并用其他方法修改的 IQueryable 进行评估，吐出一个内存中的 Enumerable，而不会将它吞入具体的列表中(如果愿意，NHibernate 可以简单地提取足够的信息从 DataReader 中创建一个完整的顶级实例).现在，First() 方法不再应用于 IQueryable 以转换为 SQL，而是应用于对象图的内存中 Enumerable，在 NHibernate 完成它的事情之后，并给出您的 Where 子句，应该是零个或一个带有水合订单集合的客户记录.

the AsEnumerable() function forces evaluation of the IQueryable created by Query and modified with the other methods, spitting out an in-memory Enumerable, without slurping it into a concrete List (NHibernate can, if it wishes, simply pull enough info out of the DataReader to create one full top-level instance). Now, the First() method is no longer applied to the IQueryable to be translated to SQL, but it is instead applied to an in-memory Enumerable of the object graphs, which after NHibernate has done its thing, and given your Where clause, should be zero or one Customer record with a hydrated Orders collection.

就像我说的，我认为您使用连接获取是在伤害自己.每行包含客户的数据和订单的数据，连接到每个不同的行.这是大量的冗余数据，我认为这将比 N+1 查询策略花费更多.

Like I said, I think you're hurting yourself using join fetching. Each row contains the data for the Customer and the data for the Order, joined to each distinct Line. That is a LOT of redundant data, which I think will cost you more than even an N+1 query strategy.

我能想到的最好的处理方法是每个对象一个查询来检索该对象的子对象.它看起来像这样:

The best way I can think of to handle this is one query per object to retrieve that object's children. It would look like this:

var session = this.generator.Session;
var customer = session.Query<Customer>()
        .Where(c => c.CustomerID == id).First();

customer.Orders = session.Query<Order>().Where(o=>o.CustomerID = id).ToList();

foreach(var order in customer.Orders)
   order.Items = session.Query<Item>().Where(i=>i.OrderID = order.OrderID).ToList();

这需要对每个订单进行查询，加上客户级别的两个查询，并且不会返回重复数据.这将比单个查询返回包含 Customer 和 Order 的每个字段以及每个 Item 的行的性能要好得多，也比发送每个 Item 的查询加上每个 Order 的查询以及针对 Customer 的查询要好得多.

This requires a query for each Order, plus two at the Customer level, and will return no duplicate data. This will perform far better than a single query returning a row containing every field of the Customer and Order along with each Item, and also better than sending a query per Item plus a query per Order plus a query for the Customer.

这篇关于NHibernate:为什么 Linq First() 只使用 FetchMany() 强制所有子和孙集合中的一个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！