Complete Insert/Update/Delete of Child Entities in Entity Framework

Question

I know it has been asked before, but after long hours of searching and coding I can't get to a working and clean approach. Here is what I have:

public class QuestionModel
{
    public int QuestionID { get; set; }
    public string QuestionText { get; set; }

    public IList<QuestionChoiceModel> Choices { get; set; }
}

public class QuestionChoiceModel
{
    public int ChoiceID { get; set; }
    public string ChoiceText { get; set; }
}

I'm using EF 5 for this ASP.Net MVC application. Generic Repository Pattern and Dependency Injection with Ninject using InRequestScope() are in place and work smoothly. These models are mapped to/from entities without a problem.

Adding new Questions to database is straight forward. I set Question property of some QuestionChoice instances, and EF handles the rest.

The problem is about updates. Assume we have a Question in database with 3 QuestionChoices:

ChoiceID    QuestionID    ChoiceText
--------    ----------    ----------
1           1             blah blah
2           1             blah blah
3           1             blah blah

When edit page of a Question opens (GET: /Questions/Edit/1), I show these 3 choices using a foreach in Razor. I've written some JQuery code that adds or deletes required markup for input elements if user wants to. So, the QuestionChoice with ID=1 might be edited on client, ID=2 might be deleted, and a new ID=4 might be added. The form data is bound back to a QuestionModel perfectly when user presses the Save button (POST: /Questions/Edit/1). The model is mapped to a Question entity correctly. That is where the story begins!

Now the Question entity has a collection of QuestionChoices some of which are already in database, some should be added to database, and some should be deleted from database.

I've read many posts like: Entity Framework not saving modified children

I can handle edits in that dirty way. And also new records by:

this._context.Entry(choice).State = EntityState.Added;

But I'm looking for a more elegant way. And also handle records that should be deleted. Is there a good approach to handle complete insert/update/delete of child entities in this scenario using EF? Honestly, I expected more from EF.

Answer 1

This is a tough problem. Unfortunately, I cannot offer the solution that you prefer. I do not believe it is possible. EF cannot track changes made to your entities unless they are made within the context that the entities are retrieved - which is impossible in a web environment. The only way for this to be possible would be to retrieve the Question object (within a context) after the POST to /Questions/Edit/1, and to perform a type of "merge" between the POSTed Question, and the Question retrieved from the database. This would include assigning properties on your QuestionModel and each QuestionChoiceModel retrieved from the database using your POSTed QuestionModel. I will say that this wouldn't be great practice either, because you WILL forget to include a property. It will happen.

The best (and easiest) solution I can provide would be to add/edit your QuestionModel and QuestionChoiceModel(s) using the .Entry() method above. You will sacrifice "best practice" here for a solution that will be less error-prone.

QuestionModel questionFromDb;
QuestionModel questionFromPost;

QuestionModelChoice[] deletedChoices = questionFromDb.Choices.Where(c => !questionFromPost.Choices.Any(c2 => c2.Id == c.Id));


using (var db = new DbContext())
{
    db.Entry(questionFromPost).State = questionFromPost.Id == 0 ? EntityState.Added : EntityState.Modified;

    foreach(var choice in questionFromPost.Choices)
    {
        db.Entry(choice).State = choice.Id == 0 ? EntityState.Added : EntityState.Modified;
    }

    foreach(var deletedChoice in deletedChoices)
    {
        db.Entry(deletedChoice).State = EntityState.Deleted;
    }

    db.SaveChanges();
}