Search code examples
c#sql-serverasp.net-mvcentity-frameworkentity-framework-5

How can I use EF to add multiple child entities to an object when the child has an identity key?


We are using EF5 and SQL Server 2012 the following two classes:

public class Question
{
    public Question()
    {
        this.Answers = new List<Answer>();
    }
    public int QuestionId { get; set; }
    public string Title { get; set; }
    public virtual ICollection<Answer> Answers { get; set; }
}

public class Answer
{
    public int AnswerId { get; set; }
    public string Text { get; set; }
    public int QuestionId { get; set; }
    public virtual Question Question { get; set; }
}

Mapping is as follows:

public class AnswerMap : EntityTypeConfiguration<Answer>
{
    public AnswerMap()
    {
        // Primary Key
        this.HasKey(t => t.AnswerId);

        // Identity
        this.Property(t => t.AnswerId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

Database DDL

CREATE TABLE Answer (
    [AnswerId] INT IDENTITY (1, 1) NOT NULL,
    [QuestionId] INT NOT NULL,
    [Text] NVARCHAR(1000),
    CONSTRAINT [PK_Answer] PRIMARY KEY CLUSTERED ([AnswerId] ASC)
);

Here are the results of what I have tried:

This works for one child:

var a = new Answer
{
    Text = "AA",
    QuestionId = 14
};
question.Answers.Add(a);
_uow.Questions.Update(question);
_uow.Commit();

This does not work for more than one child:

Error: An object with the same key already exists in the ObjectStateManager. The ObjectStateManager cannot track multiple objects with the same key.

var a = new Answer
{
    AnswerId = 0,
    Text = "AAA",
    QuestionId = 14
};
var b = new Answer
{
    AnswerId = 0,
    Text = "BBB",
    QuestionId = 14
};
question.Answers.Add(a);
question.Answers.Add(b);
_uow.Questions.Update(question);
_uow.Commit();

This does work for more than one child:

It creates AnswerID's 1000 and 1001 but I want new Id's to be created by the database.

var a = new Answer
{
    AnswerId = 1000,
    Text = "AAA",
    QuestionId = 14
};
var b = new Answer
{
    AnswerId = 1001,
    Text = "BBB",
    QuestionId = 14
};
question.Answers.Add(a);
question.Answers.Add(b);
_uow.Questions.Update(question);
_uow.Commit();

Does not work:

Compiler error. Can't convert null to int

var a = new Answer
{
    AnswerId = null,
    Text = "AAA",
    QuestionId = 14    
};
var b = new Answer
{
    AnswerId = null,
    Text = "BBB",
    QuestionId = 14
};
question.Answers.Add(a);
question.Answers.Add(b);
_uow.Questions.Update(question);
_uow.Commit();

Doesn't work:

ObjectStateManager cannot track multiple objects with the same key.

var a = new Answer
{
    Text = "AAA",
    QuestionId = 14
};
var b = new Answer
{
    Text = "BBB",
    QuestionId = 14
};
question.Answers.Add(a);
question.Answers.Add(b);
_uow.Questions.Update(question);
_uow.Commit();

In my application, I have one or more new Answer objects generated on the client and then these are sent to the server. Above I am simulating what will happen without adding the client into the question. Note that the adding of all Answers to the Question object is done on the client and then comes over in a JSON string to the server. It is then deserialized to a Question Object like this:

public HttpResponseMessage PutQuestion(int id, Question question) {
    _uow.Questions.Update(question);
    _uow.Commit();

I want each Answer objects to be created with a new identity ID, for these to be added to the Question object and for the Question object to be returned back in the normal way.

I don't know how this can be done. All my simple tests so far don't work. Please note this is a variation on an earlier question by our group member which was less clear and which I am trying to close. This question is I hope more clear.

Notes:

Here is the way update is coded:

public virtual void Update(T entity)
{
    DbEntityEntry dbEntityEntry = DbContext.Entry(entity);
    if (dbEntityEntry.State == EntityState.Detached)
    {
        DbSet.Attach(entity);
    }  
    dbEntityEntry.State = EntityState.Modified;
}

Solution

  • Did you mentioned that you are adding a two times...?!

    question.Answers.Add(a);
    question.Answers.Add(a);
    

    Usually, to add items which their id is identity, you must skip setting the id. You also should add the [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] attribute to these IDs:

    public class Answer
    {
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int AnswerId { get; set; }
        public string Text { get; set; }
        public int QuestionId { get; set; }
        public virtual Question Question { get; set; }
    }
    

    And add data like this:

    var a = new Answer{
        Text = "AAA",
        QuestionId = 14
    };
    
    var b = new Answer
    {
        Text = "BBB",
        QuestionId = 14
    };
    
    dbContext.Answers.Add(a);
    dbContext.Answers.Add(b);
    
    dbContext.SaveChanges();
    
    // ...