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;