EF: Why Include method is an anti-pattern IMHO?

On different session I saw, eager loading with Include method is presented as a good pattern.

However, IMHO, it’s an anti-pattern.

That’s right, Include method like lazy loading is very easy to use. But performance with these is often very bad or even catastrophic.

One guy of the EF team told me last year, like a joke, that they should named Include IncludeButYouShouldNotUseIt and LazyLoadingEnabledButYouShouldNot.

For a developer, it’s a surprise because Include seems very good. Indeed we have only one query executed on the DB.

However, you have to understand that SQL is not C#.

If you use the Include method, you will get the full graph in only one SQL query.

But, in SQL, we don’t get some objects with relationships. You get a resultset with some datarows with some columns.

This is very important because it means that Include methods generated SQL gets flat data with many duplications.

Take a sample. I will use ContosoRetailDW DB with the following EDM:

clip_image001[4]

Then I will use the following query:

var customers = context.Customers.Take(50).Include(c => c.OnlineSales.Select(os => os.Product.ProductSubcategory.ProductCategory)).Include(c => c.OnlineSales.Select(os => os.Store)).ToList();

 

EF generates the following SQL query:

 

SELECT

[Project1].[CustomerKey] AS [CustomerKey],

[Project1].[GeographyKey] AS [GeographyKey],

[Project1].[CustomerLabel] AS [CustomerLabel],

[Project1].[Title] AS [Title],

[Project1].[FirstName] AS [FirstName],

[Project1].[MiddleName] AS [MiddleName],

[Project1].[LastName] AS [LastName],

[Project1].[NameStyle] AS [NameStyle],

[Project1].[BirthDate] AS [BirthDate],

[Project1].[MaritalStatus] AS [MaritalStatus],

[Project1].[Suffix] AS [Suffix],

[Project1].[Gender] AS [Gender],

[Project1].[EmailAddress] AS [EmailAddress],

[Project1].[YearlyIncome] AS [YearlyIncome],

[Project1].[TotalChildren] AS [TotalChildren],

[Project1].[NumberChildrenAtHome] AS [NumberChildrenAtHome],

[Project1].[Education] AS [Education],

[Project1].[Occupation] AS [Occupation],

[Project1].[HouseOwnerFlag] AS [HouseOwnerFlag],

[Project1].[NumberCarsOwned] AS [NumberCarsOwned],

[Project1].[AddressLine1] AS [AddressLine1],

[Project1].[AddressLine2] AS [AddressLine2],

[Project1].[Phone] AS [Phone],

[Project1].[DateFirstPurchase] AS [DateFirstPurchase],

[Project1].[CustomerType] AS [CustomerType],

[Project1].[CompanyName] AS [CompanyName],

[Project1].[ETLLoadID] AS [ETLLoadID],

[Project1].[LoadDate] AS [LoadDate],

[Project1].[UpdateDate] AS [UpdateDate],

[Project1].[C1] AS [C1],

[Project1].[OnlineSalesKey] AS [OnlineSalesKey],

[Project1].[DateKey] AS [DateKey],

[Project1].[StoreKey] AS [StoreKey],

[Project1].[ProductKey] AS [ProductKey],

[Project1].[PromotionKey] AS [PromotionKey],

[Project1].[CurrencyKey] AS [CurrencyKey],

[Project1].[CustomerKey1] AS [CustomerKey1],

[Project1].[SalesOrderNumber] AS [SalesOrderNumber],

[Project1].[SalesOrderLineNumber] AS [SalesOrderLineNumber],

[Project1].[SalesQuantity] AS [SalesQuantity],

[Project1].[SalesAmount] AS [SalesAmount],

[Project1].[ReturnQuantity] AS [ReturnQuantity],

[Project1].[ReturnAmount] AS [ReturnAmount],

[Project1].[DiscountQuantity] AS [DiscountQuantity],

[Project1].[DiscountAmount] AS [DiscountAmount],

[Project1].[TotalCost] AS [TotalCost],

[Project1].[UnitCost] AS [UnitCost],

[Project1].[UnitPrice] AS [UnitPrice],

[Project1].[ETLLoadID1] AS [ETLLoadID1],

[Project1].[LoadDate1] AS [LoadDate1],

[Project1].[UpdateDate1] AS [UpdateDate1],

[Project1].[ProductKey1] AS [ProductKey1],

[Project1].[ProductLabel] AS [ProductLabel],

[Project1].[ProductName] AS [ProductName],

[Project1].[ProductDescription] AS [ProductDescription],

[Project1].[ProductSubcategoryKey] AS [ProductSubcategoryKey],

[Project1].[Manufacturer] AS [Manufacturer],

[Project1].[BrandName] AS [BrandName],

[Project1].[ClassID] AS [ClassID],

[Project1].[ClassName] AS [ClassName],

[Project1].[StyleID] AS [StyleID],

[Project1].[StyleName] AS [StyleName],

[Project1].[ColorID] AS [ColorID],

[Project1].[ColorName] AS [ColorName],

[Project1].[Size] AS [Size],

[Project1].[SizeRange] AS [SizeRange],

[Project1].[SizeUnitMeasureID] AS [SizeUnitMeasureID],

[Project1].[Weight] AS [Weight],

[Project1].[WeightUnitMeasureID] AS [WeightUnitMeasureID],

[Project1].[UnitOfMeasureID] AS [UnitOfMeasureID],

[Project1].[UnitOfMeasureName] AS [UnitOfMeasureName],

[Project1].[StockTypeID] AS [StockTypeID],

[Project1].[StockTypeName] AS [StockTypeName],

[Project1].[UnitCost1] AS [UnitCost1],

[Project1].[UnitPrice1] AS [UnitPrice1],

[Project1].[AvailableForSaleDate] AS [AvailableForSaleDate],

[Project1].[StopSaleDate] AS [StopSaleDate],

[Project1].[Status] AS [Status],

[Project1].[ImageURL] AS [ImageURL],

[Project1].[ProductURL] AS [ProductURL],

[Project1].[ETLLoadID2] AS [ETLLoadID2],

[Project1].[LoadDate2] AS [LoadDate2],

[Project1].[UpdateDate2] AS [UpdateDate2],

[Project1].[ProductSubcategoryKey1] AS [ProductSubcategoryKey1],

[Project1].[ProductSubcategoryLabel] AS [ProductSubcategoryLabel],

[Project1].[ProductSubcategoryName] AS [ProductSubcategoryName],

[Project1].[ProductSubcategoryDescription] AS [ProductSubcategoryDescription],

[Project1].[ProductCategoryKey] AS [ProductCategoryKey],

[Project1].[ETLLoadID3] AS [ETLLoadID3],

