LINQ生成嵌套/子查询

Question

我正在使用Asp.NET&实体框架与SQL Server作为数据库，不知何故，我得到了这个奇怪的问题

我有这个代码：

var pricingInfo = (from price in invDB.Pricing.AsNoTracking()
join priceD in invDB.PricingDetail.AsNoTracking() on price.PricingId equals priceDtl.PricingId
join tagD in invDB.PricingTagDetail.AsNoTracking() on priceDtl.PricingDetailId equals tagDtl.PricingDetailId
join it in invDB.Item.AsNoTracking() on tagDtl.ItemId equals item.ItemId
join par in invDB.Party.AsNoTracking() on tagDtl.PartyId equals party.PartyId
join b in invDB.Brand.AsNoTracking() on tagDtl.BrandId equals brd.BrandId into t from brand in t.DefaultIfEmpty()
where tagDtl.AvailableQuantity > 0m && price.PricingNo == printNumber
select new
{
TagNo = tagDtl.TagNo,
SellingRate = tagDtl.SellingRate,
Quantity = tagDtl.AvailableQuantity ?? 0m,
ItemCode = item.Name,
UOMId = priceDtl.UOMId,
Brand = brand.BrandCode,
Supplier = party.PartyCode,
Offer = tagDtl.Offer
}).ToList();

它生成下面的带有子查询的sql查询，没有where条件，并且从大量数据中提取完整记录。这会导致大量内存消耗和性能问题。

SELECT
[Filter1].[PricingId1] AS [PricingId],
[Filter1].[TagNo] AS [TagNo],
[Filter1].[SellingRate1] AS [SellingRate],
CASE WHEN ([Filter1].[AvailableQuantity] IS NULL) THEN cast(0 as decimal(18)) ELSE [Filter1].[AvailableQuantity] END AS [C1],
[Filter1].[Name] AS [Name],
[Filter1].[UOMId 1] AS [UOMId ],
[Extent6].[BrandCode] AS [BrandCode],
[Filter1].[PartyCode] AS [PartyCode],
[Filter1].[Offer] AS [Offer]
FROM  
(
SELECT [Extent1].[PricingId] AS [PricingId1], [Extent1].[PricingNo] AS [PricingNo], [Extent2].[UnitOfMeasurementId] AS [UnitOfMeasurementId1], [Extent3].[TagNo] AS [TagNo], [Extent3].[BrandId] AS [BrandId1], [Extent3].[SellingRate] AS [SellingRate1], [Extent3].[AvailableQuantity] AS [AvailableQuantity], [Extent3].[Offer] AS [Offer], [Extent4].[Name] AS [Name], [Extent5].[PartyCode] AS [PartyCode]
FROM     [PanERP].[Pricing] AS [Extent1]
INNER JOIN [PanERP].[PricingDetail] AS [Extent2] ON [Extent1].[PricingId] = [Extent2].[PricingId]
INNER JOIN [PanERP].[PricingTagDetail] AS [Extent3] ON [Extent2].[PricingDetailId] = [Extent3].[PricingDetailId]
INNER JOIN [PanERP].[Item] AS [Extent4] ON [Extent3].[ItemId] = [Extent4].[ItemId]
INNER JOIN [PanERP].[Party] AS [Extent5] ON [Extent3].[PartyId] = [Extent5].[PartyId]
WHERE [Extent3].[AvailableQuantity] > cast(0 as decimal(18))
) AS [Filter1]
LEFT OUTER JOIN [PanERP].[Brand] AS [Extent6] ON [Filter1].[BrandId1] = [Extent6].[BrandId]
WHERE ([Filter1].[PricingNo] = @p__linq__0) OR (([Filter1].[PricingNo] IS NULL) AND (@p__linq__0 IS NULL))

但是当我改变条件

其中tagDtl.AvailableQuantity>0m

作为一个变量，它创建了另一个没有嵌套select语句的SQL查询。

这是修改后的代码

