Reputation: 15
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
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
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