[Project1].[LoadDate3] AS [LoadDate3],

[Project1].[UpdateDate3] AS [UpdateDate3],

[Project1].[ProductCategoryKey1] AS [ProductCategoryKey1],

[Project1].[ProductCategoryLabel] AS [ProductCategoryLabel],

[Project1].[ProductCategoryName] AS [ProductCategoryName],

[Project1].[ProductCategoryDescription] AS [ProductCategoryDescription],

[Project1].[ETLLoadID4] AS [ETLLoadID4],

[Project1].[LoadDate4] AS [LoadDate4],

[Project1].[UpdateDate4] AS [UpdateDate4],

[Project1].[StoreKey1] AS [StoreKey1],

[Project1].[GeographyKey1] AS [GeographyKey1],

[Project1].[StoreManager] AS [StoreManager],

[Project1].[StoreType] AS [StoreType],

[Project1].[StoreName] AS [StoreName],

[Project1].[StoreDescription] AS [StoreDescription],

[Project1].[Status1] AS [Status1],

[Project1].[OpenDate] AS [OpenDate],

[Project1].[CloseDate] AS [CloseDate],

[Project1].[EntityKey] AS [EntityKey],

[Project1].[ZipCode] AS [ZipCode],

[Project1].[ZipCodeExtension] AS [ZipCodeExtension],

[Project1].[StorePhone] AS [StorePhone],

[Project1].[StoreFax] AS [StoreFax],

[Project1].[AddressLine11] AS [AddressLine11],

[Project1].[AddressLine21] AS [AddressLine21],

[Project1].[CloseReason] AS [CloseReason],

[Project1].[EmployeeCount] AS [EmployeeCount],

[Project1].[SellingAreaSize] AS [SellingAreaSize],

[Project1].[LastRemodelDate] AS [LastRemodelDate],

[Project1].[ETLLoadID5] AS [ETLLoadID5],

[Project1].[LoadDate5] AS [LoadDate5],

[Project1].[UpdateDate5] AS [UpdateDate5]

