Getting list of child entity nested several levels with LINQ

Question

I have entities that are nested in this order:

RootDomain
Company
CompaniesHouseRecord
CompanyOfficer

When given a RootDomain I want to create a list of all CompanyOfficers that have an email address but I am not sure how to do this.

Here Is my non-working attempt:

RootDomain rd = db.RootDomains.Find(123);
List<CompanyOfficer> col = rd.Companies.Where(x => x.CompaniesHouseRecords.Any(chr => chr.CompanyOfficers.Any(co => co.Email != null)))
                        .Select(x => x.CompaniesHouseRecords.Select(chr => chr.CompanyOfficers)).ToList();

I am obviously way off the mark here. Can someone show me or point me to the correct method for dong this?

You might want to take a look at *Include()*. See [here](http://stackoverflow.com/a/5718935/1525840). Keep in mind that there are **two** different versions. The old one when you refer to the subfield by string and the new one where you use lambda expression. — Konrad Viltersten, Sep 30 '16 at 07:23

Nsevens · Accepted Answer · 2016-09-30T07:56:23.317

4

Like this:

RootDomain rd = db.RootDomains.Find(123);
List<CompanyOfficer> col = rd.Companies
  .SelectMany(c => c.CompaniesHouseRecords)
  .SelectMany(c => c.CompanyOfficers)
  .Where(o => null != o.Email).ToList();

edited Sep 30 '16 at 07:56

answered Sep 30 '16 at 07:27

Nsevens

2,588
1
17
34

score 1 · Answer 2 · answered Sep 30 '16 at 07:47

Someone answered before me, but I can show something different, which can be more convenient for someone who is used to DB requests.

Using LINQ, you can do this type of request:

var officersWithEmail = from company in rd.Companies
                        from companiesHouseRecord in company.CompaniesHouseRecords
                        from companyOfficer in companiesHouseRecord.CompanyOfficers
                        where (companyOfficer.Email != null)
                        select companyOfficer;

Some people will find it more readable.

If you want to obtain a List<> as output, just use .ToList on the query.

Getting list of child entity nested several levels with LINQ

2 Answers2