How To execute this scenario on EF Core

Zackelberry 1 Reputation point
2021-09-30T11:39:54.083+00:00

Hello,

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
136600-tables.png

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
TIA

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
689 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,488 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,099 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Karen Payne MVP 35,016 Reputation points
    2021-09-30T17:48:01.833+00:00

    The first step is to create a GroupBy in a lambda or linq query with a where condition on a list containing QuestionId in Table B. Once you have the results either use them or apply another where predicate to the first result set. Test this in unit test methods.


  2. Viorel 110.8K Reputation points
    2021-10-01T16:55:00.83+00:00

    Maybe the next answer correspond to your question:

    int how_many_times = _dbContext.TableA.Count( ) / (_dbContext.TableB.Count( ) * 2);

    0 comments No comments