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

I have a table 'StatusWhich has multiple status ofUser. I just want to read the lastStatusof everyuser`. How could I achive that in Lambda Linq to SQL.

我有一个表'StatusWhich有多个状态的用户。我只想阅读每个用户的lastStatus。我怎么能在Lambda Linq中实现SQL。

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

I want to get the following output:

我想获得以下输出:

User1, Status_Delete
User2,Status_edited

This is my Linq to SQL Query:

这是我的Linq to SQL Query:

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

This just returns the whole list in Descending order. How can I achieve my desired output?

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

2 个解决方案

#1

I think that you need something like this:

我想你需要这样的东西:

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(); 

• First we group by our items by the UserId. So all the records that have the same UserId goes to the same group.

首先,我们按UserId对项目进行分组。因此,具有相同UserId的所有记录都将转到同一组。

• Then Ordering the result of each group by descending DateTime and getting the first item of each of these groups, we get the expected result.

然后通过降序DateTime对每个组的结果进行排序并获取每个组的第一个项,我们得到预期的结果。

#2

You need to group by UserId and then order your groups by DateTime property:

您需要按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();

Update

I tested my query using LinqPad, and it was translated to this lambda expression:

我使用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
         }
   )

Which is translated at the end to this SQL query:

哪个在此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]