FROM ( SELECT

     [Limit1].[CustomerKey] AS [CustomerKey],

     [Limit1].[GeographyKey] AS [GeographyKey],

     [Limit1].[CustomerLabel] AS [CustomerLabel],

     [Limit1].[Title] AS [Title],

     [Limit1].[FirstName] AS [FirstName],

     [Limit1].[MiddleName] AS [MiddleName],

     [Limit1].[LastName] AS [LastName],

     [Limit1].[NameStyle] AS [NameStyle],

     [Limit1].[BirthDate] AS [BirthDate],

     [Limit1].[MaritalStatus] AS [MaritalStatus],

     [Limit1].[Suffix] AS [Suffix],

     [Limit1].[Gender] AS [Gender],

     [Limit1].[EmailAddress] AS [EmailAddress],

     [Limit1].[YearlyIncome] AS [YearlyIncome],

     [Limit1].[TotalChildren] AS [TotalChildren],

     [Limit1].[NumberChildrenAtHome] AS [NumberChildrenAtHome],

     [Limit1].[Education] AS [Education],

     [Limit1].[Occupation] AS [Occupation],

     [Limit1].[HouseOwnerFlag] AS [HouseOwnerFlag],

     [Limit1].[NumberCarsOwned] AS [NumberCarsOwned],

     [Limit1].[AddressLine1] AS [AddressLine1],

     [Limit1].[AddressLine2] AS [AddressLine2],

     [Limit1].[Phone] AS [Phone],

     [Limit1].[DateFirstPurchase] AS [DateFirstPurchase],

     [Limit1].[CustomerType] AS [CustomerType],

     [Limit1].[CompanyName] AS [CompanyName],

     [Limit1].[ETLLoadID] AS [ETLLoadID],

     [Limit1].[LoadDate] AS [LoadDate],

     [Limit1].[UpdateDate] AS [UpdateDate],

     [Join4].[OnlineSalesKey] AS [OnlineSalesKey],

     [Join4].[DateKey] AS [DateKey],

     [Join4].[StoreKey1] AS [StoreKey],

     [Join4].[ProductKey1] AS [ProductKey],

     [Join4].[PromotionKey] AS [PromotionKey],

     [Join4].[CurrencyKey] AS [CurrencyKey],

     [Join4].[CustomerKey] AS [CustomerKey1],

     [Join4].[SalesOrderNumber] AS [SalesOrderNumber],

     [Join4].[SalesOrderLineNumber] AS [SalesOrderLineNumber],

     [Join4].[SalesQuantity] AS [SalesQuantity],

     [Join4].[SalesAmount] AS [SalesAmount],

     [Join4].[ReturnQuantity] AS [ReturnQuantity],

     [Join4].[ReturnAmount] AS [ReturnAmount],

     [Join4].[DiscountQuantity] AS [DiscountQuantity],

     [Join4].[DiscountAmount] AS [DiscountAmount],

     [Join4].[TotalCost] AS [TotalCost],

     [Join4].[UnitCost1] AS [UnitCost],

     [Join4].[UnitPrice1] AS [UnitPrice],

     [Join4].[ETLLoadID1] AS [ETLLoadID1],

     [Join4].[LoadDate1] AS [LoadDate1],

     [Join4].[UpdateDate1] AS [UpdateDate1],

     [Join4].[ProductKey2] AS [ProductKey1],

     [Join4].[ProductLabel] AS [ProductLabel],

     [Join4].[ProductName] AS [ProductName],

     [Join4].[ProductDescription] AS [ProductDescription],

     [Join4].[ProductSubcategoryKey1] AS [ProductSubcategoryKey],

     [Join4].[Manufacturer] AS [Manufacturer],

     [Join4].[BrandName] AS [BrandName],

     [Join4].[ClassID] AS [ClassID],

     [Join4].[ClassName] AS [ClassName],

     [Join4].[StyleID] AS [StyleID],

     [Join4].[StyleName] AS [StyleName],

     [Join4].[ColorID] AS [ColorID],

     [Join4].[ColorName] AS [ColorName],

     [Join4].[Size] AS [Size],

     [Join4].[SizeRange] AS [SizeRange],

     [Join4].[SizeUnitMeasureID] AS [SizeUnitMeasureID],

     [Join4].[Weight] AS [Weight],

     [Join4].[WeightUnitMeasureID] AS [WeightUnitMeasureID],

     [Join4].[UnitOfMeasureID] AS [UnitOfMeasureID],

     [Join4].[UnitOfMeasureName] AS [UnitOfMeasureName],

     [Join4].[StockTypeID] AS [StockTypeID],

     [Join4].[StockTypeName] AS [StockTypeName],

     [Join4].[UnitCost2] AS [UnitCost1],

     [Join4].[UnitPrice2] AS [UnitPrice1],

     [Join4].[AvailableForSaleDate] AS [AvailableForSaleDate],

     [Join4].[StopSaleDate] AS [StopSaleDate],

     [Join4].[Status1] AS [Status],

     [Join4].[ImageURL] AS [ImageURL],

     [Join4].[ProductURL] AS [ProductURL],

     [Join4].[ETLLoadID2] AS [ETLLoadID2],

     [Join4].[LoadDate2] AS [LoadDate2],

     [Join4].[UpdateDate2] AS [UpdateDate2],

     [Join4].[ProductSubcategoryKey2] AS [ProductSubcategoryKey1],

     [Join4].[ProductSubcategoryLabel] AS [ProductSubcategoryLabel],

     [Join4].[ProductSubcategoryName] AS [ProductSubcategoryName],

     [Join4].[ProductSubcategoryDescription] AS [ProductSubcategoryDescription],

     [Join4].[ProductCategoryKey1] AS [ProductCategoryKey],

     [Join4].[ETLLoadID3] AS [ETLLoadID3],

     [Join4].[LoadDate3] AS [LoadDate3],

     [Join4].[UpdateDate3] AS [UpdateDate3],

     [Join4].[ProductCategoryKey2] AS [ProductCategoryKey1],

     [Join4].[ProductCategoryLabel] AS [ProductCategoryLabel],

     [Join4].[ProductCategoryName] AS [ProductCategoryName],

     [Join4].[ProductCategoryDescription] AS [ProductCategoryDescription],

     [Join4].[ETLLoadID4] AS [ETLLoadID4],

     [Join4].[LoadDate4] AS [LoadDate4],

     [Join4].[UpdateDate4] AS [UpdateDate4],

     [Join4].[StoreKey2] AS [StoreKey1],

     [Join4].[GeographyKey] AS [GeographyKey1],

     [Join4].[StoreManager] AS [StoreManager],

     [Join4].[StoreType] AS [StoreType],

     [Join4].[StoreName] AS [StoreName],

     [Join4].[StoreDescription] AS [StoreDescription],

     [Join4].[Status2] AS [Status1],

     [Join4].[OpenDate] AS [OpenDate],

     [Join4].[CloseDate] AS [CloseDate],

     [Join4].[EntityKey] AS [EntityKey],

     [Join4].[ZipCode] AS [ZipCode],

     [Join4].[ZipCodeExtension] AS [ZipCodeExtension],

     [Join4].[StorePhone] AS [StorePhone],

     [Join4].[StoreFax] AS [StoreFax],

     [Join4].[AddressLine1] AS [AddressLine11],

     [Join4].[AddressLine2] AS [AddressLine21],

     [Join4].[CloseReason] AS [CloseReason],

     [Join4].[EmployeeCount] AS [EmployeeCount],

     [Join4].[SellingAreaSize] AS [SellingAreaSize],

     [Join4].[LastRemodelDate] AS [LastRemodelDate],

     [Join4].[ETLLoadID5] AS [ETLLoadID5],

     [Join4].[LoadDate5] AS [LoadDate5],

     [Join4].[UpdateDate5] AS [UpdateDate5],

     CASE WHEN ([Join4].[OnlineSalesKey] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]

     FROM (SELECT TOP (50) [c].[CustomerKey] AS [CustomerKey], [c].[GeographyKey] AS [GeographyKey], [c].[CustomerLabel] AS [CustomerLabel], [c].[Title] AS [Title], [c].[FirstName] AS [FirstName], [c].[MiddleName] AS [MiddleName], [c].[LastName] AS [LastName], [c].[NameStyle] AS [NameStyle], [c].[BirthDate] AS [BirthDate], [c].[MaritalStatus] AS [MaritalStatus], [c].[Suffix] AS [Suffix], [c].[Gender] AS [Gender], [c].[EmailAddress] AS [EmailAddress], [c].[YearlyIncome] AS [YearlyIncome], [c].[TotalChildren] AS [TotalChildren], [c].[NumberChildrenAtHome] AS [NumberChildrenAtHome], [c].[Education] AS [Education], [c].[Occupation] AS [Occupation], [c].[HouseOwnerFlag] AS [HouseOwnerFlag], [c].[NumberCarsOwned] AS [NumberCarsOwned], [c].[AddressLine1] AS [AddressLine1], [c].[AddressLine2] AS [AddressLine2], [c].[Phone] AS [Phone], [c].[DateFirstPurchase] AS [DateFirstPurchase], [c].[CustomerType] AS [CustomerType], [c].[CompanyName] AS [CompanyName], [c].[ETLLoadID] AS [ETLLoadID], [c].[LoadDate] AS [LoadDate], [c].[UpdateDate] AS [UpdateDate]

         FROM [dbo].[DimCustomer] AS [c] ) AS [Limit1]

     LEFT OUTER JOIN (SELECT [Extent2].[OnlineSalesKey] AS [OnlineSalesKey], [Extent2].[DateKey] AS [DateKey], [Extent2].[StoreKey] AS [StoreKey1], [Extent2].[ProductKey] AS [ProductKey1], [Extent2].[PromotionKey] AS [PromotionKey], [Extent2].[CurrencyKey] AS [CurrencyKey], [Extent2].[CustomerKey] AS [CustomerKey], [Extent2].[SalesOrderNumber] AS [SalesOrderNumber], [Extent2].[SalesOrderLineNumber] AS [SalesOrderLineNumber], [Extent2].[SalesQuantity] AS [SalesQuantity], [Extent2].[SalesAmount] AS [SalesAmount], [Extent2].[ReturnQuantity] AS [ReturnQuantity], [Extent2].[ReturnAmount] AS [ReturnAmount], [Extent2].[DiscountQuantity] AS [DiscountQuantity], [Extent2].[DiscountAmount] AS [DiscountAmount], [Extent2].[TotalCost] AS [TotalCost], [Extent2].[UnitCost] AS [UnitCost1], [Extent2].[UnitPrice] AS [UnitPrice1], [Extent2].[ETLLoadID] AS [ETLLoadID1], [Extent2].[LoadDate] AS [LoadDate1], [Extent2].[UpdateDate] AS [UpdateDate1], [Extent3].[ProductKey] AS [ProductKey2], [Extent3].[ProductLabel] AS [ProductLabel], [Extent3].[ProductName] AS [ProductName], [Extent3].[ProductDescription] AS [ProductDescription], [Extent3].[ProductSubcategoryKey] AS [ProductSubcategoryKey1], [Extent3].[Manufacturer] AS [Manufacturer], [Extent3].[BrandName] AS [BrandName], [Extent3].[ClassID] AS [ClassID], [Extent3].[ClassName] AS [ClassName], [Extent3].[StyleID] AS [StyleID], [Extent3].[StyleName] AS [StyleName], [Extent3].[ColorID] AS [ColorID], [Extent3].[ColorName] AS [ColorName], [Extent3].[Size] AS [Size], [Extent3].[SizeRange] AS [SizeRange], [Extent3].[SizeUnitMeasureID] AS [SizeUnitMeasureID], [Extent3].[Weight] AS [Weight], [Extent3].[WeightUnitMeasureID] AS [WeightUnitMeasureID], [Extent3].[UnitOfMeasureID] AS [UnitOfMeasureID], [Extent3].[UnitOfMeasureName] AS [UnitOfMeasureName], [Extent3].[StockTypeID] AS [StockTypeID], [Extent3].[StockTypeName] AS [StockTypeName], [Extent3].[UnitCost] AS [UnitCost2], [Extent3].[UnitPrice] AS [UnitPrice2], [Extent3].[AvailableForSaleDate] AS [AvailableForSaleDate], [Extent3].[StopSaleDate] AS [StopSaleDate], [Extent3].[Status] AS [Status1], [Extent3].[ImageURL] AS [ImageURL], [Extent3].[ProductURL] AS [ProductURL], [Extent3].[ETLLoadID] AS [ETLLoadID2], [Extent3].[LoadDate] AS [LoadDate2], [Extent3].[UpdateDate] AS [UpdateDate2], [Extent4].[ProductSubcategoryKey] AS [ProductSubcategoryKey2], [Extent4].[ProductSubcategoryLabel] AS [ProductSubcategoryLabel], [Extent4].[ProductSubcategoryName] AS [ProductSubcategoryName], [Extent4].[ProductSubcategoryDescription] AS [ProductSubcategoryDescription], [Extent4].[ProductCategoryKey] AS [ProductCategoryKey1], [Extent4].[ETLLoadID] AS [ETLLoadID3], [Extent4].[LoadDate] AS [LoadDate3], [Extent4].[UpdateDate] AS [UpdateDate3], [Extent5].[ProductCategoryKey] AS [ProductCategoryKey2], [Extent5].[ProductCategoryLabel] AS [ProductCategoryLabel], [Extent5].[ProductCategoryName] AS [ProductCategoryName], [Extent5].[ProductCategoryDescription] AS [ProductCategoryDescription], [Extent5].[ETLLoadID] AS [ETLLoadID4], [Extent5].[LoadDate] AS [LoadDate4], [Extent5].[UpdateDate] AS [UpdateDate4], [Extent6].[StoreKey] AS [StoreKey2], [Extent6].[GeographyKey] AS [GeographyKey], [Extent6].[StoreManager] AS [StoreManager], [Extent6].[StoreType] AS [StoreType], [Extent6].[StoreName] AS [StoreName], [Extent6].[StoreDescription] AS [StoreDescription], [Extent6].[Status] AS [Status2], [Extent6].[OpenDate] AS [OpenDate], [Extent6].[CloseDate] AS [CloseDate], [Extent6].[EntityKey] AS [EntityKey], [Extent6].[ZipCode] AS [ZipCode], [Extent6].[ZipCodeExtension] AS [ZipCodeExtension], [Extent6].[StorePhone] AS [StorePhone], [Extent6].[StoreFax] AS

[StoreFax], [Extent6].[AddressLine1] AS [AddressLine1], [Extent6].[AddressLine2] AS [AddressLine2], [Extent6].[CloseReason] AS [CloseReason], [Extent6].[EmployeeCount] AS [EmployeeCount], [Extent6].[SellingAreaSize] AS [SellingAreaSize], [Extent6].[LastRemodelDate] AS [LastRemodelDate], [Extent6].[ETLLoadID] AS [ETLLoadID5], [Extent6].[LoadDate] AS [LoadDate5], [Extent6].[UpdateDate] AS [UpdateDate5]

         FROM [dbo].[FactOnlineSales] AS [Extent2]

         INNER JOIN [dbo].[DimProduct] AS [Extent3] ON [Extent2].[ProductKey] = [Extent3].[ProductKey]

         LEFT OUTER JOIN [dbo].[DimProductSubcategory] AS [Extent4] ON [Extent3].[ProductSubcategoryKey] = [Extent4].[ProductSubcategoryKey]

         LEFT OUTER JOIN [dbo].[DimProductCategory] AS [Extent5] ON [Extent4].[ProductCategoryKey] = [Extent5].[ProductCategoryKey]

         INNER JOIN [dbo].[DimStore] AS [Extent6] ON [Extent2].[StoreKey] = [Extent6].[StoreKey] ) AS [Join4] ON [Limit1].[CustomerKey] = [Join4].[CustomerKey]

) AS [Project1]

