EF Query with conditional include that uses Joins

Question

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;

You said that your goal is to get a list of every Company regardless of the count of contacts. But it can't be that simple or you'd just get the list of companies regardless of contacts. Can you supply a little more detail (in words) on how you're filtering the companies? — Ann L., Nov 18 '11 at 23:01
@AnnL. Companies aren't filtered at all. I want to disable lazy loading and allow a single SQL query give me results that list all companies, and related contacts but the restriction is that not every user is permitted to see every contact. — makerofthings7, Nov 18 '11 at 23:04

score 1 · Answer 1 · edited Nov 19 '11 at 01:46

1

Perhaps something like this.

var q = from company in _entities.CompanyDetail
        where 
        (from c in userGroupsQueryable
        join p in contactsGroupsQueryable on c equals p.GroupID
        where company.CompanyContacts.Any(cc => cc.pkCompanyContact == p.fkCompanyContact)
        select p
        ).Any()
        select new
        {
          Company = company,
          Contacts = company.CompanyContacts
        };

edited Nov 19 '11 at 01:46

makerofthings7

60,103
53
215
448

answered Nov 18 '11 at 23:31

Magnus

45,362
8
80
118

I just spent a few hours experimenting. It seems that the returned `Company` variable is filtered correctly, meaning only companies with contacts the user is permitted to see is shown; however, when I enumerate of over the `Contact` variable, all contacts are shown and none are filtered. How can I expand the "where" logic to also apply to the contacts that are returned? The ideal situation is where I get all companies, and filter the contacts (kinda the inverse of what is happening here) – makerofthings7 Nov 19 '11 at 02:40
+1 you got me going in the right direction. I just figured out that I need to place that join into the select statement to make it work. The results are fast, but I don't know how efficient it is under the hood. – makerofthings7 Nov 19 '11 at 03:16
@makerofthings7 Great, looking at you'r query I don't see any performance problems. If you want to analyse the generated SQL you can use a program like [Linqpad](http://www.linqpad.net/). – Magnus Nov 19 '11 at 15:52

score 0 · Answer 2 · answered Nov 19 '11 at 03:18

The following code queries all companies, and appends only the contacts the user is permitted to see. I don't know how efficient this is, or if there is a way to make it faster, but it works.

 var userGroupsQueryable = _entities.UserGroupMembership.Where(ug => ug.UserID == UserID)
                                   .Select(a => a.GroupMembership);

 var  contactsGroupsQueryable = _entities.CompanyContactsSecurity;

 var listOfCompanies =
            from company in _entities.CompanyDetail
            select new
            {
                Company = company,
                Contacts = (from c in userGroupsQueryable
                            join p in contactsGroupsQueryable on c equals p.GroupID
                            where company.CompanyContacts.Any(cc => cc.CompanyID == p.CompanyID)
                            select p.CompanyContacts)
            };

EF Query with conditional include that uses Joins

2 Answers2

Linked