65

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?

adolfojp
  • 2,961
  • 4
  • 24
  • 21

3 Answers3

145

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

renakre
  • 8,001
  • 5
  • 46
  • 99
tdykstra
  • 5,880
  • 2
  • 23
  • 20
  • 6
    I think this is a better approach because it's strong type, as oppose to string Path that is weakly type. – stack247 Apr 10 '13 at 03:25
  • 10
    Note that to use this include method you need to reference System.Data.Entity – jazza1000 Jul 21 '15 at 15:14
  • 1
    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! – Aaron Hudon Dec 02 '15 at 03:11
  • 1
    I could not get .Select working. I ended up using .Include(x => x.Books.Author) – Tyler Jun 30 '16 at 12:09
  • 4
    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 http://stackoverflow.com/a/38741905/1047812 – Uriah Blatherwick Dec 14 '16 at 17:18
48

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.

Matt Hamilton
  • 200,371
  • 61
  • 386
  • 320
  • 2
    @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. – Ravvy Dec 16 '16 at 00:42
  • 3
    You can use `Select`, as per @tdykstra's answer. – Tiago César Oliveira Dec 28 '16 at 13:46
  • 2
    I don't think magic strings should ever be promoted as a viable solution. They can make debugging a real pain. – Bruno Aug 30 '17 at 19:03
  • Cyril has the right answer below, .ThenInclude. You should not be using strings. https://learn.microsoft.com/en-us/ef/core/querying/related-data – AUSTX_RJL Feb 22 '19 at 20:20
9

You can use ThenInclude keyword:

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

Cyril
  • 109
  • 1
  • 1