Entity Framework 5 Updating a Record

Question

I have been exploring different methods of editing/updating a record within Entity Framework 5 in an ASP.NET MVC3 environment, but so far none of them tick all of the boxes I need. I'll explain why.

I have found three methods to which I'll mention the pros and cons:

Method 1 - Load original record, update each property

var original = db.Users.Find(updatedUser.UserId);

if (original != null)
{
    original.BusinessEntityId = updatedUser.BusinessEntityId;
    original.Email = updatedUser.Email;
    original.EmployeeId = updatedUser.EmployeeId;
    original.Forename = updatedUser.Forename;
    original.Surname = updatedUser.Surname;
    original.Telephone = updatedUser.Telephone;
    original.Title = updatedUser.Title;
    original.Fax = updatedUser.Fax;
    original.ASPNetUserId = updatedUser.ASPNetUserId;
    db.SaveChanges();
}    

Pros

• Can specify which properties change
• Views don't need to contain every property

Cons

• 2 x queries on database to load original then update it

Method 2 - Load original record, set changed values

var original = db.Users.Find(updatedUser.UserId);

if (original != null)
{
    db.Entry(original).CurrentValues.SetValues(updatedUser);
    db.SaveChanges();
}

Pros

• Only modified properties are sent to database

Cons

• Views need to contain every property
• 2 x queries on database to load original then update it

Method 3 - Attach updated record and set state to EntityState.Modified

db.Users.Attach(updatedUser);
db.Entry(updatedUser).State = EntityState.Modified;
db.SaveChanges();

Pros

• 1 x query on database to update

Cons

• Can't specify which properties change
• Views must contain every property

Question

My question to you guys; is there a clean way that I can achieve this set of goals?

• Can specify which properties change
• Views don't need to contain every property (such as password!)
• 1 x query on database to update

I understand this is quite a minor thing to point out but I may be missing a simple solution to this. If not method one will prevail ;-)

Requital

Use ViewModels and a good mapping engine ? You get only "properties to update" to populate your view (and then to update). There's still will be the 2 queries for updating (get original + update it), but I wouldn't call this a "Con". If that's your only performance problem, you're an happy man ;) – Significancy

Thanks @RaphaëlAlthaus, very valid point. I could do this, but I have to create CRUD operation for a number of tables so I'm looking for a method that can work with the model directly to save me creating n-1 ViewModel for each Model. – Requital

Well, in my current project (many entities too) we started with working on Models, thinking we would lose time working with ViewModels. We're now going to ViewModels, and with (not negligible) infrastructure work at start, it's far, far, far clearer and easier to maintain now. And more secure (no need to fear about malicious "hidden fields" or things like that) – Significancy

And no more (awful) ViewBags to populate your DropDownLists (we have at least one DropDownList on almost all our CRU(D) views...) – Significancy

I think you are right, my bad for trying to overlook ViewModels. Yes, ViewBag just seems a bit dirty at times. I usually go one step further as per Dino Esposito's blog and create InputModels too, a tad belt and braces but it works quite well. Just means 2 extra models per models - doh ;-) – Requital

Method 1 has lazy loads problem and potentially causing a lot of unexpected transactions and crippling performance. – Dahlgren

What mapping engine / pattern would you recommend to achieve this type of behavior? Something like automapper seems like it'd be overkill. I wish that the saving/update EF entity workflow was more tightly coupled with the mvc modelbinding engine. Maybe in EF 7. – Hardener

I agree. If I have an entity with 10 fields, I should be able to pass in the Id, and one other field and only that field gets updated and it happens with only one query to the db. – Ozenfant

The root of the problem is that the properties on the object being updated don't get marked as dirty when you set them because they're POCO. In contrast LLBLGen for example generates entities for you which automatically set a field as dirty when you set its value. – Seasickness

Just a noob question, from where did you get the updatedUser? I'm trying to implement the accepted answer to my program but I don't know what updateUser is – Leptosome

