与3.5相比，4.0中的Linq查询具有不同的行为(让关键字问题?)

Question

在这里输入代码'我有几个使用'let'关键字的Linq查询。它们在3.5中工作得很好，但在4.0中我看到一个非常奇怪的行为。

的例子:

 var query1 =
            (from xVehicleOption in this.entities.VehicleOptionSet
                let regionPriceAdjustment = (from xOptionRegionPriceAdjustmentLeftOuter in this.entities.OptionRegionPriceAdjustmentSet
                                            where xOptionRegionPriceAdjustmentLeftOuter.VehicleTypeRegionId == this.vehicleTypeRegionId
                                            && xOptionRegionPriceAdjustmentLeftOuter.VehicleId == this.vehicleId
                                            && xOptionRegionPriceAdjustmentLeftOuter.VehicleOptionId == xVehicleOption.VehicleOptionId
                                            select new
                                            {
                                                xOptionRegionPriceAdjustmentLeftOuter,
                                                xOptionRegionPriceAdjustmentLeftOuter.xPriceType
                                            })
                where xVehicleOption.VehicleId == this.vehicleId
                && (xVehicleOption.OptionTypeId == 5 || xVehicleOption.OptionTypeId == 7)
                select new
                {
                    Name = xVehicleOption.DisplayName,
                    VehicleOptionId = xVehicleOption.VehicleOptionId,
                    SortOrder = xVehicleOption.SortOrder,
                    OptionAvailability = xVehicleOption.OptionAvailabilityDisplayName,
                    IsDefaultConfiguration = xVehicleOption.IsDefaultConfiguration,
                    OptionType = xVehicleOption.OptionTypeDisplayName,
                    RegionPriceAdjustment = regionPriceAdjustment
                }).OrderBy(o => o.SortOrder);

允许枚举两个嵌套循环。一个用于每个选项，一个用于一个选项内的每个区域调整。

foreach (var bookOption in query)
{
    .......    
    foreach (var regionAdjustment in bookOption.RegionPriceAdjustment)    
    {    
         .......    
    }    
}

4.0中的问题是生成的SQL是一个没有任何WHERE子句的SELECT FROM RegionPriceAdjustment表(用分析器跟踪)。这意味着命令超时(无论如何都是错误的，因为没有查询的其他部分的迹象)。

我试着用。include代替"let"，这可以在懒惰加载的情况下工作，但它会为每个PriceType生成一个动态sql语句(有8个，所以效率不是很高)。我曾试图禁用延迟加载，但这肯定会给我一个错误，因为PriceType没有被加载。我曾尝试使用两个select分隔成，但我得到相同的问题。

顺便说一句，在4.0中，我使用POCOs和EntityFramework DLL与DbContext对象。我没有尝试过ObjectContext，但这应该不会有任何区别，因为DbContext围绕着ObjectContext。

知道为什么会这样吗?有什么建议吗?

谢谢,伊夫

编辑:

下面是3.5版本的SQL:

[Project2].[VehicleOptionId] AS [VehicleOptionId],  
[Project2].[DisplayName] AS [DisplayName],  
[Project2].[OptionAvailabilityDisplayName] AS [OptionAvailabilityDisplayName],  
[Project2].[IsDefaultConfiguration] AS [IsDefaultConfiguration],  
[Project2].[SortOrder] AS [SortOrder],  
[Project2].[VehicleOptionId1] AS [VehicleOptionId1],  
[Project2].[CategoryId] AS [CategoryId],  
[Project2].[CategoryId1] AS [CategoryId1],  
[Project2].[DisplayName1] AS [DisplayName1],  
[Project2].[C1] AS [C1],  
[Project2].[C2] AS [C2],  
[Project2].[VehicleOptionId2] AS [VehicleOptionId2],  
[Project2].[VehicleTypeRegionId] AS [VehicleTypeRegionId],  
[Project2].[VehicleId] AS [VehicleId],  
[Project2].[PriceTypeId] AS [PriceTypeId],  
[Project2].[PriceAdjustment] AS [PriceAdjustment],  
[Project2].[ValueTypeId] AS [ValueTypeId],  
[Project2].[PriceTypeId1] AS [PriceTypeId1],  
[Project2].[DisplayName2] AS [DisplayName2] 
FROM ( SELECT  
        [Extent1].[VehicleOptionId] AS [VehicleOptionId],  
        [Extent1].[DisplayName] AS [DisplayName],  
        [Extent1].[OptionAvailabilityDisplayName] AS [OptionAvailabilityDisplayName],  
        [Extent1].[IsDefaultConfiguration] AS [IsDefaultConfiguration],  
        [Extent1].[SortOrder] AS [SortOrder],  
        [Extent2].[VehicleOptionId] AS [VehicleOptionId1],  
        [Extent2].[CategoryId] AS [CategoryId],  
        [Extent3].[CategoryId] AS [CategoryId1],  
        [Extent3].[DisplayName] AS [DisplayName1],  
        1 AS [C1],  
        [Project1].[VehicleOptionId] AS [VehicleOptionId2],  
        [Project1].[VehicleTypeRegionId] AS [VehicleTypeRegionId],  
        [Project1].[VehicleId] AS [VehicleId],  
        [Project1].[PriceTypeId] AS [PriceTypeId],  
        [Project1].[PriceAdjustment] AS [PriceAdjustment],  
        [Project1].[ValueTypeId] AS [ValueTypeId],  
        [Project1].[PriceTypeId1] AS [PriceTypeId1],  
        [Project1].[DisplayName] AS [DisplayName2],  
        [Project1].[C1] AS [C2] 
        FROM    [dbo].[KBB_V3_VehicleOption] AS [Extent1] 
        INNER JOIN [dbo].[KBB_V3_VehicleOptionCategory] AS [Extent2] ON ([Extent1].[VehicleOptionId] = [Extent2].[VehicleOptionId]) OR (([Extent1].[VehicleOptionId] IS NULL) AND ([Extent2].[VehicleOptionId] IS NULL)) 
        INNER JOIN [dbo].[KBB_V3_Category] AS [Extent3] ON ([Extent2].[CategoryId] = [Extent3].[CategoryId]) OR (([Extent2].[CategoryId] IS NULL) AND ([Extent3].[CategoryId] IS NULL)) 
        LEFT OUTER JOIN  (SELECT  
                [Extent4].[VehicleOptionId] AS [VehicleOptionId],  
                [Extent4].[VehicleTypeRegionId] AS [VehicleTypeRegionId],  
                [Extent4].[VehicleId] AS [VehicleId],  
                [Extent4].[PriceTypeId] AS [PriceTypeId],  
                [Extent4].[PriceAdjustment] AS [PriceAdjustment],  
                [Extent4].[ValueTypeId] AS [ValueTypeId],  
                [Extent5].[PriceTypeId] AS [PriceTypeId1],  
                [Extent5].[DisplayName] AS [DisplayName],  
                1 AS [C1] 
                FROM  [dbo].[KBB_V3_OptionRegionPriceAdjustment] AS [Extent4] 
                LEFT OUTER JOIN [dbo].[KBB_V3_PriceType] AS [Extent5] ON [Extent4].[PriceTypeId] = [Extent5].[PriceTypeId] ) AS [Project1] ON ([Project1].[VehicleTypeRegionId] = @p__linq__6) AND ([Project1].[VehicleId] = @p__linq__7) AND ([Project1].[VehicleOptionId] = [Extent1].[VehicleOptionId]) 
        WHERE (4 = [Extent1].[OptionTypeId]) AND ([Extent1].[VehicleId] = @p__linq__9) AND (22 = [Extent3].[CategoryTypeId]) 
)  AS [Project2] 
ORDER BY [Project2].[SortOrder] ASC, [Project2].[VehicleOptionId] ASC, [Project2].[VehicleOptionId1] ASC, [Project2].[CategoryId] ASC, [Project2].[CategoryId1] ASC, [Project2].[C2] ASC',N'@p__linq__6 int,@p__linq__7 int,@p__linq__9 int',@p__linq__6=0,@p__linq__7=261637,@p__linq__9=261637

