奇怪的LINQ选择行为 [英] Strange linq selection behavior

问题描述

我希望有人可以对此事发表一些看法，也许一点都不奇怪.

这将导致查询选择购买"中的每一列，而不仅仅是ID和名称.

Hi,

I am hoping somebody can shine some light on this matter, maybe it''s not strange at all.

This will cause the query to select every column inside Purchase not just Id and Name.

foreach (Customer c in db.Customers.Where(x => x.Active.Equals(true)))
{
  foreach (Purchase p in c.Purchases.Select(x => new { i.Id, i.Name }))
  {
    Log(string.format("{0} {1}", p.Id, i.Name));
  }
}

此查询将仅选择ID和正确命名

This query will only select Id and name properly

foreach (Customer c in db.Customers.Where(x => x.Active.Equals(true)))
{
  foreach (Purchase p in db.Purchases.Where(x => x.Customer.Equals(c))
  .Select(x => new { i.Id, i.Name }))
  {
    Log(string.format("{0} {1}", p.Id, i.Name));
  }
}

推荐答案

当您对数据库上下文中的某项进行查询时，该查询是在您调用Linq方法时构造的，包括在何处并选择.因此，您最终得到一个查询，例如选择ID，客户名称为{ID.{c.ID}的购买中的名称"".

在第二种情况下，您需要c.Purchases，它大概被声明为List< Purchase>.或您的域对象中的类似内容.请求该属性将导致查询由于延迟加载而触发查询，以获取该客户的所有购买商品:类似于"select * from customerID = {c.ID}的购买商品"(如果不是*，则是所有列需要完全填充购买"对象).然后，Select将在内存对象(即Linq to Object，而不是Linq to SQL)上运行.

When you''re doing a query on something from a database context, the query is constructed as you call Linq methods on it, including Where and Select. So you end up with a query like "select id, name from purchases where customerID={c.ID}".

In the second case, you''re asking for c.Purchases, which is presumably declared as a List<Purchase> or similar in your domain object. Requesting that property will cause a query to fire to get all the purchases for that customer, due to lazy loading: something like "select * from purchases where customerID={c.ID}" (or, if not *, then all the columns needed to fully populate a Purchase object). The Select is then running on an in memory object (i.e. Linq to Objects, not Linq to SQL).

您应避免像这样的foreach循环，因为它将导致执行更多查询，无法从SQL和LINQ提取更多数据，无法优化查询.
如果您这样写:

You should avoid foreach loops like this, it will cause more queries to be executed, more data to be fetched from SQL and LINQ can''t optimize queries.
If you write it like this:

from p in db.Purchases where p.Customer.Active.Equals(true) select new {p.Id, p.Name}

这将是一个仅获取两个必需列的查询.

it will be a single query fetching only two required columns.

我会这样做:

i would do :

var query = from p in db.Purchases
            join c in db.Customers on p.Customer.Id equals c.Id
            where c.Active.Equals(true)
            select new { PurchaseId = p.Id, PurchaseName = p.Name };

// concretize
var list = query.ToList();

list.foreach(i=> Log(string.format("{0} {1}", i.PurchaseId, i.PurchaseName )));

这篇关于奇怪的LINQ选择行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！