Convert Sql to linq with groupby

July 26, 2022

I have view on which I use this request

Select Spendband, SUM(SpendCurrencyJob), SUM(SpendDocumentCount)
From analysis.vwJobSupplierMetrics
Where JobId = '500E0DD1-E3D3-4887-95EF-01D3C9EA8FD0'
Group by SpendBand

And it’s running sucessfully

and get me this data

How I need to write it using linq to get same data?

I tried like this

    var data = await _dbContext.VwJobSupplierMetrics.Where(x => x.JobId == jobId)
            .GroupBy(x => x.SpendBand)
            .Select(x => new HumpChartDto() {SpendBand = x.SpendBand}).ToListAsync();

But on new HumpChartDto() {SpendBand = x.SpendBand} I got Cannot resolve symbol 'SpendBand

How I can solve this?

>Solution :

First, after grouping on SpendBand, you need to access it via Key property. Second, to compute Sum, you can use Sum method.

var data = await _dbContext.VwJobSupplierMetrics.Where(x => x.JobId == jobId)
            .GroupBy(x => x.SpendBand)
            .Select(x => new HumpChartDto() {
                                             SpendBand = x.Key,
                                             SumOfSpendCurrencyJob = x.Sum(s => s.SpendCurrencyJob),
                                             SumOfSpendDocumentCount= x.Sum(s => s.SpendDocumentCount),
                                            }
                    )
            .ToListAsync();

Note – change the property name accordingly for name I’ve used for SumOfSpendCurrencyJob and SumOfSpendDocumentCount as don’t know the definition of HumpChartDto class.