Full insert / update / delete child objects in Entity infrastructure

Question

I know this was asked before, but after many hours of searching and coding, I can’t find 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 am using EF 5 for this ASP.Net MVC application. A typical repository pattern and dependency injection using Ninject using InRequestScope() are in place and run smoothly. These models are mapped to / without objects.

Adding new questions to the database directly. I set the Question property for some QuestionChoice instances, and EF handles the rest.

The problem is in the updates. Suppose we have a question in a database with 3 QuestionChoices:

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

When the question editing page opens (GET: / questions / Edit / 1), I show these 3 options using foreach in Razor. I wrote jQuery code that adds or removes the required markup for input elements if the user wants. Thus, QuestionChoice with ID = 1 can be edited on the client, ID = 2 can be deleted, and a new ID = 4 can be added. The form data is attached to the QuestionModel perfectly when the user clicks the "Save" button (POST: / questions / Edit / 1). The model is correctly mapped to the question object. That's where the story begins!

Now the Question object has a QuestionChoices set, some of which are already in the database, some of which must be added to the database, and some of them must be deleted from the database.

I read a lot of posts like: Entity Framework does not save modified children

I can handle changes in such a dirty way. And also new entries:

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

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

Answer 1

This is a difficult problem. Unfortunately, I cannot offer the solution you prefer. I do not believe that this is possible. EF cannot track changes made to your objects unless they are made in the context in which the objects are retrieved, which is not possible in a web environment. The only way to do this would be to get the Question object (inside the context) after the POST in / questions / Edit / 1 and do a “merge” type between the POSTed Question and the Question obtained from the database. This would include assigning properties to the QuestionModel and each QuestionChoiceModel retrieved from the database using your POSTed QuestionModel . I will say that this will not be a big practice either, because you will forget to include the property. It will happen.

The best (and easiest) solution I can provide is to add / edit 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(); } 

Answer 2

This is just a proof of concept.

The controller has a func UpdateModel , but it will not work with a more complex model that includes child records. Find TestUpdate .

Rule # 1: Each table has a PK Id column.

Rule No. 2: Each FK must be installed.

Rule number 3: You must delete the cascading deletion. if you want to delete the corresponding entry.

Rule No. 4: A new record must have Id = 0 or it will be better Null, but Id cannot be zero.

 public class TestController<T> : Controller where T : class { const string PK = "Id"; protected Models.Entities con; protected System.Data.Entity.DbSet<T> model; public TestController() { con = new Models.Entities(); model = con.Set<T>(); } // GET: Default public virtual ActionResult Index() { ViewBag.Result = TempData["Result"]; TempData["Result"] = null; var list = model.ToList(); return View(list); } [HttpGet] public virtual ActionResult AddEdit(string id) { int nId = 0; int.TryParse(id, out nId); var item = model.Find(nId); return View(item); } [HttpPost] public virtual ActionResult AddEdit(T item) { TestUpdate(item); con.SaveChanges(); return RedirectToAction("Index"); } [HttpGet] public virtual ActionResult Remove(string id) { int nId = 0; int.TryParse(id, out nId); if (nId != 0) { var item = model.Find(nId); con.Entry(item).State = System.Data.Entity.EntityState.Deleted; con.SaveChanges(); } return Redirect(Request.UrlReferrer.ToString()); } private void TestUpdate(object item) { var props = item.GetType().GetProperties(); foreach (var prop in props) { object value = prop.GetValue(item); if (prop.PropertyType.IsInterface && value != null) { foreach (var iItem in (System.Collections.IEnumerable)value) { TestUpdate(iItem); } } } int id = (int)item.GetType().GetProperty(PK).GetValue(item); if (id == 0) { con.Entry(item).State = System.Data.Entity.EntityState.Added; } else { con.Entry(item).State = System.Data.Entity.EntityState.Modified; } } } 

Here is the project https://github.com/mertuarez/AspMVC_EF/

You need to create a Controler for the model and create a view for the action. In the case of AddEdit, you need to create a Template editor for subtypes.