Include method in Entity Framework has two overloads. One of which takes navigation property as a string. The other one is Include lambda method. The Include Lambda method is an extension method from the namespace System.Data.Entity. In this tutorial, we look at include method, and learn how to load entities from multiple levels and multiple tables.
The sample database for this example is taken from the AdventureWorks database. Download it and use it to create the model. You can refer to the article code first existing database to reverse engineer the model. Also, you look at the SQL queries that EF generates by logging them to console
Table of Contents
Include method
The following example shows how to use the include method against a reference property ProductModel. The EF creates a join statement for each include method and sends it database. Thus retrieving List of Products and its ProductModel in a single query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | using (AdventureWorks db = new AdventureWorks()) { //Disable Lazy Loading db.Configuration.LazyLoadingEnabled = false; db.Database.Log = Console.Write; var product = (from p in db.Products .Include(p => p.ProductModel) where p.ProductID == 814 select p).ToList(); foreach (var p in product) { Console.WriteLine("{0} {1} {2}", p.ProductID, p.Name, p.ProductModel.Name); } } |
Corresponding SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | SELECT [Extent1].[ProductID] AS [ProductID], [Extent1].[Name] AS [Name], [Extent1].[ProductNumber] AS [ProductNumber], [Extent1].[MakeFlag] AS [MakeFlag], [Extent1].[FinishedGoodsFlag] AS [FinishedGoodsFlag], [Extent1].[Color] AS [Color], [Extent1].[SafetyStockLevel] AS [SafetyStockLevel], [Extent1].[ReorderPoint] AS [ReorderPoint], [Extent1].[StandardCost] AS [StandardCost], [Extent1].[ListPrice] AS [ListPrice], [Extent1].[Size] AS [Size], [Extent1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode], [Extent1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode], [Extent1].[Weight] AS [Weight], [Extent1].[DaysToManufacture] AS [DaysToManufacture], [Extent1].[ProductLine] AS [ProductLine], [Extent1].[Class] AS [Class], [Extent1].[Style] AS [Style], [Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID], [Extent1].[ProductModelID] AS [ProductModelID], [Extent1].[SellStartDate] AS [SellStartDate], [Extent1].[SellEndDate] AS [SellEndDate], [Extent1].[DiscontinuedDate] AS [DiscontinuedDate], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[ProductModelID] AS [ProductModelID1], [Extent2].[Name] AS [Name1], [Extent2].[CatalogDescription] AS [CatalogDescription], [Extent2].[Instructions] AS [Instructions], [Extent2].[rowguid] AS [rowguid1], [Extent2].[ModifiedDate] AS [ModifiedDate1] FROM [Production].[Product] AS [Extent1] LEFT OUTER JOIN [Production].[ProductModel] AS [Extent2] ON [Extent1].[ProductModelID] = [Extent2].[ProductModelID] WHERE 814 = [Extent1].[ProductID] |
Include a Collection Property
The Products is collection navigation property for the ProductModel.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | using (AdventureWorks db = new AdventureWorks()) { //Disable Lazy Loading db.Configuration.LazyLoadingEnabled = false; db.Database.Log = Console.Write; var models = (from p in db.ProductModels .Include(p => p.Products) where p.Name == "Classic Vest" select p).ToList(); foreach (var p in models) { Console.WriteLine("{0}", p.Name); foreach (var product in p.Products) { Console.WriteLine("\t\t{0} {1}", product.ProductID, product.Name); } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | SELECT [Project1].[ProductModelID] AS [ProductModelID], [Project1].[Name] AS [Name], [Project1].[CatalogDescription] AS [CatalogDescription], [Project1].[Instructions] AS [Instructions], [Project1].[rowguid] AS [rowguid], [Project1].[ModifiedDate] AS [ModifiedDate], [Project1].[C1] AS [C1], [Project1].[ProductID] AS [ProductID], [Project1].[Name1] AS [Name1], [Project1].[ProductNumber] AS [ProductNumber], [Project1].[MakeFlag] AS [MakeFlag], [Project1].[FinishedGoodsFlag] AS [FinishedGoodsFlag], [Project1].[Color] AS [Color], [Project1].[SafetyStockLevel] AS [SafetyStockLevel], [Project1].[ReorderPoint] AS [ReorderPoint], [Project1].[StandardCost] AS [StandardCost], [Project1].[ListPrice] AS [ListPrice], [Project1].[Size] AS [Size], [Project1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode], [Project1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode], [Project1].[Weight] AS [Weight], [Project1].[DaysToManufacture] AS [DaysToManufacture], [Project1].[ProductLine] AS [ProductLine], [Project1].[Class] AS [Class], [Project1].[Style] AS [Style], [Project1].[ProductSubcategoryID] AS [ProductSubcategoryID], [Project1].[ProductModelID1] AS [ProductModelID1], [Project1].[SellStartDate] AS [SellStartDate], [Project1].[SellEndDate] AS [SellEndDate], [Project1].[DiscontinuedDate] AS [DiscontinuedDate], [Project1].[rowguid1] AS [rowguid1], [Project1].[ModifiedDate1] AS [ModifiedDate1] FROM ( SELECT [Extent1].[ProductModelID] AS [ProductModelID], [Extent1].[Name] AS [Name], [Extent1].[CatalogDescription] AS [CatalogDescription], [Extent1].[Instructions] AS [Instructions], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[ProductID] AS [ProductID], [Extent2].[Name] AS [Name1], [Extent2].[ProductNumber] AS [ProductNumber], [Extent2].[MakeFlag] AS [MakeFlag], [Extent2].[FinishedGoodsFlag] AS [FinishedGoodsFlag], [Extent2].[Color] AS [Color], [Extent2].[SafetyStockLevel] AS [SafetyStockLevel], [Extent2].[ReorderPoint] AS [ReorderPoint], [Extent2].[StandardCost] AS [StandardCost], [Extent2].[ListPrice] AS [ListPrice], [Extent2].[Size] AS [Size], [Extent2].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode], [Extent2].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode], [Extent2].[Weight] AS [Weight], [Extent2].[DaysToManufacture] AS [DaysToManufacture], [Extent2].[ProductLine] AS [ProductLine], [Extent2].[Class] AS [Class], [Extent2].[Style] AS [Style], [Extent2].[ProductSubcategoryID] AS [ProductSubcategoryID], [Extent2].[ProductModelID] AS [ProductModelID1], [Extent2].[SellStartDate] AS [SellStartDate], [Extent2].[SellEndDate] AS [SellEndDate], [Extent2].[DiscontinuedDate] AS [DiscontinuedDate], [Extent2].[rowguid] AS [rowguid1], [Extent2].[ModifiedDate] AS [ModifiedDate1], CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [Production].[ProductModel] AS [Extent1] LEFT OUTER JOIN [Production].[Product] AS [Extent2] ON [Extent1].[ProductModelID] = [Extent2].[ProductModelID] WHERE N'Classic Vest' = [Extent1].[Name] ) AS [Project1] ORDER BY [Project1].[ProductModelID] ASC, [Project1].[C1] ASC |
Filter the child collection
The Include does not allow us to filter the child collection
Include from Multiple Tables
In the following example we have three tables in the include method. Note that all are reference properties.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | using (AdventureWorks db = new AdventureWorks()) { //Disable Lazy Loading db.Configuration.LazyLoadingEnabled = false; db.Database.Log = Console.Write; var product = (from p in db.Products .Include(p => p.ProductModel) .Include(p => p.ProductSubcategory) .Include(p=> p.UnitMeasure) where p.UnitMeasure != null select p).Take(5).ToList(); foreach (var p in product) { Console.WriteLine("{0} {1} {2} {3} {4}", p.ProductID, p.Name, p.ProductModel.Name, p.ProductSubcategory.Name, p.UnitMeasure.Name); } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | SELECT TOP (5) [Extent1].[ProductID] AS [ProductID], [Extent1].[Name] AS [Name], [Extent1].[ProductNumber] AS [ProductNumber], [Extent1].[MakeFlag] AS [MakeFlag], [Extent1].[FinishedGoodsFlag] AS [FinishedGoodsFlag], [Extent1].[Color] AS [Color], [Extent1].[SafetyStockLevel] AS [SafetyStockLevel], [Extent1].[ReorderPoint] AS [ReorderPoint], [Extent1].[StandardCost] AS [StandardCost], [Extent1].[ListPrice] AS [ListPrice], [Extent1].[Size] AS [Size], [Extent1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode], [Extent1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode], [Extent1].[Weight] AS [Weight], [Extent1].[DaysToManufacture] AS [DaysToManufacture], [Extent1].[ProductLine] AS [ProductLine], [Extent1].[Class] AS [Class], [Extent1].[Style] AS [Style], [Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID], [Extent1].[ProductModelID] AS [ProductModelID], [Extent1].[SellStartDate] AS [SellStartDate], [Extent1].[SellEndDate] AS [SellEndDate], [Extent1].[DiscontinuedDate] AS [DiscontinuedDate], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[ProductModelID] AS [ProductModelID1], [Extent2].[Name] AS [Name1], [Extent2].[CatalogDescription] AS [CatalogDescription], [Extent2].[Instructions] AS [Instructions], [Extent2].[rowguid] AS [rowguid1], [Extent2].[ModifiedDate] AS [ModifiedDate1], [Extent3].[ProductSubcategoryID] AS [ProductSubcategoryID1], [Extent3].[ProductCategoryID] AS [ProductCategoryID], [Extent3].[Name] AS [Name2], [Extent3].[rowguid] AS [rowguid2], [Extent3].[ModifiedDate] AS [ModifiedDate2], [Extent4].[UnitMeasureCode] AS [UnitMeasureCode], [Extent4].[Name] AS [Name3], [Extent4].[ModifiedDate] AS [ModifiedDate3] FROM [Production].[Product] AS [Extent1] LEFT OUTER JOIN [Production].[ProductModel] AS [Extent2] ON [Extent1].[ProductModelID] = [Extent2].[ProductModelID] LEFT OUTER JOIN [Production].[ProductSubcategory] AS [Extent3] ON [Extent1].[ProductSubcategoryID] = [Extent3].[ProductSubcategoryID] LEFT OUTER JOIN [Production].[UnitMeasure] AS [Extent4] ON [Extent1].[SizeUnitMeasureCode] = [Extent4].[UnitMeasureCode] WHERE [Extent1].[SizeUnitMeasureCode] IS NOT NULL |
Include method Multiple Levels
You can also load entities to multiple levels using the following syntax. The include statement includes bothEmployee and Person table (note that both are reference types). The Query will use join to bring data from both the tables as shown below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | using (AdventureWorks db = new AdventureWorks()) { //Disable Lazy Loading db.Configuration.LazyLoadingEnabled = false; db.Database.Log = Console.Write; var person = (from p in db.SalesPersons .Include(p => p.Employee.Person) select p).Take(5).ToList(); foreach (var p in person) { Console.WriteLine("{0}", p.Employee.Person.FirstName); } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | SELECT TOP (5) [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent1].[TerritoryID] AS [TerritoryID], [Extent1].[SalesQuota] AS [SalesQuota], [Extent1].[Bonus] AS [Bonus], [Extent1].[CommissionPct] AS [CommissionPct], [Extent1].[SalesYTD] AS [SalesYTD], [Extent1].[SalesLastYear] AS [SalesLastYear], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate], [Extent2].[BusinessEntityID] AS [BusinessEntityID1], [Extent2].[NationalIDNumber] AS [NationalIDNumber], [Extent2].[LoginID] AS [LoginID], [Extent2].[OrganizationLevel] AS [OrganizationLevel], [Extent2].[JobTitle] AS [JobTitle], [Extent2].[BirthDate] AS [BirthDate], [Extent2].[MaritalStatus] AS [MaritalStatus], [Extent2].[Gender] AS [Gender], [Extent2].[HireDate] AS [HireDate], [Extent2].[SalariedFlag] AS [SalariedFlag], [Extent2].[VacationHours] AS [VacationHours], [Extent2].[SickLeaveHours] AS [SickLeaveHours], [Extent2].[CurrentFlag] AS [CurrentFlag], [Extent2].[rowguid] AS [rowguid1], [Extent2].[ModifiedDate] AS [ModifiedDate1], [Join2].[BusinessEntityID1] AS [BusinessEntityID2], [Join2].[PersonType] AS [PersonType], [Join2].[NameStyle] AS [NameStyle], [Join2].[Title] AS [Title], [Join2].[FirstName] AS [FirstName], [Join2].[MiddleName] AS [MiddleName], [Join2].[LastName] AS [LastName], [Join2].[Suffix] AS [Suffix], [Join2].[EmailPromotion] AS [EmailPromotion], [Join2].[AdditionalContactInfo] AS [AdditionalContactInfo], [Join2].[Demographics] AS [Demographics], [Join2].[rowguid1] AS [rowguid2], [Join2].[ModifiedDate1] AS [ModifiedDate2] FROM [Sales].[SalesPerson] AS [Extent1] INNER JOIN [HumanResources].[Employee] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID] LEFT OUTER JOIN (SELECT [Extent3].[BusinessEntityID] AS [BusinessEntityID1], [Extent3].[PersonType] AS [PersonType], [Extent3].[NameStyle] AS [NameStyle], [Extent3].[Title] AS [Title], [Extent3].[FirstName] AS [FirstName], [Extent3].[MiddleName] AS [MiddleName], [Extent3].[LastName] AS [LastName], [Extent3].[Suffix] AS [Suffix], [Extent3].[EmailPromotion] AS [EmailPromotion], [Extent3].[AdditionalContactInfo] AS [AdditionalContactInfo], [Extent3].[Demographics] AS [Demographics], [Extent3].[rowguid] AS [rowguid1], [Extent3].[ModifiedDate] AS [ModifiedDate1], [Extent4].[BusinessEntityID] AS [BusinessEntityID2] FROM [Person].[Person] AS [Extent3] LEFT OUTER JOIN [HumanResources].[Employee] AS [Extent4] ON [Extent3].[BusinessEntityID] = [Extent4].[BusinessEntityID] ) AS [Join2] ON [Extent1].[BusinessEntityID] = [Join2].[BusinessEntityID2] |
Summary
In this article, we showed how to make use of the Include method in Entity Framework. The Include Lambda method is an extension method from the namespace System.Data.Entity. Using it we can eagerly load the related entities in a Single Query. We also learned how to load entities from multiple levels and multiple tables.
