locked
Problem with LINQ causing SQL exception: Cannot insert duplicate key row RRS feed

  • Question

  • User1255309776 posted

    Hello,

    I develop a quiz application. In my database design, there is many-to-many relationship between Test takers and questions as same exam questions may concern various test takers and each test taker will have many questions of the same exam.  My problem is that I cannot write right action in controller so that when each user submit their answer while they are logged in, and if it's true, the relevant question score for each user must be saved in database.  So, considering this, my model is like this:

    Question

    public class Question
        {
            public Question()
            {
                Answers = new HashSet<Answer>();
            }
    
            public int Id { get; set; }
            public string Name { get; set; }
            public string Description { get; set; }
            public Exam Exam { get; set; }
            public int ExamId { get; set; }
            public TimeSpan? Remainedtime { get; set; }
            public int Score { get; set; }
            public ICollection<Answer> Answers { get; set; }
            public ICollection<UserQuestion> UserQuestions { get; set; }
    
        }    

    ExamUser

    public class ExamUser: IdentityUser
        {
            public TestTaker TestTaker { get; set; }
            public int? TestTakerId { get; set; }
        }

    TestTaker

     public class TestTaker
        {
            public int Id { get; set; }
            [Required]
            public string Name { get; set; }
            public string Phone { get; set; }
            public string Education { get; set; }
            public string Job { get; set; }
            public DateTime Birth { get; set; }
            public ExamUser ExamUser { get; set; }
            public int Result { get; set; }
            public ICollection<UserQuestion> UserQuestions { get; set; }
        }

    UserQuestion (moderate table for many-to-many relationship)

    public class UserQuestion
        {
            public int TestTakerId { get; set; }
            public TestTaker TestTaker { get; set; }
            public int QuestionId { get; set; }
            public Question Question { get; set; }
        }

    The reason why I didn't connect ExamUser directly to questions is that its built in Id is string (it might cause problem while inserting to database).

    In DbContext

    public class IntellectDbContext:IdentityDbContext<ExamUser>
        {
            public IntellectDbContext(DbContextOptions<IntellectDbContext> dbContextOptions) : base(dbContextOptions)
            {
    
            }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
                modelBuilder.Entity<UserQuestion>()
                    .HasKey(a => new { a.TestTakerId, a.QuestionId });
    
                modelBuilder.Entity<UserQuestion>()
                    .HasOne(a => a.TestTaker)
                    .WithMany(b => b.UserQuestions)
                    .HasForeignKey(a => a.TestTakerId);
    
                modelBuilder.Entity<UserQuestion>()
                    .HasOne(a => a.Question)
                    .WithMany(c => c.UserQuestions)
                    .HasForeignKey(a => a.QuestionId);
                
              
            }
    
            public DbSet<Answer> Answers { get; set; }
            public DbSet<Question> Questions { get; set; }
            public DbSet<Exam> Exams { get; set; }
            public DbSet<ExamUser> ExamUsers { get; set; }
            public DbSet<UserQuestion> UserQuestions { get; set; }
            public DbSet<TestTaker> TestTakers { get; set; }
        }

    View (Question)

    @model Intellect.Models.ViewModels.AdminViewModel
    
        @{
            Layout = "AdminLayout";
        }
    
        <div class="questioncontainer">        
            <form asp-action="Question" asp-controller="Home" asp-route-id="@Model.NextQuestion.Id" asp-route-count="@ViewBag.Equestions">
                <div class="row">
                    <div class="col-lg-3"></div>
                    <div class="col-lg-6 col-sm-12">
                        <table>
                            <tr>
                                <th>Qaliq vaxt</th>
                            </tr>
                            <tr>
                                <td>
                                    <input asp-for="Question.Score" id="time" name="timer" />
                                </td>
                            </tr>
                        </table>
                        <div class="question">@Model.CurrentQuestion.Description </div>
                    </div>
                    <div class="col-lg-3"></div>
                </div>
                <div class="row">
                    <div class="col-lg-3 col-sm-12">
                    @foreach (Answer item in Model.Answers)
                    {
                                <input asp-for="@item.Id" name="@item.Id" hidden />
                                <input type="radio" asp-for="@item.Id" name="myanswer" value="@item.Id" />@item.Description
    
                                <br>                 
                    }
    </div>
                    <div class="col-lg-3"></div>
                </div>
                <div class="row">
                    <div class="col-lg-6 col-sm-4">
    
                    </div>
                    <div class="col-lg-3 col-sm-4">
    
                    </div>
                    <div class="col-lg-3 col-sm-4">
                        <div class="nextbtn">
                            @if (ViewBag.Equestions == 0)
                            {
                                <input type="submit" value="Finish" />
                            }
                            else
                            {
                                <input type="submit" value="Next" />
                            }
    
                        </div>
                    </div>
                </div>
            </form>
    
        </div>
    
    @section Script{ 
        <script>
            function StartTimer(seconds) {
                var intSeconds = seconds;
                var timer = setInterval(myTimer, 1000);
                function myTimer() {
                    if (intSeconds < 0) {
                        Alert("bitdi")
                        clearInterval(timer)
                        return;
                    }
                    document.getElementById("time").value = intSeconds;
                    intSeconds--;
                }
            }
            StartTimer(60);
    
        </script>
    }

    Here is the Controller where error lies

     public class HomeController : Controller
        {
            private readonly IntellectDbContext _intellectDbContext;
            private readonly UserManager<ExamUser> _userManager;
            private readonly SignInManager<ExamUser> _signInManager;
            static int exam_id = 0;
            static int? PreviousId = 0;
            static int result = 0;
            static int correctAnswer = 0;
            static List<Question> RemainedQuestions = new List<Question>();
            static List<int> trueAnswers = new List<int>();
    
            public HomeController(IntellectDbContext intellectDbContext, UserManager<ExamUser> userManager, SignInManager<ExamUser> signInManager)
            {
                _intellectDbContext = intellectDbContext;
                _userManager = userManager;
                _signInManager = signInManager;
            }
             //this action opens the view before starting for test (it shows rules and start button to start)
            [HttpGet]
            public async Task<IActionResult> Test(int Id)  
            {
                exam_id = Id;
                AdminViewModel admodel = new AdminViewModel();
                admodel.Equestions = await _intellectDbContext.Questions.Include(q => q.Answers).Where(q => q.ExamId == Id).ToListAsync();
                admodel.CurrentQuestion = await _intellectDbContext.Questions.Where(q => q.ExamId == Id).FirstOrDefaultAsync();
                RemainedQuestions = admodel.Equestions;
                PreviousId = admodel.CurrentQuestion.Id;
                return View(admodel);
            }
        
             // Question action is the main action which gets us to question view described above.
            [HttpGet]
            public async Task<IActionResult> Question(int Id, int count)
            {
                AdminViewModel admodel = new AdminViewModel();
                admodel.CurrentQuestion = await _intellectDbContext.Questions.Where(x => x.Id == Id).SingleOrDefaultAsync();
                admodel.Answers = await _intellectDbContext.Answers.Where(y => y.QuestionId == Id).ToListAsync();
                admodel.Equestions = await _intellectDbContext.Questions.Where(q => q.ExamId == exam_id).ToListAsync();
    
                if (count > 1)
                {
                    var question = RemainedQuestions.Single(r => r.Id == admodel.CurrentQuestion.Id);
                    PreviousId = question.Id;
                    RemainedQuestions.Remove(question);
                    admodel.NextQuestion = RemainedQuestions[0];
                    count -= 1;
                }
                else
                {
                    admodel.NextQuestion = RemainedQuestions[0];
                    count -= 1;
                }
    
                if (count == -1)
                {
                    return RedirectToAction(nameof(Finish));
                }
    
                ViewBag.Equestions = count;
    
                return View(admodel);
            }
              //Post action of Question checks the answer chosen and inserts score value calculated into database, and redirects to next question (get action) 
            [HttpPost]
            [ValidateAntiForgeryToken]
            public async Task<IActionResult> Question(int Id, int count, int myanswer, int timer)
            {
                AdminViewModel admodel = new AdminViewModel();
                admodel.CurrentQuestion = await _intellectDbContext.Questions.Where(x => x.Id == Id).SingleOrDefaultAsync();
                admodel.Answers = await _intellectDbContext.Answers.Where(y => y.QuestionId == Id).ToListAsync();
                admodel.Equestions = await _intellectDbContext.Questions.Where(q => q.ExamId == exam_id).ToListAsync();
    
                correctAnswer = _intellectDbContext.Answers.Where(a => a.QuestionId == PreviousId && a.Correct == true).SingleOrDefault().Id;
    
                if (_signInManager.IsSignedIn(User))
                {
                    ExamUser examTaker = await _userManager.GetUserAsync(HttpContext.User);
    
                    examTaker.TestTaker = await _intellectDbContext.TestTakers.FirstOrDefaultAsync();
    
                    admodel.CurrentQuestion = await _intellectDbContext.Questions.Include(q => q.UserQuestions).Where(u => u.Id == Id).SingleOrDefaultAsync();
    
                    if (myanswer == correctAnswer)
                    {
                        admodel.CurrentQuestion.Score = timer;
                        await _intellectDbContext.SaveChangesAsync();
                    
                    }
                }
                 
                    if (count > 1)
                {
                    var question = RemainedQuestions.Single(r => r.Id == admodel.CurrentQuestion.Id);
                    PreviousId = question.Id;
                    RemainedQuestions.Remove(question);
                    admodel.NextQuestion = RemainedQuestions[0];
                    count -= 1;
                }
                else
                {
                    admodel.NextQuestion = RemainedQuestions[0];
                    count -= 1;
                }
    
                if(count == -1)
                {
                    return RedirectToAction(nameof(Finish));
                }
    
                ViewBag.Equestions = count;
    
                return RedirectToAction(nameof(Question));
            }
            

    I need to write right LINQ in highlighted code so that admodel.currentQuestion would give me the question with that Id that concerns TestTaker which is logged in (whose Id is say 10).  So, after running the application, when clicking on Next button to go the following question, SQL Exception riseshttps://prnt.sc/selfav

    Of course, when testTaker enrolls for the exam, userQuestions table is fillied for its Id with that exam's question Ids. 

     [HttpPost]
            [ValidateAntiForgeryToken]
            public async Task<IActionResult> Enroll(TestTaker testTaker, DateTime birthdate)
            {
                if (ModelState.IsValid)
                {
                    testTaker.Birth = birthdate;
                    testTaker.Result = 0;
                    testTaker.UserQuestions = await _intellectDbContext.Questions.Where(q => q.ExamId == 3).Select(q => new UserQuestion { Question = q, TestTaker = testTaker }).ToListAsync();
                    _intellectDbContext.TestTakers.Add(testTaker);
                    await _intellectDbContext.SaveChangesAsync();
                    return RedirectToAction(nameof(Proceed));
                }
                else
                {
                    ModelState.AddModelError("", "Kecmedi");
                    return View();
                }
            }

    It's like it sees that I'm trying to insert testTaker again to database while I need to set admodel.currentQuestion to that testTaker's Id so that its score would calculated for the TestTaker with id say 10. Also, this solutiuon must work when several test takers take exam, like you know from Trivia game.

    Monday, May 11, 2020 9:38 AM

All replies

  • User711641945 posted

    Hi FaridGN,

    Could you share your whole model and ViewModel?You provide several actions.Which action is used to display your main view?Could you describe your actions what they are used to do?

    And your highlight code is used to search for the record,it seems that it could not cause the insert error.More detailed code would be helpful.

    Best Regards,

    Rena

    Tuesday, May 12, 2020 8:21 AM
  • User1255309776 posted

    Dear Rena,

    I've added relevant comments to actions described above. The main action is Question (get/post) in my app, and the View provided above is Question view. The main models that concern this subject is provided above. Additionally, I share my main ViewModel which I used in Question view:

     public class AdminViewModel
        {
            public int Id { get; set; }
            public Exam Exam { get; set; }
            public Question Question { get; set; }
            public Answer Answer { get; set; }
            public IEnumerable<Exam> Exams { get; set; }
            public List<SelectListItem> Examlist { get; set; }
            public List<SelectListItem> QuestionList { get; set; }
            public IEnumerable<Question> Questions { get; set; }
            public IEnumerable<Answer> Answers { get; set; }
            public TestTaker TestTaker { get; set; }
            public IEnumerable<TestTaker> TestTakers { get; set; }
            public Question CurrentQuestion { get; set; }
            public Question NextQuestion { get; set; }
            public List<Question> Equestions { get; set; }
            public string SelectedAnswer { get; set; }
        }

    Let me describe my issue shortly. Assume you have TestTakers table, Questions table, and userQuestions table which is middle link between them (many-to-many relationship) in SQL Server. How would you define particular question score (Question has int Score property) for particular TestTaker using LINQ?

    As you know, the question under Id ,say 1 will concern each test taker who takes exam, and that's why first I get the user signed in, get his TestTaker (with ID), and try to get current question score according to his answer to that test Taker. 

    If I were to join these three tables, and to choose needed columns, I would get table view like this if say, 2 test takers participated in quiz of 2 questions:

    Name TestTakerId QuestionId Score
    Farid 10 1 3
    Farid 10 2 5
    Rena 12 1 5
    Rena 12 2 4

    Of course, my aim is not to get this type of table, I just described it in order get better picture how we would assign scores from part of C# using LINQ. Any idea?

    Tuesday, May 12, 2020 8:42 PM
  • User711641945 posted

    Hi FaridGN,

    How would you define particular question score (Question has int Score property) for particular TestTaker using LINQ?

    Did you want to use linq to get the result like the table?

    Name TestTakerId QuestionId Score
    Farid 10 1 3
    Farid 10 2 5
    Rena 12 1 5
    Rena 12 2 4

    For your error message,be sure to check if you add a multiple id.

    Best Regards,

    Rena

    Thursday, May 14, 2020 9:12 AM
  • User1255309776 posted

    Dear Rena,

    Yes I'd like to get the result like that table, but you know that I don't need to join them, I just wanted to get imagination as I cannot imagine the question with the same Id that has many score values in Question table, But my aim is not to present them in SQL, my aim is to avoid SQL exception in order  to correctly define admodel. currentQuestion which will assign it to signed in test Taker's question score. Did you get what I mean?

    Also, I don't know what code (and line) causes that SQL exception as in error statement of SQL there's not shown like on other errors like null-reference, or argument null exception. And I don't see any code in my controller that would insert the same id 2 or more times.

    Please, advise

    Thursday, May 14, 2020 9:16 PM
  • User-474980206 posted

    most likely you are using the same key for testTaker rather than a unique key (or using auto assign), in the code:

                    _intellectDbContext.TestTakers.Add(testTaker);
                    await _intellectDbContext.SaveChangesAsync();
    
    Thursday, May 14, 2020 9:34 PM
  • User1255309776 posted

    Dear Bruce,

    The code you displayed is from Enroll action which is not executed when clicking on Next button to go to next question. It is used when users enrolls for exam before starting to take it. And Enrol located in other controller (though I didn't provide). I just showed that method to ensure you that userQuestions table is not empty, and has relevant testTakerId.

    Friday, May 15, 2020 10:05 AM