如何使用linq to sql从组中选择值

Question

谁能帮我在c#中编写与linq等价的sql

select 
COUNT(ot.UserId) as orderCount, SUM(ot.TotalAmount) as TotalAmount,
MAX(u.UserName) as UserName, MAX(c.name) as ClientName
from OrderTemplate ot
join User u
on u.userid = ot.UserId
join Client c
on u.ClientID = c.clientid
group by ot.UserId

我所做的是，

from ot in dbContext.OrderTemplates
join user in dbContext.Users on ot.UserId equals user.UserID
join client in dbContext.Clients on user.ClientID equals client.ClientID
group ot by ot.UserId into g
select new 
{ 
    //UserName = need to pick this from user table
    //ClientName = need to pick this from client table
    OrderCount = g.Count(),
    TotalAmount = g.Sum(x=> x.TotalAmount)
};

Answer 1

如果用户名和客户名相同(实际上每个订单有一个客户端和一个用户)，最好按多个键分组:

from ot in dbContext.OrderTemplates
join u in dbContext.Users on ot.UserId equals u.UserID
join c in dbContext.Clients on u.ClientID equals c.ClientID
group ot by new { ot.UserId, u.UserName, c.ClientName } into g
select new 
{ 
    UserName = g.Key.UserName,
    ClientName = g.Key.ClientName,
    OrderCount = g.Count(),
    TotalAmount = g.Sum(x=> x.TotalAmount)
};

如果它们不是，或者如果你想完全像你的sql那样，那么在实例化匿名对象时，不要使用具有多个属性的键:

Username = g.Max(item => item.UserName) // will just use string comparison to select the "max"