Proxymus89
Proxymus89

Reputation: 15

Linq: group by + count for one column

I have this query in SQL

     select FileName, UploadDate, Status 
     from MyTable group by FileName, UploadDate, Status

this give me the correct output

FileName    UploadDate              Status
fuel 1.xls  2020-04-10 17:43:04.857 1
fuel 1.xls  2020-04-10 17:43:04.857 4
fuel 2.xls  2020-04-10 17:43:17.193 4

I can translate this query to LINQ

context.MyTable
                .GroupBy(x => new { x.FileName, x.UploadDate, x.Status })
                .Select(x => new { x.Key.FileName, x.Key.UploadDate, x.Key.Status });

Now i wanna the same query but an additional column with the count of the 'Status' column

i Accomplish this in SQL with this query

select FileName, UploadDate, Status, count(Status) as 'StatusCount' 
from MyTable group by FileName, UploadDate, Status

This give me the correct output

FileName    UploadDate             Status   StatusCount
fuel 1.xls  2020-04-10 17:43:04.857 1       19
fuel 1.xls  2020-04-10 17:43:04.857 4       1
fuel 2.xls  2020-04-10 17:43:17.193 4       20

How to translate this additional "column count" into LINQ?i've tried several times different solutions but without success. Can someone help me please?

Upvotes: 0

Views: 632

Answers (2)

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62498

You just need to call the Count method on each IGrouping<> object in the Select method :

context.MyTable
            .GroupBy(x => new { x.FileName, x.UploadDate, x.Status })
            .Select(x => new 
                           { 
                             FileName = x.Key.FileName, 
                             UploadDate = x.Key.UploadDate, 
                             Status = x.Key.Status,
                             Count = x.Count()
                    });

Upvotes: 0

vc 74
vc 74

Reputation: 38179

If you really mean count:

context.MyTable
   .GroupBy(x => new { x.FileName, x.UploadDate, x.Status })
   .Select(x => new { x.Key.FileName, x.Key.UploadDate, x.Key.Status,
                      Count = x.Count() });

If you actually meant Sum:

context.MyTable
   .GroupBy(x => new { x.FileName, x.UploadDate, x.Status })
   .Select(x => new { x.Key.FileName, x.Key.UploadDate, x.Key.Status,
                      Sum = x.Sum(e => e.Status) });

Upvotes: 1

Related Questions