ORDER BY [Project1].[CustomerKey] ASC, [Project1].[C1] ASC

The first point is the fact that this query is very bad!

In my computer the client execution time is 122 422 ms so more than 2 minutes! // which often throws a timeout exception

 

With the following query, the client execution time falls to 4161ms!

 

SELECT

     [Limit1].[CustomerKey] AS [CustomerKey],

     [Limit1].[GeographyKey] AS [GeographyKey],

     [Limit1].[CustomerLabel] AS [CustomerLabel],

     [Limit1].[Title] AS [Title],

     [Limit1].[FirstName] AS [FirstName],

     [Limit1].[MiddleName] AS [MiddleName],

     [Limit1].[LastName] AS [LastName],

     [Limit1].[NameStyle] AS [NameStyle],

     [Limit1].[BirthDate] AS [BirthDate],

     [Limit1].[MaritalStatus] AS [MaritalStatus],

     [Limit1].[Suffix] AS [Suffix],

     [Limit1].[Gender] AS [Gender],

     [Limit1].[EmailAddress] AS [EmailAddress],

     [Limit1].[YearlyIncome] AS [YearlyIncome],

     [Limit1].[TotalChildren] AS [TotalChildren],

     [Limit1].[NumberChildrenAtHome] AS [NumberChildrenAtHome],

     [Limit1].[Education] AS [Education],

     [Limit1].[Occupation] AS [Occupation],

     [Limit1].[HouseOwnerFlag] AS [HouseOwnerFlag],

     [Limit1].[NumberCarsOwned] AS [NumberCarsOwned],

     [Limit1].[AddressLine1] AS [AddressLine1],

     [Limit1].[AddressLine2] AS [AddressLine2],

     [Limit1].[Phone] AS [Phone],

     [Limit1].[DateFirstPurchase] AS [DateFirstPurchase],

     [Limit1].[CustomerType] AS [CustomerType],

     [Limit1].[CompanyName] AS [CompanyName],

     [Limit1].[ETLLoadID] AS [ETLLoadID],

     [Limit1].[LoadDate] AS [LoadDate],

     [Limit1].[UpdateDate] AS [UpdateDate],

     [Extent2].[OnlineSalesKey] AS [OnlineSalesKey],

     [Extent2].[DateKey] AS [DateKey],

     [Extent2].[StoreKey] AS [StoreKey],

     [Extent2].[ProductKey] AS [ProductKey],

     [Extent2].[PromotionKey] AS [PromotionKey],

     [Extent2].[CurrencyKey] AS [CurrencyKey],

     [Extent2].[CustomerKey] AS [CustomerKey1],

     [Extent2].[SalesOrderNumber] AS [SalesOrderNumber],

     [Extent2].[SalesOrderLineNumber] AS [SalesOrderLineNumber],

     [Extent2].[SalesQuantity] AS [SalesQuantity],

     [Extent2].[SalesAmount] AS [SalesAmount],

     [Extent2].[ReturnQuantity] AS [ReturnQuantity],

     [Extent2].[ReturnAmount] AS [ReturnAmount],

     [Extent2].[DiscountQuantity] AS [DiscountQuantity],

     [Extent2].[DiscountAmount] AS [DiscountAmount],

     [Extent2].[TotalCost] AS [TotalCost],

     [Extent2].[UnitCost] AS [UnitCost],

     [Extent2].[UnitPrice] AS [UnitPrice],

     [Extent2].[ETLLoadID] AS [ETLLoadID1],

     [Extent2].[LoadDate] AS [LoadDate1],

     [Extent2].[UpdateDate] AS [UpdateDate1],

     [Extent3].[ProductKey] AS [ProductKey1],

     [Extent3].[ProductLabel] AS [ProductLabel],

     [Extent3].[ProductName] AS [ProductName],

     [Extent3].[ProductDescription] AS [ProductDescription],

     [Extent3].[ProductSubcategoryKey] AS [ProductSubcategoryKey],

     [Extent3].[Manufacturer] AS [Manufacturer],

     [Extent3].[BrandName] AS [BrandName],

     [Extent3].[ClassID] AS [ClassID],

     [Extent3].[ClassName] AS [ClassName],

     [Extent3].[StyleID] AS [StyleID],

     [Extent3].[StyleName] AS [StyleName],

     [Extent3].[ColorID] AS [ColorID],

     [Extent3].[ColorName] AS [ColorName],

     [Extent3].[Size] AS [Size],

     [Extent3].[SizeRange] AS [SizeRange],

     [Extent3].[SizeUnitMeasureID] AS [SizeUnitMeasureID],

     [Extent3].[Weight] AS [Weight],

     [Extent3].[WeightUnitMeasureID] AS [WeightUnitMeasureID],

     [Extent3].[UnitOfMeasureID] AS [UnitOfMeasureID],

     [Extent3].[UnitOfMeasureName] AS [UnitOfMeasureName],

     [Extent3].[StockTypeID] AS [StockTypeID],

     [Extent3].[StockTypeName] AS [StockTypeName],

     [Extent3].[UnitCost] AS [UnitCost1],

     [Extent3].[UnitPrice] AS [UnitPrice1],

     [Extent3].[AvailableForSaleDate] AS [AvailableForSaleDate],

     [Extent3].[StopSaleDate] AS [StopSaleDate],

     [Extent3].[Status] AS [Status],

     [Extent3].[ImageURL] AS [ImageURL],

     [Extent3].[ProductURL] AS [ProductURL],

     [Extent3].[ETLLoadID] AS [ETLLoadID2],

     [Extent3].[LoadDate] AS [LoadDate2],

     [Extent3].[UpdateDate] AS [UpdateDate2],

     [Extent4].[ProductSubcategoryKey] AS [ProductSubcategoryKey1],

     [Extent4].[ProductSubcategoryLabel] AS [ProductSubcategoryLabel],

     [Extent4].[ProductSubcategoryName] AS [ProductSubcategoryName],

     [Extent4].[ProductSubcategoryDescription] AS [ProductSubcategoryDescription],

     [Extent4].[ProductCategoryKey] AS [ProductCategoryKey],

     [Extent4].[ETLLoadID] AS [ETLLoadID3],

     [Extent4].[LoadDate] AS [LoadDate3],

     [Extent4].[UpdateDate] AS [UpdateDate3],

     [Extent5].[ProductCategoryKey] AS [ProductCategoryKey1],

     [Extent5].[ProductCategoryLabel] AS [ProductCategoryLabel],

     [Extent5].[ProductCategoryName] AS [ProductCategoryName],

     [Extent5].[ProductCategoryDescription] AS [ProductCategoryDescription],

     [Extent5].[ETLLoadID] AS [ETLLoadID4],

     [Extent5].[LoadDate] AS [LoadDate4],

     [Extent5].[UpdateDate] AS [UpdateDate4],

     [Extent6].[StoreKey] AS [StoreKey1],

     [Extent6].[GeographyKey] AS [GeographyKey1],

     [Extent6].[StoreManager] AS [StoreManager],

     [Extent6].[StoreType] AS [StoreType],

     [Extent6].[StoreName] AS [StoreName],

     [Extent6].[StoreDescription] AS [StoreDescription],

     [Extent6].[Status] AS [Status1],

     [Extent6].[OpenDate] AS [OpenDate],

     [Extent6].[CloseDate] AS [CloseDate],

     [Extent6].[EntityKey] AS [EntityKey],

     [Extent6].[ZipCode] AS [ZipCode],

     [Extent6].[ZipCodeExtension] AS [ZipCodeExtension],

     [Extent6].[StorePhone] AS [StorePhone],

     [Extent6].[StoreFax] AS [StoreFax],

     [Extent6].[AddressLine1] AS [AddressLine11],

     [Extent6].[AddressLine2] AS [AddressLine21],

     [Extent6].[CloseReason] AS [CloseReason],

     [Extent6].[EmployeeCount] AS [EmployeeCount],

     [Extent6].[SellingAreaSize] AS [SellingAreaSize],

     [Extent6].[LastRemodelDate] AS [LastRemodelDate],

     [Extent6].[ETLLoadID] AS [ETLLoadID5],

     [Extent6].[LoadDate] AS [LoadDate5],

     [Extent6].[UpdateDate] AS [UpdateDate5],

     CASE WHEN ([Extent2].[OnlineSalesKey] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]

     FROM (SELECT TOP (50) [c].[CustomerKey] AS [CustomerKey], [c].[GeographyKey] AS [GeographyKey], [c].[CustomerLabel] AS [CustomerLabel], [c].[Title] AS [Title], [c].[FirstName] AS [FirstName], [c].[MiddleName] AS [MiddleName], [c].[LastName] AS [LastName], [c].[NameStyle] AS [NameStyle], [c].[BirthDate] AS [BirthDate], [c].[MaritalStatus] AS [MaritalStatus], [c].[Suffix] AS [Suffix], [c].[Gender] AS [Gender], [c].[EmailAddress] AS [EmailAddress], [c].[YearlyIncome] AS [YearlyIncome], [c].[TotalChildren] AS [TotalChildren], [c].[NumberChildrenAtHome] AS [NumberChildrenAtHome], [c].[Education] AS [Education], [c].[Occupation] AS [Occupation], [c].[HouseOwnerFlag] AS [HouseOwnerFlag], [c].[NumberCarsOwned] AS [NumberCarsOwned], [c].[AddressLine1] AS [AddressLine1], [c].[AddressLine2] AS [AddressLine2], [c].[Phone] AS [Phone], [c].[DateFirstPurchase] AS [DateFirstPurchase], [c].[CustomerType] AS [CustomerType], [c].[CompanyName] AS [CompanyName], [c].[ETLLoadID] AS [ETLLoadID], [c].[LoadDate] AS [LoadDate], [c].[UpdateDate] AS [UpdateDate]

         FROM [dbo].[DimCustomer] AS [c] ) AS [Limit1]

     LEFT OUTER JOIN [dbo].[FactOnlineSales] AS [Extent2] ON [Limit1].[CustomerKey] = [Extent2].[CustomerKey]

     LEFT OUTER JOIN [dbo].[DimProduct] AS [Extent3] ON [Extent2].[ProductKey] = [Extent3].[ProductKey]

     LEFT OUTER JOIN [dbo].[DimProductSubcategory] AS [Extent4] ON [Extent3].[ProductSubcategoryKey] = [Extent4].[ProductSubcategoryKey]

     LEFT OUTER JOIN [dbo].[DimProductCategory] AS [Extent5] ON [Extent4].[ProductCategoryKey] = [Extent5].[ProductCategoryKey]

     LEFT OUTER JOIN [dbo].[DimStore] AS [Extent6] ON [Extent2].[StoreKey] = [Extent6].[StoreKey]

