Get Last Record from Multiple Records and save result in LINQ List

Question

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.

-------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:

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?

Christos · Accepted Answer · 2015-12-21T18:33:56.190

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.
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.

edited Dec 21 '15 at 18:33

answered Dec 21 '15 at 16:54

Christos

53,228
8
76
108

It gives me error when I try to use the List of my Class `Cannot implicitly convert type 'System.Collections.Generic.List>' to 'System.Collections.Generic.List` – Muhammad Arslan Jamshaid Dec 21 '15 at 17:25
Then you have in the Select clause use the User_DTO class. I will update my post and if you have any question please let me know. – Christos Dec 21 '15 at 17:27
I did use the `User_DTO` class, but the problem is when I use `var` it works fine, but if I use `List` it gives me this error – Muhammad Arslan Jamshaid Dec 21 '15 at 17:28
If you use the `User_DTO`, then the result of the query it would a `List`. I can't get what you mean by saying if I yse `List`. Where do you want to use this? – Christos Dec 21 '15 at 17:30
Somehow, the result of above query returns a List of `IGrouping` how can I cast this result into my List of User_DTO ? – Muhammad Arslan Jamshaid Dec 21 '15 at 17:33
2

Did you make the mentioned change, `.Select(gr=>new User_DTO{...}` and it still returns a List of `IGrouping`? A sequence of `IGrouping` is returned by the `GroupBy`. Then using the `Select` you create another sequence, a sequence of `User_DTO` objects and last but not least calling the `ToList` at the end you create a `List` based on this sequence. – Christos Dec 21 '15 at 17:35
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/98590/discussion-between-programer-and-christos). – Muhammad Arslan Jamshaid Dec 21 '15 at 17:46
But why do you need to call `AsEnumerable`? – ocuenca Dec 21 '15 at 18:49
Because calling the `AsEnumerable` you will fetch the data in memory and you would do there the grouping and the further processing, using linq to entities. This is needed, because we can't a use lambda expression with a statement body, in LINQ to SQL. Please check this link http://stackoverflow.com/questions/3311244/understanding-asenumerable-in-linq-to-sql – Christos Dec 21 '15 at 19:25
Thanks for the info @Christos, so right know I have my doubts if my queries work or not, because I think the `let` expression is included inside the `select` expression, but I don't remember having troubles with something like that in the past. When I have time I'm going to test it. – ocuenca Dec 21 '15 at 19:55
@octavioccl You are welcome ! You shouldn't have any doubt. If I was to write the linq query using the query syntax, I would have picked certainly the let keyword. I just wanted to write this query using the fluent syntax using method calls. By the way, all the queries we write in query sytax during the compilation are transformed to method calls, since CLR is not aware of query syntax. If you would like, I would suggest you read this post, http://stackoverflow.com/questions/214500/linq-fluent-and-query-expression-is-there-any-benefits-of-one-over-other. – Christos Dec 22 '15 at 12:24
Thanks again @Christos, I tested my first query and it works on the server side. I didn't have to call `AsEnumerable` to execute the query using Linq to Objects, it works well using Linq to Entities. I'm going to update my answer to show the results. – ocuenca Dec 22 '15 at 13:16
As you can see in my answer to avoid execute all your query in memory you can do two `Select` to get the expected result. – ocuenca Dec 22 '15 at 13:49

ocuenca · Answer 2 · 2015-12-22T13:36:35.793

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

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:

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:

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]

If there's no datetime column, you can follow this pattern to order by descending ID if you have one (assuming an autonumbered identity column) — Tim, Dec 21 '15 at 17:02
Well, you'll need to create a custom class to save your result. I recommend you to search in internet what is a DTO. — ocuenca, Dec 21 '15 at 17:32

Get Last Record from Multiple Records and save result in LINQ List

2 Answers2

Update