Linq query with different behavior in 4.0 compared to 3.5 (let keyword issue?)

Enter code here`I have several Linq queries that use the 'let' keyword. They work fine in 3.5 but I am seeing a very strange behavior in 4.0.

Example:

 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
                                            &&开发者_Python百科 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);

This allows to enumerate through 2 nested loops. One for each option and one for each region adjustement within an option.

foreach (var bookOption in query)

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

The problem in 4.0 is that the SQL generated is a SELECT FROM RegionPriceAdjustment table WITHOUT any WHERE clause (traced with the Profiler). Which means the the command times out (and is wrong anyway because there is no sign of the other part of the query).

I have tried to replace the "let" by .Include and this works with lazy loading enabled but it generates a dynamic sql statement for each PriceType (there are 8 so it's not very efficient). I have tried to disable lazy loading but this definitely gives me an error as PriceType does not get loaded. I have tried to use two select separated by into but I get the same problem.

BTW in 4.0, I am using POCOs and EntityFramework DLL with the DbContext object. I have not tried with ObjectContext but that should not make any difference since DbContext wraps around ObjectContext.

Any clue as to why it does that? Any suggestions?

Thanks, Yves

Edited:

Here is the SQL with 3.5:

[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

Here is what I only get with 4.1 (using POCO's):

    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]

As you can see, there is no WHERE clause and no trace of the rest of the SQL......

I have found a workaround though. My new query is as follows:

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); 

Note in the third line that I am querying from xVehicleOption instead of this.entities., therefore doing an inner join. Of course, I don't need the lines that are commented out now. I found this solution in reading the excellent article by Craig Stuntz:

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

Of course, according to Craig, my query can be even more simplified but I wanted to keep most of it as it was for understandability reasons.

This still does not explain why the original query does not work in 4.1 (again the where statements are completely ignored) but it is an acceptable quick workaround.

Hope this helps others, YC