Can these two LINQ-to-SQL queries be combined?

user_1068317 0
Viewed 75

I have two tables, Exam and ExamGroup which have a *:* relationship. I am given a List<Exam> and need to determine if an ExamGroup for this list already exists. So the provided list might be A+B+C, ExamGroups will contain, A+B, A+C, A+B+C+D+E, etc. and I need to determine if A+B+C exists and create it if it doesn't. The code below seems to do it but I'm wondering if it's possible to do it in a single expression?

var givenExams = /* Provided list of exams */
var examGroup = _context
    .ExamGroups
    .Include(x => x.ExamGroupsExam) // Mapping table for EF Core
    .ThenInclude(y => y.Exam)
    .AsEnumerable();

examGroup = givenExams.Aggregate(
    examGroup, 
    (current, exam) => current.Where(x => x.Exams.Contains(exam)));

At this point examGroup is either the relevant group (which has an ID and other fields I care about) or null, at which point I know to create it.

entity-framework-core linq-to-sql linq c#
1 Answers

Yes it is possible. You need to use only the ExamGroupsExam table to determine it. Please filter down the ExamGroupsExam table by ExamId (using givenExams list) inside a sub query and then group the ExamId by the ExamGroupId. From this point, the query can identify the number of exams in that group. If this number is matching with the number of items in the givenExams then the group is exists if not you can create it. :)

I did create a query from top of my head, hopefully it will help you move forward:

var examGroupExists = (from examGroupsExam in _context.ExamGroupsExam
                     where (from examGroupsExam in _context.ExamGroupsExam
                            where givenExams.Any(x => x.Id == examGroupsExam.ExamId)
                            select examGroupsExam.ExamGroupId).Any(x == examGroupsExam.ExamGroupId)
                     group examGroupsExam.ExamId by examGroupsExam.ExamGroupId
                     into examGroup
                     select new {
                        NumberOfExamsInGroup = examGroup.Count(),
                        ExamGroupId = examGroup.Key
                     }).Any(x => x.NumberOfExamsInGroup == givenExams.Count);
                 
if (examGroupExists == false) 
{
    // then create it
}

EDIT: I've noticed there was a bug in the first query, so I've changed it above and added a subquery. Basically the bug was: if A + B + C + D exam was one group then if you were looking for A + B + C group is returned true, with the sub select this should work okay.

user_39349940
Related