CODEWITHSUNDEEP
c#sql.netlinqentity-framework-6
VollerSterne
VollerSterne

Reputation: 69

Entity Framework average, group by query

I'm trying to convert a SQL query to Linq for an ASP.NET MVC 5 controller.

SELECT TOP (10) 
    E.Surname, E.Name, E.Patronymic, AVG(CAST(IEEResult1 AS FLOAT))
FROM
    IEEResults AS I
JOIN
    Enrollee AS E ON E.Id = I.EnrolleeId
GROUP BY 
    I.EnrolleeId, E.Surname, E.Name, E.Patronymic
ORDER BY 
    AVG(CAST(IEEResult1 AS FLOAT)) DESC;

This is the answer. Thank you afrazier very much!

 var result = db.IEEResults
    .Join(db.Enrollees, r => r.EnrolleeId, e => e.Id, (r, e) => new { e.Id, e.Surname, e.Name, e.Patronymic, r.IEEResult1 })
    .GroupBy(a => new { a.Id, a.Surname, a.Name, a.Patronymic })
    .Select(gr => new IEEResultsDTO
    {
        Id = gr.Key.Id,
        Surname = gr.Key.Surname,
        Name = gr.Key.Name,
        Patronymic = gr.Key.Patronymic,
        AvgResult = gr.Average(a => a.IEEResult1)
    })
    .OrderByDescending(b => b.AvgResult)
    .Take(10)
    .ToList().AsEnumerable();
            return View(result);

Upvotes: 3

Views: 1355

Answers (1)

afrazier
afrazier

Reputation: 4902

Don't put IEEResult1 into your group since you want to operate on the collection of those items. I'm not very good with the query syntax, but this should be a working query for the lambda syntax.

var result = db.IEEResults
    .Join(db.Enrollees, r => r.EnrolleeId, e => e.Id, (r, e) => new { e.Id, e.Surname, e.Name, e.Patronymic, r.IEEResult1 })
    .GroupBy(a => new { a.Id, a.Surname, a.Name, a.Patronymic })
    .Select(gr => new {
        gr.Key.Id, gr.Key.Surname, gr.Key.Name, gr.Key.Patronymic,
        AvgResult => gr.Average(a => a.IEEResult1)
    })
    .OrderByDescending(b => b.AvgResult)
    .Take(10)
    .ToList();

Upvotes: 4

Related Questions