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

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.

Hemid Abbasov · Accepted Answer · 2017-12-18 14:37:03Z

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

answered Dec 18, 2017 at 14:37

Hemid Abbasov

1752 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ttugates Over a year ago

Oh man, that was so straight forward.. :) Swore that generated a ton of sql statements last week. Also, ReSharper has "Convert LINQ to method chain." - Nice. My very original code was SQL with Left Joins and a bit more complex, "Convert LINQ to method chain." would be very helpful..

Hemid Abbasov Over a year ago

And there is also: instead of writing p.CRMEmails.Where(e => e.EmailAddress.ToLower().Contains(query)).Any() you can write p.CRMEmails.Any(e => e.EmailAddress.ToLower().Contains(query)) (it is advised by ReSharper)

ttugates Over a year ago

Thx, thats how I would normally write it.. Dunno what I was thinking or why I had problems.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related