Linq to sql number of rows in a group

Question

I have a table with a job description column and a date column. I would like to get a line number for each line 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 cannot convert this to Linq to SQL. I tried to use

var jobGroups = context.BackgroundJobs.GroupBy(g => new{g.Description, g.RunDate}).Select((jobs, index) => new { RowCount = index++, BackgroundJob = jobs }).Tolist();

There was a way out

 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 increased the number of rows, but when the description changed, there was no reset to 1.

Please indicate how to reset the line number to 1 when the group description changes in linq to SQL.

Thanks. Mahita p>

Answer 1

You were close, but you have to make Selectthe index one level deeper:

var jobGroups = 
    context.BackgroundJobs
           .GroupBy(job => new { job.Description, job.RunDate })
           .Select(g => new
            {
               g.Key, 
               Jobs = g.Select((job,i) => new
               { 
                  RowCount = i + 1,
                  job
               })
            })
           .Tolist();

AsEnumerable() Select(g => ..., LINQ SQL- Select .