decimal availableQuantity = 0m;
var pricingInfo = (from price in invDB.Pricing.AsNoTracking()
join priceD in invDB.PricingDetail.AsNoTracking() on price.PricingId equals priceDtl.PricingId
join tagD in invDB.PricingTagDetail.AsNoTracking() on priceDtl.PricingDetailId equals tagDtl.PricingDetailId
join it in invDB.Item.AsNoTracking() on tagDtl.ItemId equals item.ItemId
join par in invDB.Party.AsNoTracking() on tagDtl.PartyId equals party.PartyId
join b in invDB.Brand.AsNoTracking() on tagDtl.BrandId equals brd.BrandId into t from brand in t.DefaultIfEmpty()
where tagDtl.AvailableQuantity > availableQuantity && price.PricingNo == printNumber
select new
{
TagNo = tagDtl.TagNo,
SellingRate = tagDtl.SellingRate,
Quantity = tagDtl.AvailableQuantity ?? availableQuantity,
ItemCode = item.Name,
UOMId = priceDtl.UOMId,
Brand = brand.BrandCode,
Supplier = party.PartyCode,
Offer = tagDtl.Offer
}).ToList();

这里是没有嵌套SQL语句的SQL查询。

SELECT
[Extent1].[PricingId] AS [PricingId],
[Extent3].[TagNo] AS [TagNo],
[Extent3].[SellingRate] AS [SellingRate],
CASE WHEN ([Extent3].[AvailableQuantity] IS NULL) THEN cast(0 as decimal(18)) ELSE [Extent3].[AvailableQuantity] END AS [C1],
[Extent4].[Name] AS [Name],
[Extent2].[UOMId ] AS [UOMId ],
[Extent6].[BrandCode] AS [BrandCode],
[Extent5].[PartyCode] AS [PartyCode],
[Extent3].[Offer] AS [Offer]
FROM      [PanERP].[Pricing] AS [Extent1]
INNER JOIN [PanERP].[PricingDetail] AS [Extent2] ON [Extent1].[PricingId] = [Extent2].[PricingId]
INNER JOIN [PanERP].[PricingTagDetail] AS [Extent3] ON [Extent2].[PricingDetailId] = [Extent3].[PricingDetailId]
INNER JOIN [PanERP].[Item] AS [Extent4] ON [Extent3].[ItemId] = [Extent4].[ItemId]
INNER JOIN [PanERP].[Party] AS [Extent5] ON [Extent3].[PartyId] = [Extent5].[PartyId]
LEFT OUTER JOIN [PanERP].[Brand] AS [Extent6] ON [Extent3].[BrandId] = [Extent6].[BrandId]
WHERE ([Extent3].[AvailableQuantity] > @p__linq__0) AND (([Extent1].[PricingNo] = @p__linq__1) OR (([Extent1].[PricingNo] IS NULL) AND (@p__linq__1 IS NULL)))

如果我将where条件作为lambda表达式移动到模型定义中，就像这个

来自库存中的价格Db.Pricing.AsNoTracking()。其中(c=>c.PricingNo==printNumber))

那么它也可以正常工作。

为什么LINQ生成嵌套的Select？我们如何才能避免这种情况？

提前感谢您的回答。

Answer 1

好吧，我想你已经回答了自己的问题，关于你的评论。我只是想澄清一下发生了什么。

当您使用硬编码的常量(如0m)时，框架会将其转换为SQL，并将其值保持为常量：

WHERE [Extent3].[AvailableQuantity] > cast(0 as decimal(18))

当您使用局部变量(如"available Quantity")时，框架会创建一个参数：

([Extent3].[AvailableQuantity] > @p__linq__0)

我可能错了，但正如我所看到的，这样做是为了在编写代码时保持程序员的目标(常量=常量，变量=参数)。

那么子查询呢

这是一个查询优化逻辑(至少在这种情况下，可能是一个糟糕的逻辑)。当您使用参数进行查询时，您可能会多次运行它，但SQL Server将始终使用相同的执行计划，从而使查询更快；当您使用常量时，每个查询都需要重新评估(如果您检查SQL Server活动监视器，您将看到带参数的查询被视为同一查询，而不管参数值如何)。

这样，在我看来(很抱歉，我找不到任何关于它的文档)，实体框架正在尝试隔离查询；外部/通用的使用参数，内部/特定的使用常量

如果有人能补充一些微软关于这个主题的文档，我会很高兴…