ORDER BY [Limit1].[CustomerKey] ASC, [C1] ASC

These queries return 8338 rows.

 

Now you will take a look on the bytes received from server. With these queries, it is 13 082 440 so more than 13 MB that will transit from the DB to your application or your server.

 

Why so much?

Because we will have many identic information. We get 8338 rows for only 50 customers with 121 columns.

 

Another point here is the fact that EF need much time to generated SQL and to materialize entities from datarows.

 

Now we will see what happens if we load the graph in several queries. Not thousands of queries like you will have with lazy loading (which is catastrophic for performance) but with 6 SQL queries: one per EntitySets.

 

var customers = context.Customers.Take(50).ToList();
 
foreach (var onlineSale in context.Customers.Take(50).SelectMany(c => c.OnlineSales));
 
foreach (var product in context.Products.Where(p => context.Customers.Take(50).SelectMany(c => c.OnlineSales).Any(os => os.ProductKey == p.ProductKey)));
 
foreach(var subcategory in context.ProductSubcategories.Where(sc => context.Customers.Take(50).SelectMany(c => c.OnlineSales).Any(os => os.Product.ProductSubcategoryKey == sc.ProductSubcategoryKey)));
 
foreach (var category in context.ProductCategories.Where(ca => context.Customers.Take(50).SelectMany(c => c.OnlineSales).Any(os => os.Product.ProductSubcategory.ProductCategoryKey == ca.ProductCategoryKey)));
 
