I have a table with a job description column and date column. I would like to get the row number for each row after grouping by description as shown below.
Description RunDate rn
File Cleanup 2013-12-30 00:00:00.0000000 +00:00 1
File Cleanup 2013-12-29 00:00:00.0000000 +00:00 2
Billing Extract 2013-12-30 00:00:00.0000000 +00:00 1
Billing Extract 2013-12-30 00:00:00.0000000 +00:00 2
Billing Extract 2013-12-29 00:00:00.0000000 +00:00 3
Unit Data Extract 2013-12-05 00:00:00.0000000 +00:00 1
Monthly Extract 2013-12-05 00:00:00.0000000 +00:00 1
I was able to achieve this in SQL with the following query
SELECT [Description], RunDate, rn = ROW_NUMBER()
OVER (PARTITION BY [Description] ORDER BY RunDate DESC)
FROM BackgroundJobs
I am not able to convert this into Linq to SQL. I tried using
var jobGroups = context.BackgroundJobs.GroupBy(g => new{g.Description, g.RunDate}).Select((jobs, index) => new { RowCount = index++, BackgroundJob = jobs }).Tolist();
The output was
Description RunDate rn
File Cleanup 2013-12-30 00:00:00.0000000 +00:00 1
File Cleanup 2013-12-29 00:00:00.0000000 +00:00 2
Billing Extract 2013-12-30 00:00:00.0000000 +00:00 3
Billing Extract 2013-12-30 00:00:00.0000000 +00:00 4
Billing Extract 2013-12-29 00:00:00.0000000 +00:00 5
Unit Data Extract 2013-12-05 00:00:00.0000000 +00:00 6
Monthly Extract 2013-12-05 00:00:00.0000000 +00:00 7
The Linq to SQL query incremented the row number but it did not reset to 1 when the description changed.
Please advise how to reset the row number to 1 when the group description changes in linq to SQL.
Thank you Mahita