How do I eagerly Include the child and grandchild elements of an entity in Entity Framework Code First?

Question

Imagine three entities (Customer, Book, Author) related like this:

A Customer has many Books

A Book has one Author

I use that data to print a report like this:

Customer: Peter
  Book: To Kill a Mockingbird - Author: Harper Lee
  Book: A Tale of Two Cities - Author: Charles Dickens
Customer: Melanie
  Book: The Hobbit - Author: J. R. R. Tolkien

When I query for Customers I get, as expected, a bunch of queries of the following nature

1. A query to get the Customers
2. A query per Customer to get his Books
3. A query per Book to get its author

I can reduce the number of queries by including the books like so:

var customers = db.Customers.Include(c => c.Books);

But I don't know how to load the third level (Author). How can I do that?

Foran

Answer 1

There's an overload for Include that accepts a string which can denote the full path to any extra properties you need:

var customers = db.Customers.Include("Books.Author");

It looks strange because "Author" isn't a property on a collection of books (rather a property on each individual book) but it works. Give it a whirl.

Gaige

@jbueno If possible, I would avoid them because they aren't caught by the compiler if you change the name of a property of a Customer. – Gamba

You can use Select, as per @tdykstra's answer. – Wakeless

I don't think magic strings should ever be promoted as a viable solution. They can make debugging a real pain. – Guinn

Cyril has the right answer below, .ThenInclude. You should not be using strings. learn.microsoft.com/en-us/ef/core/querying/related-data – Hazelton

Answer 2

Also, it isn't necessary to use the string overload. This method will work too:

var customers = db.Customers.Include(c => c.Books.Select(b => b.Author));

For more examples see the EF team blog post: http://blogs.msdn.com/b/adonet/archive/2011/01/31/using-dbcontext-in-ef-feature-ctp5-part-6-loading-related-entities.aspx

And this tutorial: http://www.asp.net/entity-framework/tutorials/reading-related-data-with-the-entity-framework-in-an-asp-net-mvc-application

Morganite

I think this is a better approach because it's strong type, as oppose to string Path that is weakly type. – Presidency

Note that to use this include method you need to reference System.Data.Entity – Seraph

This is a great question - and the answer checks out for me. I tried it 6 levels of hierarchy deep and although the SQL Query that it generates is massive, it is much more efficient than the potential hundreds of individual select statements that can arise. Way to go EF in creating TSQL for dummies! – Eolanda

I could not get .Select working. I ended up using .Include(x => x.Books.Author) – Baroque

This syntax doesn't work in EF core as far as I can tell. There is a ThenInclude that might be better solutions if you reference this question https://mcmap.net/q/297308/-include-grandchildren-in-ef-query – Brezin

Answer 3

You can use ThenInclude keyword:

var customers = db.Customers.Include(c => c.Books).ThenInclude(book => book.Author));}

Nicotine

Note that this only applies to Entity Framework Core – Cloe

Hooray for EF-Core improvements. – Streptokinase