EF Core 7.0新特性:ExecuteUpdate

终于!经过漫长的等待，EF Core 7.0现在有一个本地支持的方式来运行UPDATE(也删除)语句，同时还允许您使用任意的LINQ查询(。其中(u =>…))，而无需首先从数据库中检索相关实体:新的内置方法称为ExecuteUpdate -参见“EF Core 7.0有什么新功能?”

ExecuteUpdate正是针对这些场景的，它可以对任何IQueryable实例进行操作，并允许您在任意数量的行上更新特定的列，同时始终在幕后发出单个update语句，使其尽可能高效。

用法:

假设您想要更新特定用户的电子邮件并显示姓名:

dbContext.Users
    .Where(u => u.Id == someId)
    .ExecuteUpdate(b => b
        .SetProperty(u => u.Email, "NewEmail@gmail.com")
        .SetProperty(u => u.DisplayName, "New Display Name")
    );

如您所见，ExecuteUpdate要求您对SetProperty方法进行一次或多次调用，以指定要更新哪个属性，以及要将什么新值赋给它。

EF Core会将此转换为以下UPDATE语句:

UPDATE [u]
    SET [u].[Email] = "NewEmail@gmail.com",
    [u].[DisplayName] = "New Display Name"
FROM [Users] AS [u]
WHERE [u].[Id] = someId

另外，ExecuteDelete用于删除行:

ExecuteUpdate还有一个名为ExecuteDelete的对应程序，顾名思义，它可用于一次性删除单个或多个行，而无需首先获取它们。

用法:

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

类似于ExecuteUpdate, ExecuteDelete将在幕后生成DELETE SQL语句——在本例中，是以下语句:

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

另注:

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.

我已经在我的存储库基类中添加了一个额外的更新方法，它类似于脚手架生成的更新方法。它不是将整个对象设置为“modified”，而是设置一组单独的属性。(T是类的泛型参数。)

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

然后调用，例如:

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

我喜欢去数据库一趟。不过，为了避免重复属性集，最好在视图模型中这样做。我还没有这样做，因为我不知道如何避免将视图模型验证器上的验证消息带入域项目。

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

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

只是为了增加更多的选择。您还可以从数据库中获取对象，并使用像auto Mapper这样的自动映射工具来更新您想要更改的记录部分。

我真的很喜欢公认的答案。我相信还有另一种方法来解决这个问题。假设您有一个非常短的属性列表，您不希望在视图中包含这些属性，因此在更新实体时，这些属性将被省略。假设这两个字段是Password和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();   

这个例子允许您在向Users表和View中添加新字段后，基本上不需要考虑业务逻辑。