Best Way to Update only modified fields with Entity Framework

asp.net-mvc entity-framework

Question

Currently I am doing like this:

For 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 displayed only 25 fields in my form (I need to partially update my table, with remaining 25 column retain same old value)

I know it can be achieve by "mapping columns one by one" or by creating "hidden fields for those remaining 25 columns".

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

1
12
10/14/2015 11:42:51 PM

Accepted Answer

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

So if you only change 1 field against the object and then call SaveChanges(), EF will only update that 1 field when you call SaveChanges().

The problem here is that when you map a view model into an entity object, all of the values get overwritten. Here is my way of handling this:

In this example, you have a single entity called Person:

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

Now let's say we want to create a view model which will only update Dob, and leave all other fields exactly how they are, here is how I do that.

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 roughly as follows (you'll have to alter it to match 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 could be even more complicated and do all kinds of additional checks before assigning the view model fields to the entity object.

Anyway, the result when SaveChanges is called 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, Entity Framework has not attempted to update any other fields - just the Dob field.

I know in your example you want to avoid coding each assignment by hand, but I think this is the best way. You tuck it all away in your VM so it does not litter your main code, and this way you can cater for specific needs (i.e. composite types in there, data validation, etc). The other option is to use an AutoMapper, but I do not think they are safe. If you use an AutoMapper and spelt "Dob" as "Doob" in your VM, it would not map "Doob" to "Dob", nor would it tell you about it! It would fail silently, the user would think everything was ok, but the change would not be saved.

Whereas if you spelt "Dob" as "Doob" in your VM, the compiler will alert you that the MapToModel() is referencing "Dob" but you only have a property in your VM called "Doob".

I hope this helps you.

10
10/16/2015 10:18:14 AM

Popular Answer

I swear by EntityFramework.Extended. Nuget Link

It lets you write:

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

Which is very clearly translated into SQL.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow