CODEWITHSUNDEEP
c#linq
user1663844
user1663844

Reputation: 63

linq to sql row number per group

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

Upvotes: 4

Views: 5587

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109185

You were close, but you must do the Select with 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();

You may need an AsEnumerable() before Select(g => ... because in many implementations of LINQ to a SQL backend the indexed overload of Select isn't supported.

Upvotes: 4

Related Questions