How to use average function with EF

Question

I have a Entity named transactions with {Id RawDateTime ScoreSId} I want to get a average of Score per day for records having SId. What as I doing wrong here? _context.transactions.Where(b => b.SId == request.Id).GroupBy(a=>a.RawDateTime.Date).Include(b=>b.Average(v=>v.Score)).ToListAsync()

Tuhin Paul · Answer

Also as per your query the Include method is used to load related entities along with the main entity but in your query you are trying to include the result of the Average operation which is not a related entity. Instead of using Include you can use Select to project the result of the query into the desired shape. Another approach : var result = await _context.transactions .Where(b => b.SId == request.Id) .GroupBy(a => a.RawDateTime.Date) .Select(g => new { Date = g.Key AverageScore = g.Average(v => v.Score) }) .ToListAsync(); This query groups the transactions by date calculates the average score for each group.

Tuhin Paul · Answer

var transactionsByDate = await _context.transactions .Where(t => t.SId == request.Id) .GroupBy(t => t.RawDateTime.Date); var averageScoresByDate = transactionsByDate .Select(group => new { Date = group.Key AverageScore = group.Average(t => t.Score) }); var result = await averageScoresByDate.ToListAsync();

Amit Mohanty · Answer

You're using the Include method after the GroupBy method which is not allowed in Entity Framework. The Include method is used to specify related entities to be included in the query results but it should be used before any grouping or aggregation operations. var result = await _context.transactions .Where(b => b.SId == request.Id) .GroupBy(a => a.RawDateTime.Date) .Select(g => new { Date = g.Key AverageScore = g.Average(v => v.Score) }) .ToListAsync();

Rajkiran Swain · Answer

await _context.transactions .Where(b => b.SId == request.Id) .GroupBy(a => a.RawDateTime.Date) .Select(g => new { Date = g.Key AverageScore = g.Average(a => a.Score) }) .ToListAsync();

Jignesh Kumar · Answer

Please try below var resultGroupBy = await _context.transactions .Where(x => x.SId == request.Id) .GroupBy(g => g.RawDateTime.Date) .Select(grp => new { Date = grp.Key.Date AvgScore = grp.Average(v => v.Score) }).ToListAsync(); foreach (var item in resultGroupBy) { Console.WriteLine(Date :{0} item.Date); Console.WriteLine(AVG Score :{0} item.AvgScore); }