title: Conditional counting in Linq's Group by multiple columns with left join
body: <p>I know there's a tone of similar questions and I've been through a lot of them, but still can't grasp how to do it in LINQ. I need to create a query fetching data as this pure SQL query: </p>
  
  <pre><code>SELECT p.ProcessId, p.Description, p.StartedOn, p.StartedBy, uuus.Name + ' ' + uuus.Surname AS StartedByName, p.FinishedOn, p.FinishedBy, uuu.Name + ' ' + uuu.Surname as FinishedByName, p.PlannedFinish, p.PlannedStart, COUNT(CASE WHEN h.IsCompleted IS NULL AND h.HandlingId IS NOT NULL THEN 1 END)
  FROM JDE_Processes p LEFT JOIN JDE_Users uuu ON p.FinishedBy = uuu.UserId LEFT JOIN JDE_Handlings h ON h.ProcessId=p.ProcessId LEFT JOIN JDE_Users uuus ON uuus.UserId=p.StartedBy
  GROUP BY p.ProcessId, p.Description, p.StartedOn, p.StartedBy,uuus.Name + ' ' + uuus.Surname, p.FinishedOn, p.FinishedBy, uuu.Name + ' ' + uuu.Surname, p.PlannedFinish, p.PlannedStart, p.Createdon
  ORDER BY p.CreatedOn DESC
  </code></pre>
  
  <p>Here's my linq version, it mostly works but I can't figure out the 'Count if h.IsCompleted=0' part..</p>
  
  <pre><code>var items = (from p in db.JDE_Processes
      join uuu in db.JDE_Users on p.FinishedBy equals uuu.UserId into finished
      from fin in finished.DefaultIfEmpty()
      join h in db.JDE_Handlings on p.ProcessId equals h.ProcessId into hans
      from ha in hans.DefaultIfEmpty()
      group new { p, fin }
      by new {
          p.ProcessId,
          p.Description,
          p.StartedOn,
          p.StartedBy,
          p.FinishedOn,
          p.FinishedBy,
          p.PlannedFinish,
          p.PlannedStart,
          fin.Name,
          fin.Surname
      } into grp
      orderby grp.Key.ProcessId descending
      select new Process
      {
          ProcessId = grp.Key.ProcessId,
          Description = grp.Key.Description,
          StartedOn = grp.Key.StartedOn,
          StartedBy = grp.Key.StartedBy,
          FinishedOn = grp.Key.FinishedOn,
          FinishedBy = grp.Key.FinishedBy,
          FinishedByName = grp.Key.Name + " " + grp.Key.Surname,
          PlannedStart = grp.Key.PlannedStart,
          PlannedFinish = grp.Key.PlannedFinish,
          HandlingStatus = grp.Count().ToString()
      });
  </code></pre>
  
  <p>Questions:</p>
  
  <p>1) how to get 'Count if h.IsCompleted=0' working?</p>
  
  <p>2) Can I use aliases of some sort? I mean, in real version of this query there's also another left join <code>join uuu in db.JDE_Users on p.StartedBy equals uuu.UserId into started
                               from star in started.DefaultIfEmpty()</code>
  It causes I have 2 Name and 2 Surname columns in grp. How I can then assign proper field to proper output field? I mean like below:</p>
  
  <pre><code>select new Process
  {
      ProcessId = grp.Key.ProcessId,
      Description = grp.Key.Description,
      StartedOn = grp.Key.StartedOn,
      StartedBy = grp.Key.StartedBy,
      StartedByName = grp.Key.Name + " " + grp.Key.Surname, // &lt;-- how will it know which Name field to use? 
      FinishedOn = grp.Key.FinishedOn,
      FinishedBy = grp.Key.FinishedBy,
      FinishedByName = grp.Key.Name + " " + grp.Key.Surname,
      PlannedStart = grp.Key.PlannedStart,
      PlannedFinish = grp.Key.PlannedFinish,
      HandlingStatus = grp.Count().ToString()
  }
  </code></pre>