title: Aggregate functions with a left outer join in LINQ to Entities
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I've done left outer joins in LINQ before <a href="https://stackoverflow.com/questions/1122942/linq-to-sql-left-outer-join-with-multiple-join-conditions">similar to this example</a> but I'm not sure how to combine the <code>count</code> and <code>group</code> statements with this to get the desired result:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>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:</p>
  
  <pre><code>IEnumerable&lt;Job&gt; t = context1.JobSet.Include("Offers").Include("Contacts").Where(j =&gt; j.Hidden == false);
  IEnumerable &lt;JobWithOfferCounts&gt; 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 =&gt; o.Rejected == false),
                      FilledCount = j.offers.Count(o =&gt; o.Accepted == true),
                      PendingCount = j.offers.Count(o =&gt; o.Accepted == false &amp;&amp; o.Rejected == false)
                   });            
  
          return r;
  </code></pre>