foreach (var store in context.Stores.Where(s => context.Customers.Take(50).SelectMany(c => c.OnlineSales).Any(os => os.StoreKey == s.StoreKey)));

 

Note that the foreach (); is useful because EF will instantiate entities when it will iterate on them. Then, the ObjectContext will attach itself entities together (ie fill navigation properties) using FK.

Now we will look at the SQL for it:

SELECT TOP (50)

[c].[CustomerKey] AS [CustomerKey],

[c].[GeographyKey] AS [GeographyKey],

[c].[CustomerLabel] AS [CustomerLabel],

[c].[Title] AS [Title],

[c].[FirstName] AS [FirstName],

[c].[MiddleName] AS [MiddleName],

[c].[LastName] AS [LastName],

[c].[NameStyle] AS [NameStyle],

[c].[BirthDate] AS [BirthDate],

[c].[MaritalStatus] AS [MaritalStatus],

[c].[Suffix] AS [Suffix],

[c].[Gender] AS [Gender],

[c].[EmailAddress] AS [EmailAddress],

[c].[YearlyIncome] AS [YearlyIncome],

[c].[TotalChildren] AS [TotalChildren],

[c].[NumberChildrenAtHome] AS [NumberChildrenAtHome],

[c].[Education] AS [Education],

[c].[Occupation] AS [Occupation],

[c].[HouseOwnerFlag] AS [HouseOwnerFlag],

[c].[NumberCarsOwned] AS [NumberCarsOwned],

[c].[AddressLine1] AS [AddressLine1],

[c].[AddressLine2] AS [AddressLine2],

[c].[Phone] AS [Phone],

[c].[DateFirstPurchase] AS [DateFirstPurchase],

[c].[CustomerType] AS [CustomerType],

[c].[CompanyName] AS [CompanyName],

[c].[ETLLoadID] AS [ETLLoadID],

[c].[LoadDate] AS [LoadDate],

[c].[UpdateDate] AS [UpdateDate]

FROM [dbo].[DimCustomer] AS [c]

 

SELECT

[Extent2].[OnlineSalesKey] AS [OnlineSalesKey],

[Extent2].[DateKey] AS [DateKey],

[Extent2].[StoreKey] AS [StoreKey],

[Extent2].[ProductKey] AS [ProductKey],

[Extent2].[PromotionKey] AS [PromotionKey],

[Extent2].[CurrencyKey] AS [CurrencyKey],

[Extent2].[CustomerKey] AS [CustomerKey],

[Extent2].[SalesOrderNumber] AS [SalesOrderNumber],

[Extent2].[SalesOrderLineNumber] AS [SalesOrderLineNumber],

[Extent2].[SalesQuantity] AS [SalesQuantity],

[Extent2].[SalesAmount] AS [SalesAmount],

[Extent2].[ReturnQuantity] AS [ReturnQuantity],

[Extent2].[ReturnAmount] AS [ReturnAmount],

[Extent2].[DiscountQuantity] AS [DiscountQuantity],

[Extent2].[DiscountAmount] AS [DiscountAmount],

[Extent2].[TotalCost] AS [TotalCost],

