Aggregate functions with a left outer join in LINQ to Entities

Question

I've been looking through related LINQ questions here trying to figure this one out, but I'm having some trouble converting a SQL query of mine to the equivalent LINQ to Entities version.

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

I've done left outer joins in LINQ before similar to this example but I'm not sure how to combine the count and group statements with this to get the desired result:

    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

The query is further complicated by the fact that it needs to utilize two separate data contexts. I apologize for the lack of example code, but I'm really not sure how to start this, my LINQ-fu is still weak.

Update:

This is the solution I arrived at with Craig's help, had to use LINQ to Objects because of the unfortunate multiple context setup, JobWithOfferCounts is not an entity object:

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

I can't see why you have individuals in your query, or why you group by offers.JobID when it (unlike jobs.JobId) could be null, but here's a first stab:

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

It's rarely correct to use join in LINQ to Entities or LINQ to SQL.