EFCore返回太多列以进行简单的LEFT OUTER连接 [英] EFCore returning too many columns for a simple LEFT OUTER join
问题描述
我目前正在SQL Server上使用EFCore 1.1（预览版）。
I am currently using EFCore 1.1 (preview release) with SQL Server.
我正在做我认为是 OrderItem 表。
I am doing what I thought was a simple OUTER JOIN between an Order and OrderItem table.
var orders = from order in ctx.Order
join orderItem in ctx.OrderItem
on order.OrderId equals orderItem.OrderId into tmp
from oi in tmp.DefaultIfEmpty()
select new
{
order.OrderDt,
Sku = (oi == null) ? null : oi.Sku,
Qty = (oi == null) ? (int?) null : oi.Qty
};
返回的实际数据是正确的（我知道早期版本的OUTER JOINS根本不起作用） 。但是，SQL太可怕了，它包括 Order 和 OrderItem 中的每一列，考虑到其中之一是大型XML，这是有问题的Blob。
The actual data returned is correct (I know earlier versions had issues with OUTER JOINS not working at all). However the SQL is horrible and includes every column in Order and OrderItem which is problematic considering one of them is a large XML Blob.
SELECT [order]。[OrderId]，[order]。[OrderStatusTypeId]，
[order]。[ OrderSummary]，[order]。[OrderTotal]，[order]。[OrderTypeId]，
[order]。[ParentFSPId]，[order]。[ParentOrderId]，
[order]。[PayPalECToken] ，[order]。[PaymentFailureTypeId] ....
SELECT [order].[OrderId], [order].[OrderStatusTypeId], [order].[OrderSummary], [order].[OrderTotal], [order].[OrderTypeId], [order].[ParentFSPId], [order].[ParentOrderId], [order].[PayPalECToken], [order].[PaymentFailureTypeId] ....
... [orderItem]。[OrderId]，[orderItem]。[OrderItemType]，[orderItem]。 [数量]，
[orderItem]。[SKU]来自[Order] AS [order]左联接[OrderItem] AS
[orderItem] ON [order]。[OrderId] = [orderItem]。[ OrderId] ORDER BY
[order]。[OrderId]
...[orderItem].[OrderId], [orderItem].[OrderItemType], [orderItem].[Qty], [orderItem].[SKU] FROM [Order] AS [order] LEFT JOIN [OrderItem] AS [orderItem] ON [order].[OrderId] = [orderItem].[OrderId] ORDER BY [order].[OrderId]
（还有许多未显示的列。）
另一方面-如果我将其设置为INNER JOIN，则该SQL仅符合我的select子句中的列要求：
On the other hand - if I make it an INNER JOIN then the SQL is as expected with only the columns in my select clause:
SELEC T [order]。[OrderDt]，[orderItem]。[SKU]，[orderItem]。[数量]从
[订单] AS [订单]内联接[OrderItem] AS [orderItem] ON
[order]。[OrderId] = [orderItem]。[OrderId]
SELECT [order].[OrderDt], [orderItem].[SKU], [orderItem].[Qty] FROM [Order] AS [order] INNER JOIN [OrderItem] AS [orderItem] ON [order].[OrderId] = [orderItem].[OrderId]
我尝试恢复到EFCore 1.01，但遇到了一些麻烦
I tried reverting to EFCore 1.01, but got some horrible nuget package errors and gave up with that.
不清楚这是一个实际的回归问题还是EFCore中的不完整功能。
Not clear whether this is an actual regression issue or an incomplete feature in EFCore. But couldn't find any further information about this elsewhere.
编辑：EFCore 2.1解决了很多分组问题还有N + 1类型问题，其中对每个子实体进行单独的查询。实际上，其性能给人留下了深刻的印象。
EFCore 2.1 has addressed a lot of issues with grouping and also N+1 type issues where a separate query is made for every child entity. Very impressed with the performance in fact.
3/14/18-不建议使用EFCore的预览版1，因为GROUP BY SQL在使用OrderBy（）时会出现一些问题，但已在每晚的版本中进行了修复，并且预览版2。
3/14/18 - 2.1 Preview 1 of EFCore isn't recommended because the GROUP BY SQL has some issues when using OrderBy() but it's fixed in nightly builds and Preview 2.
推荐答案
以下内容适用于EF Core 1.1.0（发行版）。
The following applies to EF Core 1.1.0 (release).
尽管不应该这样做，但尝试了几种替代的语法查询（使用导航属性而不是手动联接，使用 let /中间 Select ，使用 Concat / Union 进行仿真左联接，替代的左联接语法等。）结果-与帖子中的结果相同，和/或执行多个查询，和/或无效的SQL查询，和/或奇怪的运行时异常，例如 IndexOutOfRange ， InvalidArgument 等。
Although shouldn't be doing such things, tried several alternative syntax queries (using navigation property instead of manual join, joining subqueries containing anonymous type projection, using let / intermediate Select, using Concat / Union to emulate left join, alternative left join syntax etc.) The result - either the same as in the post, and/or executing more than one query, and/or invalid SQL queries, and/or strange runtime exceptions like IndexOutOfRange, InvalidArgument etc.
根据测试我可以说的是问题可能与错误有关（回归，执行不完整） -真的很重要）在 GroupJoin 翻译中。例如，＃7003：为带有组联接的查询生成的错误SQL在子查询中不存在最终投影或＃6647-左联接（GroupJoin）总是物化元素，从而导致不必要的数据提取等。
What I can say based on tests is that most likely the problem is related to bug(s) (regression, incomplete implementation - does it really matter) in GroupJoin translation. For instance, #7003: Wrong SQL generated for query with group join on a subquery that is not present in the final projection or #6647 - Left Join (GroupJoin) always materializes elements resulting in unnecessary data pulling etc.
直到它被修复（何时？），作为一种（远非完美的）解决方法，我建议使用其他左外部联接语法（从A中的A到B中的b.Where（b = b.Key == a.Key）.DefaultIfEmpty（））：
Until it get fixed (when?), as a (far from perfect) workaround I could suggest using the alternative left outer join syntax (from a in A from b in B.Where(b = b.Key == a.Key).DefaultIfEmpty()):
var orders = from o in ctx.Order
from oi in ctx.OrderItem.Where(oi => oi.OrderId == o.OrderId).DefaultIfEmpty()
select new
{
OrderDt = o.OrderDt,
Sku = oi.Sku,
Qty = (int?)oi.Qty
};
会产生以下SQL：
SELECT [o].[OrderDt], [t1].[Sku], [t1].[Qty]
FROM [Order] AS [o]
CROSS APPLY (
SELECT [t0].*
FROM (
SELECT NULL AS [empty]
) AS [empty0]
LEFT JOIN (
SELECT [oi0].*
FROM [OrderItem] AS [oi0]
WHERE [oi0].[OrderId] = [o].[OrderId]
) AS [t0] ON 1 = 1
) AS [t1]
如您所见，投影效果还可以，但不是左联接它使用奇怪的 CROSS APPLY ，这可能会引入另一个性能问题。
As you can see, the projection is ok, but instead of LEFT JOIN it uses strange CROSS APPLY which might introduce another performance issue.
还请注意，访问正确的联接表时，必须对值类型使用强制类型转换，而对于字符串则不使用任何类型，如上所示。如果您像在原始查询中一样使用 null 检查，则在运行时将获得 ArgumentNullException （还有另一个错误）。
Also note that you have to use casts for value types and nothing for strings when accessing the right joined table as shown above. If you use null checks as in the original query, you'll get ArgumentNullException at runtime (yet another bug).
这篇关于EFCore返回太多列以进行简单的LEFT OUTER连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
