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
– Evonet, Commented Jun 9, 2014 at 12:20
Possible duplicate of How to include a child object's child object in Entity Framework 5 — Michael Freidgeim
– Michael Freidgeim, Commented Sep 4, 2018 at 12:55

d219 · Accepted Answer · 2019-02-07 02:44:09Z

66

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.

edited Feb 7, 2019 at 2:44

d219

2,8757 gold badges34 silver badges39 bronze badges

answered Jun 9, 2014 at 12:22

Mattias Åslund

3,9252 gold badges21 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jesse · Accepted Answer · 2022-11-16 09:06:58Z

56

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, 2022 at 9:06

Jesse

436 bronze badges

answered Jul 3, 2019 at 16:14

Menasheh

3,7375 gold badges35 silver badges52 bronze badges

3 Comments

CusterN Over a year ago

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.

user2210411 Over a year ago

I agree, this works nicely and is somewhat clean.

cikatomo Over a year ago

how do you include another ThenInclude if Quotes have another collection besides QuoteItems

BenV · Accepted Answer · 2021-06-03 13:43:40Z

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 3, 2021 at 13:43

BenV

12.5k13 gold badges68 silver badges96 bronze badges

answered Jun 9, 2014 at 12:21

flindeberg

5,0471 gold badge27 silver badges37 bronze badges

8 Comments

Ramy Moussa Over a year ago

have you tried to use .Include after where clause ?

Daniel Kelley Over a year ago

This is a fairly bizarre answer.

flindeberg Over a year ago

@DanielKelley Eh? ObjectQuery? It's the way you do it sadly.

flindeberg Over a year ago

@RamyMohamed Ty for pointing it out, Include is only valid on object queries.

McKay Over a year ago

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.

|

Tzvi Gregory Kaidanov · Accepted Answer · 2017-10-04 11:44:59Z

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 4, 2017 at 11:44

Tzvi Gregory Kaidanov

3,1683 gold badges28 silver badges33 bronze badges

1 Comment

Tonatio Over a year ago

It can be better if you don't use magic strings: .Include(${nameof(Companies.CompanyBranches)}.{nameof(CompanyBranch.Address)})

Collectives™ on Stack Overflow

How to include() nested child entity in linq

4 Answers 4

Comments

3 Comments

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related