这是我只得到4.1(使用POCO的):

    SELECT  
[Extent1].[VehicleOptionId] AS [VehicleOptionId],  
[Extent1].[VehicleTypeRegionId] AS [VehicleTypeRegionId],  
[Extent1].[VehicleId] AS [VehicleId],  
[Extent1].[PriceTypeId] AS [PriceTypeId],  
[Extent1].[PriceAdjustment] AS [PriceAdjustment],  
[Extent1].[ValueTypeId] AS [ValueTypeId] 
FROM [dbo].[KBB_V3_OptionRegionPriceAdjustment] AS [Extent1]

如您所见，没有WHERE子句，也没有SQL其余部分的跟踪......

Answer 1

我已经找到了一个解决方法。我的新查询如下:

var query = 
  (from xVehicleOption in this.entities.VehicleOptionSet 
   let regionPriceAdjustment = (from xOptionRegionPriceAdjustmentLeftOuter in xVehicleOption.XOptionRegionPriceAdjustment 
      where xOptionRegionPriceAdjustmentLeftOuter.VehicleTypeRegionId == this.vehicleTypeRegionId 
      //&& xOptionRegionPriceAdjustmentLeftOuter.VehicleId == this.vehicleId 
      //&& xOptionRegionPriceAdjustmentLeftOuter.VehicleOptionId == xVehicleOption.VehicleOptionId 
      select new 
      { 
       xOptionRegionPriceAdjustmentLeftOuter, 
       xOptionRegionPriceAdjustmentLeftOuter.XPriceType 
      }) 
   join xVehicleOptionCategory in this.entities.VehicleOptionCategorySet on xVehicleOption.VehicleOptionId equals xVehicleOptionCategory.VehicleOptionId 
   join xCategory in this.entities.CategorySet on xVehicleOptionCategory.CategoryId equals xCategory.CategoryId 
   where xVehicleOption.VehicleId == this.vehicleId 
   && xVehicleOption.OptionTypeId == 4 
   && xCategory.CategoryTypeId == 22 
   select new 
   { 
   Name = xVehicleOption.DisplayName, 
   VehicleOptionId = xVehicleOption.VehicleOptionId, 
   SortOrder = xVehicleOption.SortOrder, 
   OptionAvailability = xVehicleOption.OptionAvailabilityDisplayName, 
   IsDefaultConfiguration = xVehicleOption.IsDefaultConfiguration, 
   OptionType = xCategory.DisplayName, 
   RegionPriceAdjustment = regionPriceAdjustment 
   }).OrderBy(o => o.SortOrder); 

请注意，在第三行，我是从xVehicleOption而不是this.entities查询。，因此执行内连接。当然，我不需要现在被注释掉的行。我在阅读Craig Stuntz的一篇优秀文章时找到了这个解决方案:

http://blogs.teamb.com/craigstuntz/2010/01/13/38525/

当然，根据Craig的说法，我的查询可以更简化，但出于可理解性的原因，我想保留大部分内容。

这仍然不能解释为什么最初的查询不能在4.1中工作(同样，where语句被完全忽略)，但这是一个可以接受的快速解决方案。

希望这对其他人有帮助，YC