title: Left outer join and multiple counts SQL to LINQ
body: <p>How would this query using an inner join, left outer join, group by and two counts be converted to linq?</p>
  
  <pre><code>SELECT
      c.EndowmentID,
      COUNT(DISTINCT f.CriterionID) AS RequiredCriteria,
      COUNT(r.ChoiceID) AS Response
  FROM
      Criteria c
  INNER JOIN
      Filters f
  ON
      c.ID = f.CriterionID
  LEFT OUTER JOIN 
      Responses r
  ON
      f.ChoiceID = r.ChoiceID
  WHERE
      f.IsRequirement = 1
  GROUP BY
      c.EndowmentID;
  </code></pre>
  
  <p>This is what I have done so far:</p>
  
  <pre><code>            var result =
                  from c in context.Criteria
                  join f in context.Filters on c.ID equals f.CriterionID
                  join r in context.Responses on f.ChoiceID equals r.ChoiceID into resfil
                  from rf in resfil.DefaultIfEmpty()
                  group rf by c.EndowmentID into grouped
                  select new 
                  {
                      EndowmentID = grouped.Key,
                      Requirements = grouped.Count(t=&gt;t.CriterionID),
                      Response = grouped.Count(t=&gt;t.ChoiceID)
                  };
  </code></pre>