Entity Framework - GroupBy

Question

Hi

I have a quiz and questions model.

context.Quizzes.Include(Questions)
.Where(q => q.Status = true)
.GroupBy() //need group by Questions.QuizId and get Id, Title, Status, Total Questions in the Select
.Take(10).
.ToList();

GroupBy(quiz => quiz.Question.GroupBy(question => question.QuizId))
Is that valid?
How do i select all the column from quiz with total questions ?

Answer 1

Hi, @Mahesh Kumar. Welcome to Microsoft Q&A. 

Here we assume your table is as follows:

    public class Question
    {
        public int Id { get; set; }
        public int QuizId { get; set; }

        public Quiz Quiz { get; set; }
    }

    public class Quiz
    {
        public int Id { get; set; }

        public string Title { get; set; }

        public bool Status { get; set; }

        public List<Question> Question { get; set; }
    }

The configuration information is as follows:

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Quiz>(entity =>
            {
                entity.HasKey(p=>p.Id);

                entity.HasMany(p=>p.Question).WithOne(q=>q.Quiz).HasForeignKey(o=>o.QuizId);
            });

            modelBuilder.Entity<Question>(entity =>
            {
                entity.HasKey(p => p.Id);
            });
        }

If QuizId is used as a foreign key in question table and is associated with quiz's primary key Id, there is no need to group by QuizId, because the grouping operation is equivalent to grouping by quiz's id, but Id is unique.

You could refer to the following query code to select

var list = context.Quizzes.Include(p=>p.Question).Where(q=>q.Status == true).Select(p =>
    new 
    {
        Id = p.Id,
        Title = p.Title,
        Status = p.Status,
        TotalQuestions = p.Question.Count()
    }
).Take(10).ToList();

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.