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

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you? Your LINQ has a left join for JDE_Handlings but your SQL shows an inner join - which is correct? — NetMage, Feb 11 '19 at 23:27
Your SQL has `1 ELSE 0` both of which count, so your test does nothing. Can you show the SQL that your LINQ is actually supposed to be translating? — NetMage, Feb 11 '19 at 23:37

NetMage · Accepted Answer · 2019-02-12T19:42:18.070

1

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

edited Feb 12 '19 at 19:42

answered Feb 11 '19 at 23:41

NetMage

26,163
3
34
55

thanks for answers. Indeed my sql string contained errors, sorry, I haven't checked it before posting. I updated my original question so now the sql is correct. I just had to change JOIN to JDE_Handlings as LEFT JOIN and correct COUNT condition as it counted all rows as you noted. My Linq query is supposed to be related to that sql string, the whole point of this question is that I'm trying to get there but can't, my knowledge of linq is just too limited. – robs23 Feb 12 '19 at 13:58
@robs23 I updated my answer to match your SQL, but your LINQ still has reference/questions to fields not in the SQL (e.g. `Surname`). – NetMage Feb 12 '19 at 19:42
Once more thank you. I updated my sql one more time, sorry, it's embarrassing really. I added 1 more condition for Count but has already tested that phg.Where(ph=>ph.h.IsCompleted == null && ph.h.HandlingId >0).Count() does the job. I also added another left join to JDE_Users to get Name and Surname value to StartedBy key from JDE_Processes. First part of my question is already solved thanks to you, I'm looking for an answer for 2nd part of my question now: how to get StartedByName – robs23 Feb 13 '19 at 08:27

score 0 · Answer 2 · answered Feb 13 '19 at 08:51

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

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

2 Answers2