CODEWITHSUNDEEP
c#visual-studioentity-frameworklinq
Grumbunks
Grumbunks

Reputation: 1267

LINQ Get Average values from IQueryable

I'm new to LINQ and I need to write a LINQ query that returns each project's grade, called notet also the average of all notes. Here is the query I have: 

`var query = _context.Set<EvaluationResult>()
                                .Include(x => x.RatingLevel)
                                .Include(x => x.Skill)
                                .Include(x => x.Evaluation)
                                    .ThenInclude(y => y.Project)
                                        .ThenInclude(z => z.ProjectRatingLevels)
                                            .ThenInclude(a => a.RatingLevel)
                                .Include(y => y.Evaluation.Project)
                                    .ThenInclude(y => y.Degree)
                                .Where(x => x.Evaluation.Project.DegreeId == QueriedDegreeId)
                                .GroupBy(i => new { project = i.Evaluation.Project })
                                .Select(g => new
                                {
                                    project = g.Select(y => y.Evaluation.Project.Label)
                                               .Distinct().FirstOrDefault(),

                                    note = Math.Round(((g.Sum(y => (double)y.Skill.Weight * (double)y.RatingLevel.Rate) /
                                                        g.Sum(y => (double)y.RatingLevel.Rate)) * 100) /
                                                        (double)g.Key.project.ProjectRatingLevels
                                                                 .Select(z => z.RatingLevel.Rate)
                                                                 .Max(), 2, MidpointRounding.AwayFromZero)
                                });

Here is the result:

[
    {
        "project": "Projet 1",
        "note": 42.86
    },
    {
        "project": "Projet 2",
        "note": 41.67
    },
    {
        "project": "Projet 3",
        "note": 46.67
    }
]

What I want is to add another value, average, which is just the Average of all "note" values, like so (the asterisks are just for emphasis):

[
    {
        "project": "Projet 1",
        "note": 42.86,
        **"average": 43.73**
    },
    {
        "project": "Projet 2",
        "note": 41.67,
        **"average": 43.73**
    },
    {
        "project": "Projet 3",
        "note": 46.67,
        **"average": 43.73**
    }
]

MY PROBLEM

I'm stuck trying to calculate an Average of all the returned notes. I have no idea how to proceed. I tried to add an average key in my Select after note and project that reused the note query, like this:

average = g.Average(Math.Round(((g.Sum(... etc

But this gives me type errors: CS1929 'IGrouping<<anonymous type: Project project>, EvaluationResult>' does not contain a definition for average 'Average'. So I'm at an utter loss of what to do.

Can someone point me in the right direction? Am I missing something, like the need to use a Key or something?

Upvotes: 2

Views: 999

Answers (1)

Holger
Holger

Reputation: 2654

Is your goal to do it all in one query ?

The only possible Resultset, with Aggregate and the Aggregated Items, is a GroupBy. If you want to aggregate all, you want only one group, so you have to have a fictive key,the same one for each item

So Append:

 .GroupBy(x => 1) /* makes one group for all, all have the same key */
 .Select(g => new { average = g.Average(x => x.notes), items = g.Select(x => x)});

But this is really Forcing the SQL-Server to do the average. You Manifest the items in your memory anyway. So you can also take your existing Resultset, manifested with ToList or ToArray and just compute

 var result = <yourquery>.ToList();
 double average = result.Average(x => x.notes);

The only difference is, this is done on your CPU, not on the SQL-Server.

Upvotes: 1

Related Questions