Entity Framework Linq Query: How to Where on Multiple Nav Properties and Select from 3rd Nav Property

c# entity-framework-core linq linq-to-sql

Question

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

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

I have the following working Query:

        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 can I replace the "(from in where select).Any()" statements to use Linq's lambda syntax? Must result in 1 SQL statement. Can use left outter joins, or nested select.

1
0
12/18/2017 2:29:30 PM

Accepted Answer

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 by using ReSharper's command "Convert LINQ to method chain"

1
12/18/2017 2:37:03 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow