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 ;-)

Answer 1

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, "NewEmail@gmail.com")
        .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] = "NewEmail@gmail.com",
    [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.

Answer 2

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.

Answer 3

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

Answer 4

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();
    }
}

Answer 5

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.

Answer 6

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..

Answer 7

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();

Answer 8

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.

Answer 9

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();