user6934713 user6934713 - 23 days ago 6
C# Question

Insert multiple related tables in Entity at once

I have three tables that are connected by foreign keys. I am trying to insert 1 row in the question table and two rows in the other two tables. I am getting the error 'Insert statement conflict with Foreign Key constraint'
Thank you in advance for the help

public void setMultiAnswer()
{
try
{
string question = "Question 1"
responsesList.Add("Answer1");
responsesList.Add("Answer2");
questionResponsesList.Add(false);
questionResponsesList.Add(true);

using (Entities testEntity = new Entities())
{
Question questionObj = new Question();
questionObj.Question1 = question;
questionObj.CreatedBy = "test";
questionObj.CreatedDate = DateTime.Now;

QuestionRespons questionResponsesObj = new QuestionRespons();
// fill response
foreach (var questionResponse in questionResponsesList)
{
questionResponsesObj.CorrectResponse = questionResponse;
}

questionObj.QuestionResponses.Add(questionResponsesObj);

Response responseObj = new Response();

// fill response
foreach (var response in responsesList)
{
responseObj.Response1 = response;
responseObj.CreatedBy = "test";
responseObj.CreatedDate = DateTime.Now;
}
questionResponsesObj.Response = responseObj;

testEntity.Questions.Add(questionObj);
testEntity.SaveChanges();
}
}
catch (Exception ex)
{
Console.Write(ex);
}


enter image description here

Answer

It sounds like your question id is autogenerated. In this case int questionId = questionObj.QuestionID; will only work after the SaveChanges() call.

In general if you have an EntitySet with foreign keys it is easier to use the navigation properties instead of building id references yourself.

Question questionObj = new Question();
questionObj.CreatedBy = "Test";
questionObj.CreatedDate = DateTime.Now;

QuestionRespons questionResponsesObj = new QuestionRespons();
// fill question response here
questionObj.QuestionResponses.Add(questionResponseObj);

Response responseObj = new Response();
// fill your response here
questionResponsesObj.Response = reponseObj;
// if you do the above in your loop you should be fine

testEntity.Questions.Add(questionObj);
testEntity.SaveChanges();

To match your example:

public void setMultiAnswer()
{
    try
    {
        string question = "Question 1"
        responsesList.Add("Answer1");
        responsesList.Add("Answer2");
        questionResponsesList.Add(false);
        questionResponsesList.Add(true);

        using (Entities testEntity = new Entities())
        {
            Question questionObj = new Question();
            questionObj.Question1 = question;
            questionObj.CreatedBy = "Test";
            questionObj.CreatedDate = DateTime.Now;
            testEntity.Questions.Add(questionObj);

            for (int i = 0; i < responsesList.Count; i++)
            {
                // i am not sure about your relation here, but i assume you require one QuestionResponse per response
                // which is why a moved the line of code
                QuestionRespons questionResponsesObj = new QuestionRespons();
                questionObj.QuestionResponses.Add(questionResponsesObj);

                Response responseObj = new Response();
                responseObj.Response1 = responsesList.ElementAt(i);
                responseObj.CreatedBy = "Test";
                responseObj.CreatedDate = DateTime.Now;

                if (!string.IsNullOrEmpty(responseObj.Response1))
                {
                    questionResponsesObj.Response = responseObj;
                    questionResponsesObj.CorrectResponse = questionResponsesList.ElementAt(i);
                }

            }
            testEntity.SaveChanges();
        }
    }
    catch (Exception ex)
    {
        Console.Write(ex);
    }
}