Conditional counting in Linq's Group by multiple columns with left join

Question

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:

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

Here's my linq version, it mostly works but I can't figure out the 'Count if h.IsCompleted=0' part..

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

Questions:

1) how to get 'Count if h.IsCompleted=0' working?

2) Can I use aliases of some sort? I mean, in real version of this query there's also another left join join uuu in db.JDE_Users on p.StartedBy equals uuu.UserId into started from star in started.DefaultIfEmpty() 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:

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, // <-- 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()
}

Answer 1

Sorry for answering my own question, but I've already found an answer to 2). I just didn't know how to create alias for a property when I had 2 properties with equal name (e.g. "Surname"). Please find below the code containing aliases as well as conditional counter part solved by NetMage:

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 uu in db.JDE_Users on p.StartedBy equals uu.UserId into started
                             from star in started.DefaultIfEmpty()
                             join h in db.JDE_Handlings on p.ProcessId equals h.ProcessId into hans
                             from ha in hans.DefaultIfEmpty()
                             where p.TenantId == tenants.FirstOrDefault().TenantId && p.CreatedOn >= dFrom && p.CreatedOn <= dTo
                             group new { p, fin, star, ha }
                             by new {
                                 p.ProcessId,
                                 p.Description,
                                 p.StartedOn,
                                 p.StartedBy,
                                 p.FinishedOn,
                                 p.FinishedBy,
                                 p.PlannedFinish,
                                 p.PlannedStart,
                                 fin.Name,
                                 fin.Surname,
                                 StarterName = star.Name, // <-- Creating alias
                                 StarterSurname = star.Surname // <-- Creating alias
                             } 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,
                                 StartedByName = grp.Key.StarterName + " " + grp.Key.StarterSurname,
                                 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.Where(ph=>ph.ha.IsCompleted == null && ph.ha.HandlingId >0).Count().ToString()
                             });

Answer 2

I am not sure how helpful this will be because it is a translation of your SQL, and your LINQ doesn't seem to be related to your SQL, but I have:

var ans = from p in db.JDE_Processes
          join uuu in db.JDE_Users on p.FinishedBy equals uuu.UserId into uuuj
          from uuu in uuuj.DefaultIfEmpty()
          join h in db.JDE_Handlings on p.ProcessId equals h.ProcessId into hj
          from h in hj
          group new { p, h } by new { p.ProcessId, p.Description, p.StartedOn, p.StartedBy, p.FinishedOn, p.FinishedBy, p.PlannedFinish, p.PlannedStart } into phg
          select new {
              phg.Key.ProcessId,
              phg.Key.Description,
              phg.Key.StartedOn,
              phg.Key.StartedBy,
              phg.Key.FinishedOn,
              phg.Key.FinishedBy,
              phg.Key.PlannedFinish,
              phg.Key.PlannedStart,
              HandlingStatus = phg.Where(ph => ph.h.IsCompleted == null).Count()
          };