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

Question

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

Answer 1

I have run into the same identity "limitation" as well. It turns out that if you add a parent and any children, EF can handle the fact that the parent and children are all being added together. You run into problems when you Update the parent and insert two children at the same time. If you attach the parent, EF will automatically pick up those two children and attach them whether you want it to or not. Since we want it to auto generate the Id, we wouldn't set the primary key of the children. However, EF cannot handle items with the same Primary key when the parent is an Update and blows up since both have the same PK of 0 for both children.

The only way I have found around this is to manually set the ids of the children to different numbers. I usually set the first child's Id to -1, then -2 for the second child, and so on. This will cause EF to save the children and the key will automatically be updated due to the Identity running on the database because -1 and -2 are not valid identity values.

However, this will cause great pain if you have a 3rd level or beyond. Not only do you have to update this PK on each child, but then you'd have to update the FK on any of its children to this new -1 or -2 value. Otherwise, the save will fail again!

The only other option I see is really just to insert one child at a time and call save so the context isn't dealing with more than one object with the same PK, but that kind of defeats the purpose of an ORM...

Answer 2

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

// ...

Answer 3

Try these things:

• use the Create() method of DbSet
• add the new instances to the Answers collection of your Context

You have set the QuestionId appropriately for EF to realise the relationship. Also, do not explicitly set AnswerId to zero.

var a = new _uow.Answers.Create();
a.Text = "AAA";
a.QuestionId = 14;
_uow.Answers.Add(a);

var b = new _uow.Answers.Create();
b.Text = "BBB";
b.QuestionId = 14;
_uow.Answers.Add(a);

You may need to make a call to _uow.ChangeTracker.DetectChanges() if you plan on querying the Answers collection of Question 14

Answer 4

If you have correctly declared the Id as Key and as being DBGenerated identity. Then EF will allow you ADD many of these to the context before saving. You can NOT ATTACH items with the same key. Attach is meant for offline data, put in context, set its state and save type scenarios.

You have either used the Same instance twice and with EF tracking by default caused a mess. Or somehow used ATTACH twice. Make sure you are handling your instances cleanly.*

eg

public class BaseEntityLongConfiguration<T> : EntityTypeConfiguration<T> where T : BaseObjectLong {
    public BaseEntityLongConfiguration(DatabaseGeneratedOption DGO = DatabaseGeneratedOption.Identity) {

        // Primary Key
        this.HasKey(t => t.Id);

        // Properties
        //Id is an indent allocated by DB
        this.Property(t => t.Id).HasDatabaseGeneratedOption(DGO); // default to db generated

        this.Property(t => t.RowVersion)   // for concurrency
            .IsRequired()
            .IsFixedLength()
            .HasMaxLength(8)
            .IsRowVersion();
    }
}

A just tried a simple Test to check it works (in ef5)

public class ExampleLog  {
    public virtual long Id   { get; set; }
    public virtual string MessageText { get; set; }
}

[TestMethod]
    public void ExampleLogTest() {
        var e1 = new ExampleLog();
        e1.MessageText = "example1";
        var e2 = new ExampleLog();
        e2.MessageText = "example2";
        _context.Set<ExampleLog>().Add(e1);
        _context.Set<ExampleLog>().Add(e2);
     var res =   _context.SaveChanges();
      Debug.WriteLine("result expected 2->" + res.ToString());
    }

edit: At request, adding save Respository pattern, BAsic sample, error handling removed

public class RepositoryBase<TPoco> : where TPoco :    BaseObject {
    public RepositoryBase(BosBaseDbContext context) { Context = context; }

....

   /// <summary>
    /// Add new POCO 
    /// </summary>
    public virtual OperationResult Add(TPoco poco) {
        var opResult = new OperationResult();
        try {
          Context.Set<TPoco>().Add(poco);
        }
        catch (Exception ex) {
         .... custom error tool
            return opResult;
        }
        return opResult;
    }
     /// <summary>
    /// Poco must already be attached,, detect chnages is triggered
    /// </summary>
    public virtual OperationResult Change(TPoco poco) {
        var opResult = new OperationResult();
        try {    // ONLY required if NOT using chnage tracking enabled
            Context.ChangeTracker.DetectChanges();
        }
        catch (Exception ex) {
         .... custom error tool
            return opResult;
        }
        return opResult;
    }

Answer 5

as i was going through the same issue. So thought to submit my solution. It will be helpful for the other persons who will be searching-

using (var db = new EFService())
{
    var question= db.Question.Single(p => p.QuestionID == QuestionID);
    question.Answers.Add(a);
    question.Answers.Add(b);
    db.SaveChanges();
}