c# - 一次在实体中插入多个相关表

Question

我有三个通过外键连接的表。我试图在问题表中插入 1 行，在其他两个表中插入两行。我收到错误“插入语句与外键约束冲突”预先感谢您的帮助

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

Answer 1

听起来您的问题 ID 是自动生成的。在这种情况下int questionId = questionObj.QuestionID;仅在 SaveChanges() 调用后有效。

一般来说，如果您有一个带外键的 EntitySet，则使用导航属性比自己构建 id 引用更容易。

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

为了匹配你的例子:

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