CODEWITHSUNDEEP
c#.netentity-frameworklinqentity-framework-6
Guerrilla
Guerrilla

Reputation: 14926

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?

Upvotes: 2

Views: 625

Answers (2)

nsevens
nsevens

Reputation: 2835

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

Upvotes: 4

Vasilievski
Vasilievski

Reputation: 863

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.

Upvotes: 1

Related Questions