Entity Framework average, group by query

Question

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);

are you really using link to sql (your tag)? or are you using EF ? What version? — gsharp, Nov 16 '20 at 18:09
and what exactly is "not working" is it your group by? check --> https://stackoverflow.com/questions/847066/group-by-multiple-columns — gsharp, Nov 16 '20 at 18:17
You have `group a by a.Id into gr` which means `gr.Key` and `a.Id` are the same - is `a.Id` a class? Otherwise, you can't reference `gr.Key.Surname` unless `a.Id.Surname` is valid. Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Nov 16 '20 at 18:45
Enrollee does not contain the definition of IEEResult1 :) Still fighting with Average function. — VollerSterne, Nov 16 '20 at 19:10
Please [edit] your question and include the exception message you get. — Gert Arnold, Nov 16 '20 at 19:56
Don't edit your question to remove the original issue and incorporate the answer from a posted answer. — NetMage, Nov 16 '20 at 20:14

score 4 · Accepted Answer · answered Nov 16 '20 at 19:17

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();

Thank you very much! You are very good in query syntax! – VollerSterne Nov 16 '20 at 19:45 — VollerSterne, Nov 16 '20 at 19:45

Entity Framework average, group by query

1 Answers1