从多条记录中获取最后一条记录，并将结果保存在LINQ列表中

本文关键字：记录 结果 保存 存在 列表 LINQ 一条 获取 最后 | 更新日期: 2023-09-27 18:27:34

我有一个表"状态Which has multiple status of用户. I just want to read the last状态of every用户"。我怎么能在Lambda Linq到SQL中实现这一点呢。

-------Status-------------
|   user1, Stats_Active   |
|   user1, Stats_InActive |
|   user1, Stats_Deleted  |
|   user2, Stats_Login    |
|   user2, Stats_Logout   |
|   user2, Stats_Edited   |
________________________

我想得到以下输出：

User1, Status_Delete
User2,Status_edited

这是我的Linq-to-SQL查询：

EntityName db = new EntityName();
var result = db.Status.OrderByDescending(k=> k.DateTime).List()

这只是按降序返回整个列表。如何实现我想要的输出？

从多条记录中获取最后一条记录，并将结果保存在LINQ列表中

我认为您需要这样的东西：

var result = db.Status
               .AsEnumerable()
               .GroupBy(s=>s.UserId)
               .Select(gr=>
               {
                   // Generally you should avoid use the First
                   // and you should use the FirstOrDefault
                   // but in this context is safe to use First.
                   var mostRecentComplaint = gr.OrderByDescending(k=> k.DateTime)
                                               .First();
                   return new Complaint_DTO
                   {
                       ComplaintId = gr.Key,
                       Status = mostRecentComplaint.Status,
                       CreatedDate = mostRecentComplaint.CreatedDate
                   };
               }).ToList();
  • 首先，我们根据UserId对项目进行分组。因此，所有具有相同UserId的记录都属于同一组
  • 然后通过递减DateTime对每个组的结果进行排序，并获得每个组的第一个项目，我们就得到了预期的结果

您需要按UserId分组，然后按DateTime属性排序：

var result =(from s in db.Status
             group s by s.UserId into g
             let first=g.OrderByDescending(s=>s.DateTime).FirstOrDefault()
             select new { userId= g.Key, Status= first.Status, CreatedDate=first.CreatedDate}).ToList();

更新

我使用LinqPad测试了我的查询，它被翻译成这个lambda表达式：

Status
   .GroupBy (a => a.UserId)
   .Select (
      g => 
         new  
         {
            g = g, 
            first = g.OrderBy (a => a.DateTime).FirstOrDefault ()
         }
   )
   .Select (
      temp0 => 
         new  
         {
            Key = temp0.g.Key, 
            Status= temp0.first.Status,
            CreatedDate=temp0.first.CreatedDate
         }
   )

它在这个SQL查询的末尾被翻译：

SELECT [t1].[UserId] AS [UserId], (
    SELECT [t3].[Status], [t3].[DateTime]
    FROM (
        SELECT TOP (1) [t2].[Status]
        FROM [Status] AS [t2]
        WHERE (([t1].[UserId] IS NULL) AND ([t2].[UserId] IS NULL)) OR (([t1].[UserId] IS NOT NULL) AND ([t2].[UserId] IS NOT NULL) AND ([t1].[UserId] = [t2].[UserId]))
        ORDER BY [t2].[DateTime]
        ) AS [t3]
    ) AS [Status],(
    SELECT [t5].[DateTime]
    FROM (
      SELECT TOP (1) [t4].[DateTime]
      FROM [Application] AS [t4]
      WHERE (([t1].[UserId] IS NULL) AND ([t4].[UserId] IS NULL)) OR (([t1].[UserId] IS NOT NULL) AND ([t4].[UserId] IS NOT NULL) AND ([t1].[UserId] = [t4].[UserId]))
      ORDER BY [t4].[DateTime]
      ) AS [t5]
    ) AS [DateTime]
FROM (
    SELECT [t0].[UserId]
    FROM [Application] AS [t0]
    GROUP BY [t0].[UserId]
    ) AS [t1]
相关文章：