Best way to update only changed fields with Entity Framework

Question

I am currently doing this:

Example:

public update(Person model) { // Here model is model return from form on post var oldobj = db.Person.where(x=>x.ID = model.ID).SingleOrDefault(); db.Entry(oldobj).CurrentValues.SetValues(model); } 

It works, but for example

I have 50 columns in my table, but I only displayed 25 fields in my form (I need to partially update the table, while the remaining 25 columns keep the same old value)

I know this can be achieved by "matching columns one by one" or by creating "hidden fields for the remaining 25 columns."

Just wondering if there is an elegant way to do this with less effort and optimal performance?

Answer 1

This is a very good question. By default, I found that as long as change tracking is turned on (by default, unless you turn it off), the Entity Framework will do a good job of applying to the database only what you ask to change.

So, if you change only one field against an object, and then call SaveChanges (), EF will update this field only when SaveChanges () is called.

The problem is that when matching a view model with an entity object, all values ​​are overwritten. Here is my way to handle this:

In this example, you have one object named Person:

 Person ====== Id - int FirstName - varchar Surname - varchar Dob - smalldatetime 

Now suppose we want to create a view model that will only update the Dob, and leave all the other fields exactly as they are, that's how I do it.

First create a view model:

 public class PersonDobVm { public int Id { get; set; } public DateTime Dob { get; set; } public void MapToModel(Person p) { p.Dob = Dob; } } 

Now write the code in approximately the following way (you will need to change it according to your context name, etc.):

 DataContext db = new DataContext(); Person p = db.People.FirstOrDefault(); // you would have this posted in, but we are creating it here just for illustration var vm = new PersonDobVm { Id = p.Id, // the Id you want to update Dob = new DateTime(2015, 1, 1) // the new DOB for that row }; vm.MapToModel(p); db.SaveChanges(); 

The MapToModel method can be even more complex and perform all kinds of additional checks before assigning the fields of the view model to the object object.

In any case, the result of calling SaveChanges is the following SQL:

 exec sp_executesql N'UPDATE [dbo].[Person] SET [Dob] = @0 WHERE ([Id] = @1) ',N'@0 datetime2(7),@1 int',@0='2015-01-01 00:00:00',@1=1 

So you can clearly see that the Entity Framework did not try to update any other fields - just a Dob field.

I know that in your example you want to avoid manually coding each job, but I think this is the best way. You remove all this on your virtual machine so that it does not interfere with your main code, and in this way you can satisfy specific needs (i.e. Compound types there, data validation, etc.). Another option is to use AutoMapper, but I don't think they are safe. If you use AutoMapper and recorded “Dob” as “Doob” in your virtual machine, it will not display “Doob” on “Dob” and will not tell you about it! It will fail, the user will think that everything is in order, but the change will not be saved.

If you wrote "Dob" as "Doob" in your VM, the compiler will warn you that MapToModel () refers to "Dob", but you only have a property in your virtual machine called "Doob".

Hope this helps you.

Answer 2

I swear EntityFramework.Extended. Nuget link

This allows you to write:

 db.Person .Where(x => x.ID == model.ID) .Update(p => new Person() { Name = newName, EditCount = p.EditCount+1 }); 

This is very clearly translated into SQL.

Answer 3

I solved the problem using FormCollection to display the used element in the form and only change these columns in the database.

I have provided my sample code below; Great if he can help someone else

 // Here // collection = FormCollection from Post // model = View Model for Person var result = db.Person.Where(x => x.ID == model.ID).SingleOrDefault(); if (result != null) { List<string> formcollist = new List<string>(); foreach (var key in collection.ToArray<string>()) { // Here apply your filter code to remove system properties if any formcollist.Add(key); } foreach (var prop in result.GetType().GetProperties()) { if( formcollist.Contains(prop.Name)) { prop.SetValue(result, model.GetType().GetProperty(prop.Name).GetValue(model, null)); } } db.SaveChanges(); }