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