In this article let us explore the Eager Loading in EF Core using theInclude & ThenInclude method. The Include Lambda method is an extension method from the namespace Microsoft.EntityFrameworkCore.. We use the include & ThenInclude methods, along with the Projection Query in EF Core to load the related entities. In this tutorial, we look at include method and learn how to load entities from multiple levels and multiple tables. We also show how to filter, use them multiple times to load the related data.
Database:
The Database for this tutorial is taken from the chinook database.Source Code:
The source code of this project available in GitHub. It also contains the script of the database
Table of Contents
Eager Loading in EF Core
Eager loading is a technique where EF core loads the related entities along with the main entity. All entities are loaded in a single query to database thus saving bandwidth and crucial server CPU time. The other two ways of loading data are Lazy Loading & Explicit Loading. The Eager Loading is in EF Core done using the Include & ThenInclude method
Include Method
Consider the following query which loads the customer into the context. We would also like to know the InvoiceDate & total from the Invoice table. One way is to handle it is by using the Lazy Loading in EF Core. The Other way is to use the include method.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | using (ChinookContext db = new ChinookContext()) { var customers = db.Customer .Where(c => c.FirstName.StartsWith("A")) .ToList(); foreach (var customer in customers) { Console.WriteLine("{0} {1}", customer.FirstName, customer.LastName); } } //SQL QUERY SELECT [c].[CustomerId], [c].[Address], [c].[City], [c].[Company], [c].[Country], [c].[Email], [c].[Fax], [c].[FirstName], [c].[LastName], [c].[Phone], [c].[PostalCode], [c].[State], [c].[SupportRepId] FROM [Customer] AS [c] WHERE [c].[FirstName] LIKE N'A%' |
To include the Invoice table, we use the Include method and passing the navigation property name as the lambda expression as shown below Include(c => c.Invoice). Note that the Invoice property is a collection navigational property in the customer entity.
You can look at the SQL query. The EF Core Left Joins the Invoice table to customer table correctly using the CustomerId as join condition.
The list of invoices are correctly returned as a collection. You can now loop through it and display it as shown below.
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 | using (ChinookContext db = new ChinookContext()) { var customers = db.Customer .Include(c => c.Invoice) .Where(c => c.FirstName.StartsWith("A")) .ToList(); foreach (var customer in customers) { Console.WriteLine("{0} {1}", customer.FirstName, customer.LastName); foreach (var invoice in customer.Invoice) { Console.WriteLine("\t\t {0} {1}", invoice.InvoiceDate, invoice.Total); } } } //SQL SELECT [c].[CustomerId], [c].[Address], [c].[City], [c].[Company], [c].[Country], [c].[Email], [c].[Fax], [c].[FirstName], [c].[LastName], [c].[Phone], [c].[PostalCode], [c].[State], [c].[SupportRepId], [i].[InvoiceId], [i].[BillingAddress], [i].[BillingCity], [i].[BillingCountry], [i].[BillingPostalCode], [i].[BillingState], [i].[CustomerId], [i].[InvoiceDate], [i].[Total] FROM [Customer] AS [c] LEFT JOIN [Invoice] AS [i] ON [c].[CustomerId] = [i].[CustomerId] WHERE [c].[FirstName] LIKE N'A%' ORDER BY [c].[CustomerId], [i].[InvoiceId] |
ThenInclude
You can drill down through relationships to include multiple levels.
For Example in the above query, we included invoice entity which has a one to many relationships with customer entity. We also have a Invoiceline, which has one to many relationships with the invoice entity. To load the Invoiceline we use the ThenInclude method on the invoice and passing InvoiceLine as lambda expression as shown below.
The data returned now has InvoiceLine collection under each Invoice.
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 | using (ChinookContext db = new ChinookContext()) { var customers = db.Customer .Include(c => c.Invoice) .ThenInclude(c => c.InvoiceLine) .Where(c => c.FirstName.StartsWith("A")) .ToList(); foreach (var customer in customers) { Console.WriteLine("{0} {1}", customer.FirstName, customer.LastName); foreach (var invoice in customer.Invoice) { Console.WriteLine("\t {0} {1}", invoice.InvoiceDate, invoice.Total); foreach (var invoiceLine in invoice.InvoiceLine) { Console.WriteLine("\t\t {0} {1} {2}", invoiceLine.TrackId, invoiceLine.Quantity, invoiceLine.UnitPrice); } } } } //SQL SELECT [c].[CustomerId], [c].[Address], [c].[City], [c].[Company], [c].[Country], [c].[Email], [c].[Fax], [c].[FirstName], [c].[LastName], [c].[Phone], [c].[PostalCode], [c].[State], [c].[SupportRepId], [t].[InvoiceId], [t].[BillingAddress], [t].[BillingCity], [t].[BillingCountry], [t].[BillingPostalCode], [t].[BillingState], [t].[CustomerId], [t].[InvoiceDate], [t].[Total], [t].[InvoiceLineId], [t].[InvoiceId0], [t].[Quantity], [t].[TrackId], [t].[UnitPrice] FROM [Customer] AS [c] LEFT JOIN ( SELECT [i].[InvoiceId], [i].[BillingAddress], [i].[BillingCity], [i].[BillingCountry], [i].[BillingPostalCode], [i].[BillingState], [i].[CustomerId], [i].[InvoiceDate], [i].[Total], [i0].[InvoiceLineId], [i0].[InvoiceId] AS [InvoiceId0], [i0].[Quantity], [i0].[TrackId], [i0].[UnitPrice] FROM [Invoice] AS [i] LEFT JOIN [InvoiceLine] AS [i0] ON [i].[InvoiceId] = [i0].[InvoiceId] ) AS [t] ON [c].[CustomerId] = [t].[CustomerId] WHERE [c].[FirstName] LIKE N'A%' ORDER BY [c].[CustomerId], [t].[InvoiceId], [t].[InvoiceLineId] |
Multiple Levels
The above query returned the invoiceLine , which has TrackId,Quantity & UnitPrice properties. To get the Track.Name, we need to include the Track entity. And Again to include MediaType.Name we need to include the MediaType entity
You can keep going down the Relationship chain using the ThenInclude as shown below
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 | using (ChinookContext db = new ChinookContext()) { var customers = db.Customer .Include(c => c.Invoice) .ThenInclude(c => c.InvoiceLine) .ThenInclude(c => c.Track) .ThenInclude(c => c.MediaType) .Where(c => c.FirstName.StartsWith("A")) .ToList(); foreach (var customer in customers) { Console.WriteLine("{0} {1}", customer.FirstName, customer.LastName); foreach (var invoice in customer.Invoice) { Console.WriteLine("\t {0} {1}", invoice.InvoiceDate, invoice.Total); foreach (var invoiceLine in invoice.InvoiceLine) { Console.WriteLine("\t\t {0} {1} {2} {3}", invoiceLine.Track.Name, invoiceLine.Track.MediaType.Name, invoiceLine.Quantity, invoiceLine.UnitPrice); } } } } //SQL SELECT [c].[CustomerId], [c].[Address], [c].[City], [c].[Company], [c].[Country], [c].[Email], [c].[Fax], [c].[FirstName], [c].[LastName], [c].[Phone], [c].[PostalCode], [c].[State], [c].[SupportRepId], [t1].[InvoiceId], [t1].[BillingAddress], [t1].[BillingCity], [t1].[BillingCountry], [t1].[BillingPostalCode], [t1].[BillingState], [t1].[CustomerId], [t1].[InvoiceDate], [t1].[Total], [t1].[InvoiceLineId], [t1].[InvoiceId0], [t1].[Quantity], [t1].[TrackId], [t1].[UnitPrice], [t1].[TrackId0], [t1].[AlbumId], [t1].[Bytes], [t1].[Composer], [t1].[GenreId], [t1].[MediaTypeId], [t1].[Milliseconds], [t1].[Name], [t1].[UnitPrice0], [t1].[MediaTypeId0], [t1].[Name0] FROM [Customer] AS [c] LEFT JOIN ( SELECT [i].[InvoiceId], [i].[BillingAddress], [i].[BillingCity], [i].[BillingCountry], [i].[BillingPostalCode], [i].[BillingState], [i].[CustomerId], [i].[InvoiceDate], [i].[Total], [t0].[InvoiceLineId], [t0].[InvoiceId] AS [InvoiceId0], [t0].[Quantity], [t0].[TrackId], [t0].[UnitPrice], [t0].[TrackId0], [t0].[AlbumId], [t0].[Bytes], [t0].[Composer], [t0].[GenreId], [t0].[MediaTypeId], [t0].[Milliseconds], [t0].[Name], [t0].[UnitPrice0], [t0].[MediaTypeId0], [t0].[Name0] FROM [Invoice] AS [i] LEFT JOIN ( SELECT [i0].[InvoiceLineId], [i0].[InvoiceId], [i0].[Quantity], [i0].[TrackId], [i0].[UnitPrice], [t].[TrackId] AS [TrackId0], [t].[AlbumId], [t].[Bytes], [t].[Composer], [t].[GenreId], [t].[MediaTypeId], [t].[Milliseconds], [t].[Name], [t].[UnitPrice] AS [UnitPrice0], [m].[MediaTypeId] AS [MediaTypeId0], [m].[Name] AS [Name0] FROM [InvoiceLine] AS [i0] INNER JOIN [Track] AS [t] ON [i0].[TrackId] = [t].[TrackId] INNER JOIN [MediaType] AS [m] ON [t].[MediaTypeId] = [m].[MediaTypeId] ) AS [t0] ON [i].[InvoiceId] = [t0].[InvoiceId] ) AS [t1] ON [c].[CustomerId] = [t1].[CustomerId] WHERE [c].[FirstName] LIKE N'A%' ORDER BY [c].[CustomerId], [t1].[InvoiceId], [t1].[InvoiceLineId], [t1].[TrackId0], [t1].[MediaTypeId0] |
Multiple Includes
The Customer entity also related to Employee entity using the navigational property SupportRepId. We can start second Include to include that also in the result as shown below
Note that Include always applied on the first entity in the query (i.e Customer)
1 2 3 4 5 6 7 8 9 10 | var customers = db.Customer .Include(c => c.Invoice) .ThenInclude(c => c.InvoiceLine) .ThenInclude(c => c.Track) .ThenInclude(c => c.MediaType) .Include(c => c.SupportRep) .Where(c => c.FirstName.StartsWith("A")) .ToList(); |
Filtered Include
The EF Core 3.0 Currently does not support adding Filters in Include/ThenInclude methods. But this feature will available in the next version of EF Core. You can use the following operators
- Where,
- OrderBy(Descending)/ThenBy(Descending),
- Skip,
- Take.
The operators applicable only on Collections. You can apply only one filter per navigation.
If you have included the navigation multiple times you should ensure that the filter is applied only once. Alternatively you can apply the same exact filter in all navigation
You can also use the where clause to filer out the result in the ThenInclude method.
1 2 3 4 5 6 7 8 9 10 11 12 13 | //Will not work in EF Core 3.0 Will be available in EF Core 5.0 var customers = db.Customer .Include(c => c.Invoice .Where(f => f.Total > 10 )) .ThenInclude(c => c.InvoiceLine) .ThenInclude(c => c.Track) .ThenInclude(c => c.MediaType) .Include(c => c.SupportRep) .Where(c => c.FirstName.StartsWith("A")) .ToList(); |
Multiple ThenInclude
Now, Consider the following model. The child has two collection navigation properties. SubChild1 & SubChild1.
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 | public class Parent { public int Id { get; set; } public string Name { get; set; } public virtual Child Child { get; set; } } public class Child { public int Id { get; set; } public string ChildName { get; set; } public virtual List<SubChild1> SubChild1 { get; set; } public virtual List<SubChild2> SubChild2 { get; set; } } public class SubChild1 { public int Id { get; set; } } public class SubChild2 { public int Id { get; set; } } |
You can use multiple Includes on the same table Child and use the ThenInclude to load the SubChild1 & SubChild2. Note that if you are using the where clause in Include ensure that you use it on only one of them. Or use the same clause both of them. Different clauses will result in an error or may result in an invalid result.
1 2 3 4 5 | db.Parent .Include(a => a.Child).ThenInclude(b => b.SubChild1) .Include(a => a.Child).ThenInclude(b => b.SubChild2) |
The following is another example
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | db.Invoice .Include(x => x.Items) .ThenInclude(x => x.Department) .ThenInclude(x => x.location) .Include(x => x.Items) .ThenInclude(x => x.Store) .ThenInclude(x => x.Manager) .Include(x => x.Items) .ThenInclude(x => x.Model) .ThenInclude(x => x.Color); |
Reference
Read More
Summary
The eager loading in EF Core done via the Include & ThenInclude methods. We need to supply the navigational property of the related entity as the argument. The next version of EF Core will also support the filtering & Ordering of the related data. You can apply on multiple tables and at multiple levels etc.