updatedUser is the User object which has different data from the original. In the accepted answer below we're saying you want to save the user, but only the Email property – Requital

Also, you can use newly released library which will automatically set the state of all entities in the entity graph. You can read my answer to the similar question. – Flipper

Answer 1

You are looking for:

db.Users.Attach(updatedUser);
var entry = db.Entry(updatedUser);
entry.Property(e => e.Email).IsModified = true;
// other changed properties
db.SaveChanges();

Scotopia

thanks a lot, I tried this just after I posted the question the other day and it kept giving me an error but today it works and is exactly what I wanted ;-) – Requital

hi @Ladislav Mrnka,if I want to update all properties at once, can I use the below code? db.Departments.Attach(department); db.Entry(department).State = EntityState.Modified; db.SaveChanges(); – Hindi

One of the problems with this approach is that you can't mock db.Entry(), which is a serious PITA. EF has a reasonably good mocking story elsewhere - it's pretty annoying that (so far as I can tell) they don't have one here. – Geothermal

For me, just db.Users.Attach(updatedUser);db.SaveChanges(); alone do the trick. – Mcglynn

@Foysal Doing context.Entry(entity).State = EntityState.Modified alone is enough no need to do the attach. It will be automatically attached as its modified... – Hebetic

@Sandman4, that means every other property needs to be there and be set to the current value. In some application designs, this isn't feasible. – Haematothermal

how about references? This does not seem to work with navigation properties. How can we update the code to make the context understand that a navigation property has been modified. – Abstergent

"EF has a reasonably good mocking story" - Why does everything nowadays have to be a story? – Seasickness

@Franva context.Entry is new to EF5 and above. msdn.microsoft.com/en-us/library/gg696238(v=vs.103).aspx – Barbitone

How would this work if there was a one to many relationship from the Users table, for example one User could have multiple Roles held in the Role table. So if you wanted to update the User details, and the User.Roles list, how can you get EF to attach the child table? – Derosier

@LadislavMrnka Can you please explain your answer? – Enhance

Answer 2

I really like the accepted answer. I believe there is yet another way to approach this as well. Let's say you have a very short list of properties that you wouldn't want to ever include in a View, so when updating the entity, those would be omitted. Let's say that those two fields are Password and SSN.

db.Users.Attach(updatedUser);

var entry = db.Entry(updatedUser);
entry.State = EntityState.Modified;

entry.Property(e => e.Password).IsModified = false;
entry.Property(e => e.SSN).IsModified = false;   

db.SaveChanges();   

This example allows you to essentially leave your business logic alone after adding a new field to your Users table and to your View.

Giblets

Still I will receive an error if I don't specify a value for SSN property, even though I set IsModified to false it still validate the property against the model rules. So if the property is marked as NOT NULL it will fail if I dont set any value different than null. – Jacquie

You won't receive an error because those fields won't be in your form. You leave out the fields you will definitely not be updating, grab the entry from the database using the form passed back by attaching it, and tell the entry that those fields aren't being modified. Model validation is controlled in the ModelState, not in the context. This example is referencing an existing user, hence "updatedUser". If your SSN is a required field, it would have been there when it was first created. – Giblets

If I understand correctly, "updatedUser" is an instance of an object already populated with a FirstOrDefault() or similar, so I am updating only the properties I changed and setting others to ISModified=false. This works fine. But, what I am trying to do is to update an object without populating it first, without making any FirstOrDefault() bofore the update. This is when I receive an error if I don't specify a value for all the requiered fields, even thoug I set ISModified = false on those properties. entry.Property(e => e.columnA).IsModified = false; Without this line ColumnA will fail. – Jacquie

What you are describing is creating a new entity. This applies to updating only. – Giblets

RolandoCC, put db.Configuration.ValidateOnSaveEnabled = false; before the db.SaveChanges(); – Command

Answer 3

