C# lambda 内连接与最大值

Question

我创建了这个sql查询，但很难将其转换为linq lambda。我对 linq 相当陌生。

Select t1.* Fom msgs as t1
Inner join(
    Select fromUser, MAX(ts) as mts
    From msgs
    Where toUser='aUserName' AND isDeleted='0'
    Group By fromUser
)t2 On (t2.fromUser=t1.fromUser and t1.ts = t2.mts) Order By ts desc;

Answer 1

你可以用OrderByDescending（linq扩展）和GroupBy实现同样的事情。

var messages = msg.ToList();
var results = messages.Where(x=> x.toUser = "aUserName" AND isDeleted="0")
    .GroupBy(x=>x.fromUser)
    .Select(x=>
     { 
         var maxitem = x.OrderByDescending(y=>y.ts).First();
         return new 
         {
              fromUser = maxItem.fromUser,
              ts = maxitem.ts,
              ... // remaining properties.
         }
     })
    .ToList();