I have a working SQL query that returns the results I want on SQL Server:
SELECT
SUM(CriticalCount) As CriticalCount,
SUM(HighCount) As HighCount,
SUM(MediumCount) As MediumCount,
SUM(LowCount) As LowCount,
Timestamp
FROM [dbo].[TicketCounts]
GROUP BY Timestamp
ORDER BY Timestamp DESC
The result set is:
| CriticalCount | HighCount | MediumCount | LowCount | Timestamp |
|---|---|---|---|---|
| 32 | 15 | 18 | 3 | 2021-07-01 11:00:00 |
| 24 | 10 | 42 | 15 | 2021-06-30 10:00:00 |
In my TicketCountRepository of my .Net Core 5.0 project, I want this method to return the same results:
public async Task<IEnumerable<TicketCount>> GetTicketCounts()
{
return await _dbContext.TicketCounts
.GroupBy(o => o.Timestamp)
.Select(tc => new TicketCount
{
CriticalCount = tc.Sum(o => o.CriticalCount),
HighCount = tc.Sum(o => o.HighCount),
MediumCount = tc.Sum(o => o.MediumCount),
LowCount = tc.Sum(o => LowCount)
}).OrderByDescending(o => o.Timestamp).toListAsync();
}
The code compiles fine, but when I hit this method in the TicketCount controller, I get a 500 error:
System.InvalidOperationException: The LINQ expression DbSet().GroupBy(keySelector: o => o.Timestamp, elementSelector o => o).Select(e => new TicketCount { CriticalCount = e.Sum(s => s.CriticalCount), HighCount = e.Sum(s => s.HighCount), MediumCount = e.Sum(s => s.MediumCount), LowCount = e.Sum(s => s.LowCount)}).OrderByDescending(e0 => e0.Timestamp) could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList' or 'ToListAsync'.
What am I doing wrong? Should I use something other than linq to get the result set needed?
The type you're querying is wrong, it's the return type instead of the type containing the raw data. You want something like:
return await _dbContext.Tickets
.GroupBy(o => o.Timestamp)
.Select(x => new TicketCount
{
Timestamp = x.Key,
CriticalCount = x.Sum(o => o.CriticalCount),
HighCount = x.Sum(o => o.HighCount),
MediumCount = x.Sum(o => o.MediumCount),
LowCount = x.Sum(o => LowCount)
}).OrderByDescending(x => x.Timestamp).toListAsync();
Since you don't select timestamp move it before select
return await _dbContext.TicketCounts
.GroupBy(o => o.Timestamp)
.OrderByDescending(o => o.Timestamp)
.Select(tc => new TicketCount
{
CriticalCount = tc.Sum(o => o.CriticalCount),
HighCount = tc.Sum(o => o.HighCount),
MediumCount = tc.Sum(o => o.MediumCount),
LowCount = tc.Sum(o => LowCount)
}).ToListAsync();
or you can try to add Timestamp property to TicketCount and add it to Select.