This is an exam program where Table B is all the question to the exam and Table A is the answers of the user.
each question has two choices AnswerID that is why the QuestionId appears twice in one Group in TableA. Once the user answered all the question. which is the TableA.ID 1 to TableA.ID 6. he finishes the exam. now the user is allowed to retake the exam. This TableA.ID 7 to TableA.ID 12 is the retake.
What i want to achieve is to get how many times does the user took the exam. that's why in this example the answer is 2.
Please see Image
I have this query base on the tables on the image
var tableBquestionIDs = await _dbContext.TableB.Select(q => q.QuestionID).toListAsync(); var tableAQuestionIDs = await _dbContext.TableA.Where(r => tableBquestionIDs.Contains(r.QuestionID)) .Select(r => r.QuestionID) .ToListAsync();
can someone help me how am I able to accomplish this on Ef Core
as I was stock here for days