Linq 组中的条件计数按具有左联接的多个列进行

我知道有类似问题的基调,我经历过很多,但仍然无法掌握如何在LINQ中做到这一点。我需要创建一个查询来获取数据,就像这个纯SQL查询一样:


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

这是我的linq版本,它主要工作,但我无法弄清楚“如果h.IsCompleted=0计数”部分。


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,

    });

问题:

1)如何让“如果h.IsCompleted=0计数”工作?

2) 我可以使用某种别名吗?我的意思是,在这个查询的实际版本中,还有另一个左连接,它导致我在grp中有2个名字和2个姓氏列。然后,如何将适当的字段分配给正确的输出字段?我的意思是像下面这样:join uuu in db.JDE_Users on p.StartedBy equals uuu.UserId into started                             from star in started.DefaultIfEmpty()



犯罪嫌疑人X
浏览 100回答 2
2回答

一只斗牛犬

我不确定这会有多大帮助,因为它是你的SQL的翻译,你的LINQ似乎与你的SQL无关,但我有: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()          };

慕桂英3389331

很抱歉回答我自己的问题,但我已经找到了2的答案)。我只是不知道如何为一个属性创建别名,当我有2个具有相同名称的属性(例如“姓氏”)时。请在下面找到包含别名以及NetMage解决的条件计数器部分的代码:var items = (from p in db.JDE_Processes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;join uuu in db.JDE_Users on p.FinishedBy equals uuu.UserId into finished&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from fin in finished.DefaultIfEmpty()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;join uu in db.JDE_Users on p.StartedBy equals uu.UserId into started&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from star in started.DefaultIfEmpty()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;join h in db.JDE_Handlings on p.ProcessId equals h.ProcessId into hans&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from ha in hans.DefaultIfEmpty()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where p.TenantId == tenants.FirstOrDefault().TenantId && p.CreatedOn >= dFrom && p.CreatedOn <= dTo&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;group new { p, fin, star, ha }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;by new {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;p.ProcessId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;p.Description,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;p.StartedOn,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;p.StartedBy,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;p.FinishedOn,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;p.FinishedBy,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;p.PlannedFinish,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;p.PlannedStart,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fin.Name,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;fin.Surname,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;StarterName = star.Name, // <-- Creating alias&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;StarterSurname = star.Surname // <-- Creating alias&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;} into grp&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;orderby grp.Key.ProcessId descending&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;select new Process&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ProcessId = grp.Key.ProcessId,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Description = grp.Key.Description,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;StartedOn = grp.Key.StartedOn,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;StartedBy = grp.Key.StartedBy,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;StartedByName = grp.Key.StarterName + " " + grp.Key.StarterSurname,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FinishedOn = grp.Key.FinishedOn,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FinishedBy = grp.Key.FinishedBy,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FinishedByName = grp.Key.Name + " " + grp.Key.Surname,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PlannedStart = grp.Key.PlannedStart,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PlannedFinish = grp.Key.PlannedFinish,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;HandlingStatus = grp.Where(ph=>ph.ha.IsCompleted == null && ph.ha.HandlingId >0).Count().ToString()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;});
打开App,查看更多内容
随时随地看视频慕课网APP