EF Query with conditional include that uses Joins
- by makerofthings7
This is a follow up to another user's question. I have 5 tables
CompanyDetail
CompanyContacts FK to CompanyDetail
CompanyContactsSecurity FK to CompanyContact
UserDetail
UserGroupMembership FK to UserDetail
How do I return all companies and include the contacts in the same query? I would like to include companies that contain zero contacts.
Companies have a 1 to many association to Contacts, however not every user is permitted to see every Contact. My goal is to get a list of every Company regardless of the count of Contacts, but include contact data.
Right now I have this working query:
var userGroupsQueryable = _entities.UserGroupMembership
.Where(ug => ug.UserID == UserID)
.Select(a => a.GroupMembership);
var contactsGroupsQueryable = _entities.CompanyContactsSecurity;//.Where(c => c.CompanyID == companyID);
/// OLD Query that shows permitted contacts
/// ... I want to "use this query inside "listOfCompany"
///
//var permittedContacts= from c in userGroupsQueryable
//join p in contactsGroupsQueryable on c equals p.GroupID
//select p;
However this is inefficient when I need to get all contacts for all companies, since I use a For..Each loop and query each company individually and update my viewmodel. Question: How do I shoehorn the permittedContacts variable above and insert that into this query:
var listOfCompany = from company in _entities.CompanyDetail.Include("CompanyContacts").Include("CompanyContactsSecurity")
where company.CompanyContacts.Any(
// Insert Query here....
// b => b.CompanyContactsSecurity.Join(/*inner*/,/*OuterKey*/,/*innerKey*/,/*ResultSelector*/)
)
select company;
My attempt at doing this resulted in:
var listOfCompany = from company in _entities.CompanyDetail.Include("CompanyContacts").Include("CompanyContactsSecurity")
where company.CompanyContacts.Any(
// This is concept only... doesn't work...
from grps in userGroupsQueryable
join p in company.CompanyContactsSecurity on grps equals p.GroupID
select p
)
select company;
