如何使此SelectMany使用Join? [英] How can I make this SelectMany use a Join?

问题描述

鉴于我在Linq To Sql模型中有三个表(Customer，Orders和OrderLines)，其中

Given that I have three tables (Customer, Orders, and OrderLines) in a Linq To Sql model where

客户-一对多->订单-一对多->订单行

Customer -- One to Many -> Orders -- One to Many -> OrderLines

当我使用

var customer = Customers.First();
var manyWay = from o in customer.CustomerOrders
              from l in o.OrderLines
              select l;

我看到一个查询吸引了客户，这是有道理的.然后，我看到一个查询客户订单的查询，然后是每个订单的单个查询以获取订单行，而不是将两者合并.总共n +1个查询(不算获得客户)

I see one query getting the customer, that makes sense. Then I see a query for the customer's orders and then a single query for each order getting the order lines, rather than joining the two. Total of n + 1 queries (not counting getting customer)

但是如果我使用

var tableWay = from o in Orders
               from l in OrderLines
               where o.Customer == customer
               && l.Order == o
               select l;

然后，我没有看到针对每个获得订单行的订单的查询，而是看到了将两个表连接在一起的查询.总共1个查询(不算获得客户)

Then instead of seeing a single query for each order getting the order lines, I see a single query joining the two tables. Total of 1 query (not counting getting customer)

我更喜欢使用第一个Linq查询，因为它对我来说更具可读性，但是为什么L2S不能像我在第一个查询中期望的那样加入表?使用LINQPad，我看到第二个查询正在被编译为SelectMany，尽管我看不到第一个查询没有任何变化，但不确定这是否表明我的查询中存在某些问题.

I would prefer to use the first Linq query as it seems more readable to me, but why isn't L2S joining the tables as I would expect in the first query? Using LINQPad I see that the second query is being compiled into a SelectMany, though I see no alteration to the first query, not sure if that's a indicator to some problem in my query.

推荐答案

我认为这里的关键是

customer.CustomerOrders

那是EntitySet，而不是IQueryable，因此您的第一个查询不会直接转换为SQL查询.相反，它将解释为许多查询，每个查询一个.

Thats an EntitySet, not an IQueryable, so your first query doesn't translate directly into a SQL query. Instead, it is interpreted as many queries, one for each Order.

那是我的猜测.

这篇关于如何使此SelectMany使用Join?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！