[Extent2].[UnitCost] AS [UnitCost],

[Extent2].[UnitPrice] AS [UnitPrice],

[Extent2].[ETLLoadID] AS [ETLLoadID],

[Extent2].[LoadDate] AS [LoadDate],

[Extent2].[UpdateDate] AS [UpdateDate]

FROM   (SELECT TOP (50) [c].[CustomerKey] AS [CustomerKey]

      FROM [dbo].[DimCustomer] AS [c] ) AS [Limit1]

INNER JOIN [dbo].[FactOnlineSales] AS [Extent2] ON [Limit1].[CustomerKey] = [Extent2].[CustomerKey]

 

SELECT

[Extent1].[ProductKey] AS [ProductKey],

[Extent1].[ProductLabel] AS [ProductLabel],

[Extent1].[ProductName] AS [ProductName],

[Extent1].[ProductDescription] AS [ProductDescription],

[Extent1].[ProductSubcategoryKey] AS [ProductSubcategoryKey],

[Extent1].[Manufacturer] AS [Manufacturer],

[Extent1].[BrandName] AS [BrandName],

[Extent1].[ClassID] AS [ClassID],

[Extent1].[ClassName] AS [ClassName],

[Extent1].[StyleID] AS [StyleID],

[Extent1].[StyleName] AS [StyleName],

[Extent1].[ColorID] AS [ColorID],

[Extent1].[ColorName] AS [ColorName],

[Extent1].[Size] AS [Size],

[Extent1].[SizeRange] AS [SizeRange],

[Extent1].[SizeUnitMeasureID] AS [SizeUnitMeasureID],

[Extent1].[Weight] AS [Weight],

[Extent1].[WeightUnitMeasureID] AS [WeightUnitMeasureID],

[Extent1].[UnitOfMeasureID] AS [UnitOfMeasureID],

[Extent1].[UnitOfMeasureName] AS [UnitOfMeasureName],

[Extent1].[StockTypeID] AS [StockTypeID],

[Extent1].[StockTypeName] AS [StockTypeName],

[Extent1].[UnitCost] AS [UnitCost],

[Extent1].[UnitPrice] AS [UnitPrice],

[Extent1].[AvailableForSaleDate] AS [AvailableForSaleDate],

[Extent1].[StopSaleDate] AS [StopSaleDate],

[Extent1].[Status] AS [Status],

[Extent1].[ImageURL] AS [ImageURL],

[Extent1].[ProductURL] AS [ProductURL],

[Extent1].[ETLLoadID] AS [ETLLoadID],

[Extent1].[LoadDate] AS [LoadDate],

[Extent1].[UpdateDate] AS [UpdateDate]

FROM [dbo].[DimProduct] AS [Extent1]

WHERE  EXISTS (SELECT

      1 AS [C1]

      FROM   (SELECT TOP (50) [c].[CustomerKey] AS [CustomerKey]

            FROM [dbo].[DimCustomer] AS [c] ) AS [Limit1]

      INNER JOIN [dbo].[FactOnlineSales] AS [Extent3] ON [Limit1].[CustomerKey] = [Extent3].[CustomerKey]

      WHERE [Extent3].[ProductKey] = [Extent1].[ProductKey]

)

 

SELECT

[Extent1].[ProductSubcategoryKey] AS [ProductSubcategoryKey],

[Extent1].[ProductSubcategoryLabel] AS [ProductSubcategoryLabel],

[Extent1].[ProductSubcategoryName] AS [ProductSubcategoryName],

[Extent1].[ProductSubcategoryDescription] AS [ProductSubcategoryDescription],

[Extent1].[ProductCategoryKey] AS [ProductCategoryKey],

[Extent1].[ETLLoadID] AS [ETLLoadID],

[Extent1].[LoadDate] AS [LoadDate],

[Extent1].[UpdateDate] AS [UpdateDate]

FROM [dbo].[DimProductSubcategory] AS [Extent1]

WHERE  EXISTS (SELECT

      1 AS [C1]

      FROM    (SELECT TOP (50) [c].[CustomerKey] AS [CustomerKey]

            FROM [dbo].[DimCustomer] AS [c] ) AS [Limit1]

      INNER JOIN [dbo].[FactOnlineSales] AS [Extent3] ON [Limit1].[CustomerKey] = [Extent3].[CustomerKey]

      INNER JOIN [dbo].[DimProduct] AS [Extent4] ON [Extent3].[ProductKey] = [Extent4].[ProductKey]

      WHERE [Extent4].[ProductSubcategoryKey] = [Extent1].[ProductSubcategoryKey]

)

 

SELECT

[Extent1].[ProductCategoryKey] AS [ProductCategoryKey],

[Extent1].[ProductCategoryLabel] AS [ProductCategoryLabel],

[Extent1].[ProductCategoryName] AS [ProductCategoryName],

[Extent1].[ProductCategoryDescription] AS [ProductCategoryDescription],

[Extent1].[ETLLoadID] AS [ETLLoadID],

[Extent1].[LoadDate] AS [LoadDate],

[Extent1].[UpdateDate] AS [UpdateDate]

FROM [dbo].[DimProductCategory] AS [Extent1]

WHERE  EXISTS (SELECT

      1 AS [C1]

      FROM     (SELECT TOP (50) [c].[CustomerKey] AS [CustomerKey]

            FROM [dbo].[DimCustomer] AS [c] ) AS [Limit1]

      INNER JOIN [dbo].[FactOnlineSales] AS [Extent3] ON [Limit1].[CustomerKey] = [Extent3].[CustomerKey]

      INNER JOIN [dbo].[DimProduct] AS [Extent4] ON [Extent3].[ProductKey] = [Extent4].[ProductKey]

      INNER JOIN [dbo].[DimProductSubcategory] AS [Extent5] ON [Extent4].[ProductSubcategoryKey] = [Extent5].[ProductSubcategoryKey]

      WHERE [Extent5].[ProductCategoryKey] = [Extent1].[ProductCategoryKey]

)

 

SELECT

[Extent1].[StoreKey] AS [StoreKey],

[Extent1].[GeographyKey] AS [GeographyKey],

[Extent1].[StoreManager] AS [StoreManager],

[Extent1].[StoreType] AS [StoreType],

[Extent1].[StoreName] AS [StoreName],

[Extent1].[StoreDescription] AS [StoreDescription],

[Extent1].[Status] AS [Status],

[Extent1].[OpenDate] AS [OpenDate],

[Extent1].[CloseDate] AS [CloseDate],

[Extent1].[EntityKey] AS [EntityKey],

[Extent1].[ZipCode] AS [ZipCode],

[Extent1].[ZipCodeExtension] AS [ZipCodeExtension],