foreach(PropertyInfo propertyInfo in original.GetType().GetProperties()) {
    if (propertyInfo.GetValue(updatedUser, null) == null)
        propertyInfo.SetValue(updatedUser, propertyInfo.GetValue(original, null), null);
}
db.Entry(original).CurrentValues.SetValues(updatedUser);
db.SaveChanges();

Embrasure

This seems like a really nice solution - no muss or fuss; you don't have to manually specify properties and it takes into account all of the OPs bullets - is there any reason this does not have more votes? – Hardener

It doesn't though. It has one of the biggest "cons", more than one hit to the database. You'd still have to load the original with this answer. – Giblets

@Giblets why do you say it hits the database more than once? I don't see that happening unless using SetValues() has that effect but that doesn't seem like it would be true. – Medici

@Medici I think I must have been asleep when I wrote that. Apologies. The actual problem is overriding an intended null value. If the updated user no longer has reference to something, it would not be right to replace it with the original value if you meant to clear it. – Giblets

Answer 4

I have added an extra update method onto my repository base class that's similar to the update method generated by Scaffolding. Instead of setting the entire object to "modified", it sets a set of individual properties. (T is a class generic parameter.)

public void Update(T obj, params Expression<Func<T, object>>[] propertiesToUpdate)
{
    Context.Set<T>().Attach(obj);

    foreach (var p in propertiesToUpdate)
    {
        Context.Entry(obj).Property(p).IsModified = true;
    }
}

And then to call, for example:

public void UpdatePasswordAndEmail(long userId, string password, string email)
{
    var user = new User {UserId = userId, Password = password, Email = email};

    Update(user, u => u.Password, u => u.Email);

    Save();
}

I like one trip to the database. Its probably better to do this with view models, though, in order to avoid repeating sets of properties. I haven't done that yet because I don't know how to avoid bringing the validation messages on my view model validators into my domain project.

Seasickness

Aha... separate project for view models and separate project for repositories that work with view models. – Seasickness

Really like this approach. Still explicit, but easier. – Marsland

Answer 5

public interface IRepository
{
    void Update<T>(T obj, params Expression<Func<T, object>>[] propertiesToUpdate) where T : class;
}

public class Repository : DbContext, IRepository
{
    public void Update<T>(T obj, params Expression<Func<T, object>>[] propertiesToUpdate) where T : class
    {
        Set<T>().Attach(obj);
        propertiesToUpdate.ToList().ForEach(p => Entry(obj).Property(p).IsModified = true);
        SaveChanges();
    }
}

Abridge

Why not just DbContext.Attach(obj); DbContext.Entry(obj).State = EntityState.Modified; – Flynn

This controls the set part of the update statement. – Odontoblast

Answer 6

EF Core 7.0 new feature: ExecuteUpdate

Finally! After a long wait, EF Core 7.0 now has a natively supported way to run UPDATE (and also DELETE) statements while also allowing you to use arbitrary LINQ queries (.Where(u => ...)), without having to first retrieve the relevant entities from the database: The new built-in method called ExecuteUpdate — see "What's new in EF Core 7.0?".

ExecuteUpdate is precisely meant for these kinds of scenarios, it can operate on any IQueryable instance, and lets you update specific columns on any number of rows, while always issuing a single UPDATE statement behind the scenes, making it as efficient as possible.

Usage:

Imagine you want to update a specific user's email and display name:

dbContext.Users
    .Where(u => u.Id == someId)
    .ExecuteUpdate(b => b
        .SetProperty(u => u.Email, "[email protected]")
        .SetProperty(u => u.DisplayName, "New Display Name")
    );

As you can see, ExecuteUpdate requires you to make one or more calls to the SetProperty method, to specify which property to update, and also what new value to assign to it.

EF Core will translate this into the following UPDATE statement:

UPDATE [u]
    SET [u].[Email] = "[email protected]",
    [u].[DisplayName] = "New Display Name"
FROM [Users] AS [u]
WHERE [u].[Id] = someId

