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;}
}

OrderItems

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.

发出的SQL查询是传统的左外联接查询，前面是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中的一个bug，但是由于联接获取很少见(而且我认为实际上在网络上为每个Item行的一部分拖动相同的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，而无需将其拖入一个具体的List中(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.

就像我说的那样，我认为您正在使用join fetching伤害自己.每行都包含客户数据和订单数据，并连接到每个不同的行.那是很多冗余数据，我认为这比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屋！