CODEWITHSUNDEEP
c#linqlambda
coderwill
coderwill

Reputation: 844

Linq Lambda : Getting last user updated with group of same user using group by and count?

I want to get last user updated with a linq lambda expression using group by and the count of the remaining users. I don't know how I can do that.

here is my data :

userid | name | datetime            | isdelete
1      | abc  | 16-03-2017 15:45:59 | 0
1      | abc  | 16-03-2017 12:45:10 | 0 
2      | xyz  | 16-03-2017 15:45:59 | 0
1      | abc  | 16-03-2017 10:40:59 | 0

I want the result to look like this:

userid | name | datetime            | count 
1      | abc  | 16-03-2017 15:45:59 |   3   
2      | xyz  | 16-03-2017 15:45:59 |   1

Here the count for userid = 1 should be 3 as there are three records for that id in the table.

I have written this query, but it is getting all the records.

List<Users> UList = new List<Users>();
UList  = db.Users.Where(a => a.isdelete == false)
    .OrderByDescending(a => a.datetime)
    .Skip(skip)
    .Take(pageSize)
    .ToList();

Anyone know how I can get the data I want? Please let me know using linq lambda expression.

Upvotes: 0

Views: 141

Answers (2)

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50752

You need to group by user, than sort each group and take first from each group

var UList = (db.Users
                      .Where(a => a.isdelete == false)
                      .GroupBy(a => a.UserId)
                      .Select(g => new MyNewClass
                                   {
                                     Count = g.Count(), 
                                     User =  g.OrderByDescending(a => a.datetime).First()
                                   }
                      ))
                       .Skip(skip)
                       .Take(pageSize)
                       .ToList();

Upvotes: 1

MakePeaceGreatAgain
MakePeaceGreatAgain

Reputation: 37050

You forgot to group your data:

var result = db.Users.Where(a => !a.isdelete)
    .GroupBy(x => x.userid)
    .Select(x => new User 
        { 
            userid = x.Key, 
            name = x.Last().Name, 
            datetime = x.OrderByDescending(a => a.datetime).First().datetime, 
            count = x.Count() 
        });

EDIT: This might be not optimal considering the performance as the call to Last and OrderByAscending will both iterate the whole data. To overcome this a bit you may re-structure this query a bit:

var result = db.Users.Where(a => !a.isdelete)
    .GroupBy(x => x.userid)
    .Select(x => new 
        { 
            user = x.OrderByDescending(a => a.datetime).First(), 
            count = x.Count() 
        })
    .Select(x => new User {
        name = x.user.name,
        userid = x.user.userid,
        datetime = x.user.datetime,
        count = x.count
    });

Upvotes: 1

Related Questions