Entity Framework Linq Query: how to find several Nav properties and select from the 3rd Nav property

Question

I have the following models with nav settings set in the Entity Framework Core:

• CRMLocations (one-to-many) CRMPities
• CRMeoples (one to many) CRMEmails
• CRMPeoples (one to many) CRMPhones

I have the following working request:

var iqable = _crmDbContext.CRMPeoples .Where(p => p.Name.ToLower().Contains(query) || (from e in p.CRMEmails where e.EmailAddress.ToLower().Contains(query) select e).Any() || (from h in p.CRMPhones where h.PhoneNumberNormalized.Contains(query) select h).Any()) .Select(p => new CRMSearchResultDTO() { PersonName = p.Name, LocationName = p.CRMLocations.Name, }); 

How to replace the expression "(from in where select) .Any ()" to use the Linq lambda syntax? Must result in 1 SQL statement. Can use left stitch connection or nested selection.

Answer 1

 var iqable = _crmDbContext.CRMPeoples .Where(p => p.Name.ToLower().Contains(query) || p.CRMEmails.Where(e => e.EmailAddress.ToLower().Contains(query)).Any() || p.CRMPhones.Where(h => h.PhoneNumberNormalized.Contains(query)).Any()) .Select(p => new CRMSearchResultDTO() { PersonName = p.Name, LocationName = p.CRMLocations.Name, }); 

I got this code using the ReSharper command "Convert LINQ to Method Chain"