我知道有類似問(wèn)題的基調(diào)，我經(jīng)歷過(guò)很多，但仍然無(wú)法掌握如何在LINQ中做到這一點(diǎn)。我需要?jiǎng)?chuàng)建一個(gè)查詢來(lái)獲取數(shù)據(jù)，就像這個(gè)純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.StartedByGROUP 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.CreatedonORDER BY p.CreatedOn DESC這是我的linq版本，它主要工作，但我無(wú)法弄清楚“如果h.IsCompleted=0計(jì)數(shù)”部分。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, });問(wèn)題：1）如何讓“如果h.IsCompleted=0計(jì)數(shù)”工作？2） 我可以使用某種別名嗎？我的意思是，在這個(gè)查詢的實(shí)際版本中，還有另一個(gè)左連接，它導(dǎo)致我在grp中有2個(gè)名字和2個(gè)姓氏列。然后，如何將適當(dāng)?shù)淖侄畏峙浣o正確的輸出字段？我的意思是像下面這樣：join uuu in db.JDE_Users on p.StartedBy equals uuu.UserId into started
from star in started.DefaultIfEmpty()
2 回答
一只斗牛犬
TA貢獻(xiàn)1784條經(jīng)驗(yàn) 獲得超2個(gè)贊
我不確定這會(huì)有多大幫助，因?yàn)樗悄愕腟QL的翻譯，你的LINQ似乎與你的SQL無(wú)關(guān)，但我有：
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
TA貢獻(xiàn)2036條經(jīng)驗(yàn) 獲得超8個(gè)贊
很抱歉回答我自己的問(wèn)題，但我已經(jīng)找到了2的答案）。我只是不知道如何為一個(gè)屬性創(chuàng)建別名，當(dāng)我有2個(gè)具有相同名稱的屬性（例如“姓氏”）時(shí)。請(qǐng)?jiān)谙旅嬲业桨瑒e名以及NetMage解決的條件計(jì)數(shù)器部分的代碼：
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()
});
- 2 回答
- 0 關(guān)注
- 111 瀏覽
添加回答
舉報(bào)
0/150
提交
取消