Guerrilla Guerrilla - 2 months ago 14
C# Question

Getting list of child entity nested several levels with LINQ

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?

Answer

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();