CODEWITHSUNDEEP
c#.netlinqado.netlinq-to-entities
Vajda
Vajda

Reputation: 1794

Group by and average in linq

I have 2 tables. MarkQuestion and Question.

MarkQuestion has:
IDQuestion,
IDUser,
Mark

Question has:
IDQuestion,
Text

I want to extract text of each question and average mark for each question:

I tried something like this:

var questions = from mark in Entities.MarkedQuestions
                          join question in Entities.Questions on mark.IDQuestion equals question.IDQuestion
                          group mark by mark.IDQuestion into markGroup,
                          question by question.IDQuestion into questionGroup
                          select new ModelView.MarkQuestionModel
                          {
                              AverageMark = markGroup.Average(x => x.Mark),
                              Text = ..
                          };

How to solve this?

Upvotes: 1

Views: 7230

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1504172

It sounds to me like you want a group join, so that you have two range variables - one representing a question, and another representing all the marked questions for that particular question.

Here's the query expression syntax you'd use:

var questions = from question in Entities.Questions
                join marked in Entities.MarkedQuestions
                    on question.IDQuestion equals marked.IDQuestion
                    into marks
                select new ModelView.MarkQuestionModel
                {
                    AverageMark = marks.Average(x => x.Mark),
                    Text = question.Text
                };

For dot notation, you'd use the GroupJoin method to achieve the same result.

Upvotes: 9

Alex Aza
Alex Aza

Reputation: 78547

Something like this:

var questions =
    from question in Entities.Questions 
    select
        new
        {
            question.Text,
            AverageMark = Entities.MarkedQuestions
                .Where(arg => arg.IDQuestion == question.IDQuestion)
                .Average(arg => arg.Mark)
        };

Upvotes: 1

Related Questions