[Extent1].[StorePhone] AS [StorePhone],

[Extent1].[StoreFax] AS [StoreFax],

[Extent1].[AddressLine1] AS [AddressLine1],

[Extent1].[AddressLine2] AS [AddressLine2],

[Extent1].[CloseReason] AS [CloseReason],

[Extent1].[EmployeeCount] AS [EmployeeCount],

[Extent1].[SellingAreaSize] AS [SellingAreaSize],

[Extent1].[LastRemodelDate] AS [LastRemodelDate],

[Extent1].[ETLLoadID] AS [ETLLoadID],

[Extent1].[LoadDate] AS [LoadDate],

[Extent1].[UpdateDate] AS [UpdateDate]

FROM [dbo].[DimStore] AS [Extent1]

WHERE  EXISTS (SELECT

      1 AS [C1]

      FROM   (SELECT TOP (50) [c].[CustomerKey] AS [CustomerKey]

            FROM [dbo].[DimCustomer] AS [c] ) AS [Limit1]

      INNER JOIN [dbo].[FactOnlineSales] AS [Extent3] ON [Limit1].[CustomerKey] = [Extent3].[CustomerKey]

      WHERE [Extent3].[StoreKey] = [Extent1].[StoreKey]

)

What about SQL performance?

The client processing time is 4672ms to execute which is very good comparing to the SQL generated by EF and almost the same that the one I previously wrote.

The good point with this way is the fact that you don’t have duplicated information on datarows. So we reduced a lot the number of bytes received from server. With these 6 queries, it is 1 302 789 so 10 less than previously!

However, it’s a shame to execute these queries synchronously. It would be great to execute them in different threads but sadly, the ObjectContext is not thread safe.

So my idea is to run them in different ObjectContext and then to attach them in the same ObjectContext.

List<Customer> customers;
object lockObject = new object();
 
Task customersTask = new Task(() =>
    {
        using (var context2 = new ContosoRetailDWEntities())
        {
            var query = context2.Customers.Take(50);
            var objectQuery = (ObjectQuery<Customer>)query;
            objectQuery.MergeOption = MergeOption.NoTracking;
            customers = objectQuery.ToList();
            lock (lockObject)
            {
                foreach (var c in customers)
                    context.Customers.Attach(c);
            }
        }
    });
customersTask.Start();
Task onlineSalesTask = new Task(() =>
    {
        using (var context2 = new ContosoRetailDWEntities())
        {
            var query = context2.Customers.Take(50).SelectMany(c => c.OnlineSales).Take(50);
            var objectQuery = (ObjectQuery<OnlineSale>)query;
            objectQuery.MergeOption = MergeOption.NoTracking;
            var onlineSales = objectQuery.ToList();
            lock (lockObject)
            {
                foreach (var os in onlineSales)
                    context.OnlineSales.Attach(os);
            }
        }
    });
onlineSalesTask.Start();
Task productsTask = new Task(() =>
    {
        using (var context2 = new ContosoRetailDWEntities())
        {
            var query = context2.Products.Where(p => context2.Customers.Take(50).SelectMany(c => c.OnlineSales).Any(os => os.ProductKey == p.ProductKey));
            var objectQuery = (ObjectQuery<Product>)query;
            objectQuery.MergeOption = MergeOption.NoTracking;
            var products = objectQuery.ToList();
            lock (lockObject)
            {
                foreach (var p in products)
                    context.Products.Attach(p);
            }
        }
    });
productsTask.Start();
Task subCategoriesTask = new Task(() =>
{
    using (var context2 = new ContosoRetailDWEntities())
    {
        var query = context2.ProductSubcategories.Where(sc => context2.Customers.Take(50).SelectMany(c => c.OnlineSales).Any(os => os.Product.ProductSubcategoryKey == sc.ProductSubcategoryKey));
        var objectQuery = (ObjectQuery<ProductSubcategory>)query;
        objectQuery.MergeOption = MergeOption.NoTracking;
        var subCategories = objectQuery.ToList();
        lock (lockObject)
        {
            foreach (var sc in subCategories)
                context.ProductSubcategories.Attach(sc);
        }
    }
});
subCategoriesTask.Start();
Task categoriesTask = new Task(() =>
{
    using (var context2 = new ContosoRetailDWEntities())
    {
        var query = context2.ProductCategories.Where(ca => context2.Customers.Take(50).SelectMany(c => c.OnlineSales).Any(os => os.Product.ProductSubcategory.ProductCategoryKey == ca.ProductCategoryKey));
        var objectQuery = (ObjectQuery<ProductCategory>)query;
        objectQuery.MergeOption = MergeOption.NoTracking;
        var categories = objectQuery.ToList();
        lock (lockObject)
        {
            foreach (var c in categories)
                context.ProductCategories.Attach(c);
        }
    }
});
categoriesTask.Start();
Task storesTask = new Task(() =>
{
    using (var context2 = new ContosoRetailDWEntities())
    {
        var query = context2.Stores.Where(s => context2.Customers.Take(50).SelectMany(c => c.OnlineSales).Any(os => os.StoreKey == s.StoreKey));
        var objectQuery = (ObjectQuery<Store>)query;
        objectQuery.MergeOption = MergeOption.NoTracking;
        var stores = objectQuery.ToList();
        lock (lockObject)
        {
            foreach (var s in stores)
                context.Stores.Attach(s);
        }
    }
});
storesTask.Start();
 
Task.WaitAll(customersTask, onlineSalesTask, productsTask, subCategoriesTask, categoriesTask, storesTask);

Like this, if we have a enough good DB Server, all the queries will be executed on parallel so you only have to wait the time of the longest SQL query: 1 214 ms.

 

Now there is three other points to take in consideration: the time that EF needs to generate the query, the time for EF to materialize datarows to entities and the attachment logic.

In the two first points my way is better than with Include method, particularly on materialization which is difficult when you have the properties of many entities mapped to columns of the same datarow.

For the attachment logic, I use a shortcut. Indeed, I should first check if the ObjectContext does not already attach an entity with the same key before attaching one. However, even like this, this solution is significantly faster than the one with Include method.

This entry was posted in 7671, 7674. Bookmark the permalink.

2 Responses to EF: Why Include method is an anti-pattern IMHO?

  1. Darrel Miller says:

    Now do the same comparison for the following scenario: Find the 10 highest value sales this year and show me the customer information and production information for those sales.

    As long as you are including entities from the 1 side of a Many to one relation then you will not incur any data transfer overhead and you will save time in round trips.

    Including child items when retrieving a parent row should be relatively inexpensive as long as the parent table is not wide.

    Include is a tool that when used appropriately can be very valuable, however it is very easy to misuse it. Like many powerful tools.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>