Also, ExecuteDelete for deleting rows:

There's also a counterpart to ExecuteUpdate called ExecuteDelete, which, as the name implies, can be used to delete a single or multiple rows at once without first fetching them.

Usage:

// Delete users that haven't been active in 2022:
dbContext.Users
    .Where(u => u.LastActiveAt.Year < 2022)
    .ExecuteDelete();

Similar to ExecuteUpdate, ExecuteDelete will generate DELETE SQL statements behind the scenes — in this case, the following one:

DELETE FROM [u]
FROM [Users] AS [u]
WHERE DATEPART(year, [u].[LastActiveAt]) < 2022

---

Other notes:

• Keep in mind that both ExecuteUpdate and ExecuteDelete are "terminating", meaning that the update/delete operation will take place as soon as you call the method. You're not supposed to call dbContext.SaveChanges() afterwards.
• If you're curious about the SetProperty method, and you're confused as to why ExectueUpdate doesn't instead receive a member initialization expression (e.g. .ExecuteUpdate(new User { Email = "..." }), then refer to this comment (and the surrounding ones) on the GitHub issue for this feature.
• Furthermore, if you're curious about the rationale behind the naming, and why the prefix Execute was picked (there were also other candidates), refer to this comment, and the preceding (rather long) conversation.
• Both methods also have async equivalents, named ExecuteUpdateAsync, and ExecuteDeleteAsync respectively.

Badge

I think I have found what am looking for. thanks man – Ader

Answer 7

Just to add to the list of options. You can also grab the object from the database, and use an auto mapping tool like Auto Mapper to update the parts of the record you want to change..

Bonnard

Answer 8

Depending on your use case, all the above solutions apply. This is how i usually do it however :

For server side code (e.g. a batch process) I usually load the entities and work with dynamic proxies. Usually in batch processes you need to load the data anyways at the time the service runs. I try to batch load the data instead of using the find method to save some time. Depending on the process I use optimistic or pessimistic concurrency control (I always use optimistic except for parallel execution scenarios where I need to lock some records with plain sql statements, this is rare though). Depending on the code and scenario the impact can be reduced to almost zero.

For client side scenarios, you have a few options

1. Use view models. The models should have a property UpdateStatus(unmodified-inserted-updated-deleted). It is the responsibility of the client to set the correct value to this column depending on the user actions (insert-update-delete). The server can either query the db for the original values or the client should send the original values to the server along with the changed rows. The server should attach the original values and use the UpdateStatus column for each row to decide how to handle the new values. In this scenario I always use optimistic concurrency. This will only do the insert - update - delete statements and not any selects, but it might need some clever code to walk the graph and update the entities (depends on your scenario - application). A mapper can help but does not handle the CRUD logic

2. Use a library like breeze.js that hides most of this complexity (as described in 1) and try to fit it to your use case.

Hope it helps

Kurtiskurtosis

Answer 9

There are some really good answers given already, but I wanted to throw in my two cents. Here is a very simple way to convert a view object into a entity. The simple idea is that only the properties that exist in the view model get written to the entity. This is similar to @Anik Islam Abhi's answer, but has null propagation.

public static T MapVMUpdate<T>(object updatedVM, T original)
{
    PropertyInfo[] originalProps = original.GetType().GetProperties();
    PropertyInfo[] vmProps = updatedVM.GetType().GetProperties();
    foreach (PropertyInfo prop in vmProps)
    {
        PropertyInfo projectProp = originalProps.FirstOrDefault(x => x.Name == prop.Name);
        if (projectProp != null)
        {
            projectProp.SetValue(original, prop.GetValue(updatedVM));
        }
    }
    return original;
}

Pros

• Views don't need to have all the properties of the entity.
• You never have to update code when you add remove a property to a view.
• Completely generic

Cons

• 2 hits on the database, one to load the original entity, and one to save it.

To me the simplicity and low maintenance requirements of this approach outweigh the added database call.

Nieshanieto