Calculate row number over group in linq and take only those with row number 1?

Question

What would be the LINQ counterpart to get the row number over partition and then get only those records having row number as 1?

Here is the SQL which I want to convert to its LINQ implementation:

select
   ROW_NUMBER () OVER (PARTITION BY TASKKEY  ORDER BY CREATEDON DESC) as rn
   ,pwt.*
from
   TaskClass pwt
where pwt.STATUS = 0

Here is the LINQ i have tried so far, but it doesnt give correct result

            var output = Result.GroupBy(t => t.TaskKey)
            .SelectMany(g => g.Select((j, i) =>
                new
               {
                   //Rest of the fields 
                   ,
                   rn = i + 1 //Row number
               }

                )
            )
            .Where(e => e.rn == 0)
            .Select(
            y => new TaskClass
            {
                //Rest of the fields
            }

            ).OrderBy(o => o.Id )
            .ToList<TaskClass>()
            );

In a nutshell, I want to find which entry from the rows having the same TaskKey has latest CreatedOn, but in pure LINQ.

Answer 1

I'm not sure why do you need the row numbers but, if you want distinct by the TaksKey and get the latest entry:

var output = Result.GroupBy(a => a.TaskKey)
    .ToDictionary(a => a.Key, b => b.Value.OrderBy(CreatedOn).First());

Same with some filter.

var output = Result.Where(a => a.STATUS == 0)
    .GroupBy(a => a.TaskKey)
    .ToDictionary(a => a.Key, b => b.Value.OrderBy(CreatedOn).First());

I have not tested this so please check the date order, if it doesn't fit just use Last instead of First.