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 字段值通过网络作为每个 Item 的行的一部分)我怀疑团队会解决的.

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 将提取一个完整的客户记录(每个订单行将是一行)并构造客户对象图.将 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() 方法不再应用于要转换为 SQL 的 IQueryable，而是应用于对象图的内存中 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屋！