Insert multiple related tables in Entity at once

Question

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);
}

can you show the models code ?

Sampath
– Sampath

2016-11-16 12:13:14 +00:00
Commented Nov 16, 2016 at 12:13 — Sampath
– Sampath, Commented Nov 16, 2016 at 12:13
Thank you I added the part of the model.

user6934713
– user6934713

2016-11-16 12:38:12 +00:00
Commented Nov 16, 2016 at 12:38 — user6934713
– user6934713, Commented Nov 16, 2016 at 12:38

jpk · Accepted Answer · 2016-11-16 13:19:26Z

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);
    }
}

Thank you. I need to insert in table Response as well. And the table questionResonses has foreign keys from the other two tables
I updated the excample to inculde Response, i'm not sure about your relation between QuestionResponses and Reponse which is why i did not include your for loop in the example.

Jens H · Accepted Answer · 2016-11-16 12:45:25Z

0

Change the order of

int questionId = questionObj.QuestionID;
testEntity.SaveChanges();

into

testEntity.SaveChanges();
int questionId = questionObj.QuestionID;

You created a new instance questionObj which should have a default ID of 0. Only AFTER calling SaveChanges() the ID should be assigned the newly assigned actual ID value.

So, what happens here, is that you memorize questionId with the default value of 0 instead of the real ID. As a conseqence, the relations between questions and responses will always be wrong.

answered Nov 16, 2016 at 12:45

Jens H

4,6422 gold badges28 silver badges39 bronze badges

2 Comments

Gert Arnold Over a year ago

This splits an atomic process into two transactions -- not good.

Jens H Over a year ago

@GertArnold Agreed! I would not implemet it that way, either. I just wanted to keep the answer short and without completely rewriting the full code.

LeBaptiste · Accepted Answer · 2016-11-16 12:50:35Z

0

If I were you I would remove this QuestionResponse table, to keep only Question and Response. Use the navigation properties instead of directly setting the foreign key.

    Question questionObj = new Question
    {
        Text = question,
        CreatedBy = "Test",
        CreatedDate = DateTime.Now
    };

    foreach(var response in responsesList.Where(x => !string.IsNullOrEmpty(x)))
    {
        Response responseObj = new Response
        {
            Text = response,
            IsCorrect = true,
            CreatedBy = "Test",
            CreatedDate = DateTime.Now
        }

        questionObj.Add(responseObj);
    }

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

answered Nov 16, 2016 at 12:50

LeBaptiste

1,18614 silver badges20 bronze badges

4 Comments

user6934713 Over a year ago

How can I include the question response as well

Gert Arnold Over a year ago

No, the QuestionResponse entity can't be removed from the model, but setting navigation properties instead of FK values is entirely correct!

user6934713 Over a year ago

I changed the code according to your suggestion(you can see the code above). But I am entering only one questionResponse and response instead of two.

LeBaptiste Over a year ago

I suggested to remove the QuestionResponse table making the assumption the relation Response <-> Question should be one-to-many as it is unlikely to have same response related to many questions. If this is not pertinent please ignore the suggestion.

Collectives™ on Stack Overflow

Insert multiple related tables in Entity at once

3 Answers 3

2 Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related