EF查询使用联接的条件包含 [英] EF Query with conditional include that uses Joins

问题描述

这是对另一个用户的问题的后续操作.我有5张桌子

This is a follow up to another user's question. I have 5 tables

• CompanyDetail
• 公司联系人FK to CompanyDetail
• CompanyContactsSecurity FK to CompanyContact
• UserDetail
• UserGroupMembership FK to UserDetail

• 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.

公司与联系人有1到1的关联，但是并不是每个用户都可以看到每个联系人.我的目标是获取每个公司的列表，而不考虑联系人的数量，但包括联系人数据.

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;

但是，当我需要获取所有公司的所有联系人时，这效率很低，因为我使用了For..Each循环并分别查询每个公司并更新我的视图模型. 问题:我该如何刺穿上面的allowedContacts变量并将其插入到此查询中:

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;

推荐答案

也许是这样的.

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
        };

这篇关于EF查询使用联接的条件包含的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！