LINQ to Entities 中具有左外连接的聚合函数

Question

我一直在这里查看相关的 LINQ 问题，试图解决这个问题，但在将我的 SQL 查询转换为等效的 LINQ to Entities 版本时遇到了一些问题。

select companies.CommpanyName,
       job.Position,
       count(offers.jobID) As Offered,
       job.Openings,
       job.Filled
from jobs
left outer join offers on  jobs.ID = offers.JobID
join membership.dbo.individuals on jobs.UserID = individuals.ID
join membership.dbo.companies on individuals.CompanyID = companies.ID
where jobs.Hidden = 0
group by offers.JobID,
         companies.CommpanyName,
         job.Position,
         job.Openings,
         job.Filled

我之前在 LINQ 中完成了左外连接 类似于这个例子，但我不确定如何将 count 和 group 语句与此结合起来以获得所需的结果：

    CompanyName    Position    Offered    Openings    Filled

1   Exmaple Co.    Job X       0          2           0
2   Example Co.    Job Y       4          6           3
3   Test Co.       Job Z       1          1           1

事实使查询变得更加复杂它需要利用两个独立的数据上下文。对于缺少示例代码，我深表歉意，但我真的不知道如何开始，我的 LINQ-fu 仍然很弱。

更新：

这是我在 Craig 的帮助下找到的解决方案，由于不幸的多上下文设置，不得不使用 LINQ to Objects，JobWithOfferCounts 不是实体对象：

IEnumerable<Job> t = context1.JobSet.Include("Offers").Include("Contacts").Where(j => j.Hidden == false);
IEnumerable <JobWithOfferCounts> r = (from j in t
                 join i in context2.IndividualSet on j.UserID equals i.ID
                 join c in context2.CompanySet on i.CompanyID equals c.ID
                 select new JobWithOfferCounts()
                 {
                    JobObject = j,
                    CompanyID = Convert.ToInt32(c.ID),
                    CompanyName = c.HostName,
                    OfferCount = j.offers.Count(o => o.Rejected == false),
                    FilledCount = j.offers.Count(o => o.Accepted == true),
                    PendingCount = j.offers.Count(o => o.Accepted == false && o.Rejected == false)
                 });            

        return r;

Answer 1

我不明白为什么您的查询中有individuals，或者为什么您按offers.JobID分组（与jobs.JobId不同）可能为 null，但这是第一个尝试：

var q = from c in Context.Companies     
        from i in c.Individuals
        from j in i.Jobs
        where j.Hidden == 0
        select new 
        {
            CompanyName = c.CompanyName,
            Position = j.Position,
            Offered = j.Offers.Count(),
            Openings = j.Openings,
            Filled = j.Filled
        };

使用 加入 LINQ to Entities 或LINQ to SQL。