How to get dictinct userid with order by close using Linq

Question

I have table called info, and its data something like this:

FKUserID	CompletedTime	Type
1	2021-03-10 12:56:00.423	5
245	2021-03-10 12:46:46.977	5
1	2021-03-10 12:44:53.683	5
1	2021-03-10 12:40:54.733	5
1	2021-03-10 12:35:26.307	5
245	2021-03-10 11:11:33.887	5
245	2021-03-10 10:18:11.403	5

I need to get distinct userID data, and also with the maximum completed time of theirs CompletedTime column

expected output is:

FKUserID	CompletedTime	Type
1	2021-03-10 12:56:00.423	5
245	2021-03-10 12:46:46.977	5

I need to do this using Linq query How can I do this, I did it using SQl, need it using Linq

SELECT FKUserID , MAX(CompletedTime) 
from Info 
where cast(CompletedTime as date) = '2021-03-10' 
and Status = 5 
GROUP BY FKUserID;

i think your answer is already here: https://stackoverflow.com/questions/157786/how-do-i-get-the-max-row-with-a-group-by-in-linq-query — MorenajeRD, Mar 10 '21 at 15:20

score 1 · Answer 1 · edited Mar 10 '21 at 20:47

1

Something like this

var d = new DateTime(2021, 3, 10);
var d2 = d.AddDays(1);

dbContext.Infos
  .Where(i => i.Status == 5 && i.CompletedTime >= d && i.CompletedTime < d2)
  .GroupBy(i => i.FkUserId)
  .Select(g => new { 
    FkUserId = g.Key, 
    CompletedTime = g.Max(g2 => g2.CompletedTime)
  } 
);

edited Mar 10 '21 at 20:47

Meysam Asadi

6,438
3
7
17

answered Mar 10 '21 at 15:26

Caius Jard

72,509
5
49
80

to where may I add status = 5 logic – thomsan Mar 10 '21 at 16:30

score 1 · Answer 2 · answered Mar 10 '21 at 15:38

You can use the following query. The following query implements your SQL query.

var query = context.Info.GroupBy(a => a.FKUserID)
     .Join(context.Info,
      left => left.Key,
      right => right.FKUserID,
      (left, right) => new { left, right })
      .Where(a => a.right.CompletedTime.ToShortDateString() == "2021-03-10" && a.right.Status == 5)
      .Select(a => new
      {
         FKUserID = a.left.Key,
         CompletedTime = a.left.Max(x => x.CompletedTime)
      }).ToList();

How to get dictinct userid with order by close using Linq

2 Answers2