How to include() nested child entity in linq

Question

How do I include a child of a child entitiy?

Ie, Jobs have Quotes which have QuoteItems

var job = db.Jobs
            .Where(x => x.JobID == id)
            .Include(x => x.Quotes)
            .Include(x => x.Quotes.QuoteItems) // This doesn't work
            .SingleOrDefault();

Just to be clearer - I'm trying to retrieve a single Job item, and it's associated Quotes (one to many) and for each Quote the associated QuoteItems (One Quote can have many QuoteItems)

The reason I'm asking is because in my Quote Index view I'm trying to show the Total of all the Quote items for each Quote by SUMming the Subtotal, but it's coming out as 0. I'm calling the Subtotal like this:

@item.QuoteItem.Sum(p => p.Subtotal)

I believe the reason I have this issue is that my Linq query above isn't retrieving the associated QuoteItems for each Quote.

Yeah, I'm pretty sure that's not the syntax, just wanted to demonstrate what I was after — Evonet, Jun 09 '14 at 12:20
Possible duplicate of [How to include a child object's child object in Entity Framework 5](https://stackoverflow.com/questions/13047845/how-to-include-a-child-objects-child-object-in-entity-framework-5) — Michael Freidgeim, Sep 04 '18 at 12:55

score 65 · Accepted Answer · edited Feb 07 '19 at 02:44

To get a job and eager load all its quotes and their quoteitems, you write:

var job = db.Jobs
        .Include(x => x.Quotes.Select(q => q.QuoteItems))
        .Where(x => x.JobID == id)
        .SingleOrDefault();

You might need SelectMany instead of Select if QuoteItems is a collection too.

Note to others; The strongly typed Include() method is an extension method so you need to include using System.Data.Entity; at the top of your file.

score 51 · Answer 2 · edited Nov 16 '22 at 09:06

51

The method in the accepted answer doesn't work in .NET Core.

For anyone using .NET Core, while the magic string way does work, the cleaner way to do it would be ThenInclude:

var job = db.Jobs
        .Where(x => x.JobID == id)
        .Include(x => x.Quotes)
        .ThenInclude(x => x.QuoteItems)
        .SingleOrDefault();

Source: Work with data in ASP.NET Core Apps | Microsoft Learn

edited Nov 16 '22 at 09:06

Jesse

31
4

answered Jul 03 '19 at 16:14

Menasheh

3,560
3
33
48

4

This was the right answer for me. I have children of children and it seems that you can nest pretty deep with this, where the "magic string" doesn't work. – CusterN Oct 25 '20 at 16:30
I agree, this works nicely and is somewhat clean. – user2210411 Aug 30 '21 at 10:24
how do you include another ThenInclude if Quotes have another collection besides QuoteItems – cikatomo May 21 '22 at 19:13

score 31 · Answer 3 · edited Jun 03 '21 at 13:43

31

This will do the job (given that we are talking entity framework and you want to fetch child-entities):

var job = db.Jobs
            .Include(x => x.Quotes) // include the "Job.Quotes" relation and data
            .Include("Quotes.QuoteItems") // include the "Job.Quotes.QuoteItems" relation with data
            .Where(x => x.JobID == id) // going on the original Job.JobID
            .SingleOrDefault(); // fetches the first hit from db.

For more information about the Include statement have a look at this: https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.objectquery-1.include

This answer has been getting upvotes throught the years, so I'd just like to clarify, try https://stackoverflow.com/a/24120209/691294 first. This answer is for those cases where all else fails and you have to resort to a black magic solution (i.e. using magic strings).

edited Jun 03 '21 at 13:43

BenV

12,052
13
64
92

answered Jun 09 '14 at 12:21

flindeberg

4,887
1
24
37

have you tried to use .Include after where clause ? – Ramy M. Mousa Jun 09 '14 at 12:22
2

This is a fairly bizarre answer. – Daniel Kelley Jun 09 '14 at 12:24
1

@DanielKelley Eh? ObjectQuery? It's the way you do it sadly. – flindeberg Jun 09 '14 at 12:26
@RamyMohamed Ty for pointing it out, `Include` is only valid on object queries. – flindeberg Jun 09 '14 at 12:29
3

I Think this is good, but iirc, you don't need to have the first .include line, because it will be included as part of the second include line, so you just need the reflective call, and not the first quotes call. – McKay Jun 09 '14 at 12:36
@flindeberg Now the answer has been updated it makes much more sense. – Daniel Kelley Jun 09 '14 at 12:38
I received an error doing it the way as described by the accepted answer. This way did the trick. Thanks. – hanesjw Aug 02 '18 at 03:32
@hanesjw That is undoubtably the biggest problem with magic strings, they work :-/ – flindeberg Nov 12 '18 at 08:27

score 9 · Answer 4 · answered Oct 04 '17 at 11:44

9

This did the trick for me as @flindeberg said here . Just added checking if there are children in each parent item in the list

 List<WCF.DAL.Company> companies = dbCtx.Companies.Where(x=>x.CompanyBranches.Count > 0)
                            .Include(c => c.CompanyBranches)
                            .Include("CompanyBranches.Address")
                            .ToList();

answered Oct 04 '17 at 11:44

Tzvi Gregory Kaidanov

3,080
3
26
33

12

It can be better if you don't use magic strings: `.Include(${nameof(Companies.CompanyBranches)}.{nameof(CompanyBranch.Address)})` – Tonatio Sep 30 '18 at 17:45

How to include() nested child entity in linq

4 Answers4

Linked

Related