SQL 如何使用实体框架仅更新一个字段？

How to update only one field using Entity Framework?

提问by h3n

Here's the table

这是桌子

Users

用户

UserId
UserName
Password
EmailAddress

and the code..

和代码..

public void ChangePassword(int userId, string password){
//code to update the password..
}

回答by Stuart

Ladislav's answer updated to use DbContext (introduced in EF 4.1):

Ladislav 的答案更新为使用 DbContext（在 EF 4.1 中引入）：

public void ChangePassword(int userId, string password)
{
  var user = new User() { Id = userId, Password = password };
  using (var db = new MyEfContextName())
  {
    db.Users.Attach(user);
    db.Entry(user).Property(x => x.Password).IsModified = true;
    db.SaveChanges();
  }
}

回答by Ladislav Mrnka

You can tell EF which properties have to be updated in this way:

您可以通过这种方式告诉 EF 哪些属性必须更新：

public void ChangePassword(int userId, string password)
{
  var user = new User { Id = userId, Password = password };
  using (var context = new ObjectContext(ConnectionString))
  {
    var users = context.CreateObjectSet<User>();
    users.Attach(user);
    context.ObjectStateManager.GetObjectStateEntry(user)
      .SetModifiedProperty("Password");
    context.SaveChanges();
  }
}

回答by marc_s

You have basically two options:

您基本上有两种选择：

• go the EF way all the way, in that case, you would
  • load the object based on the userIdprovided - the entire object gets loaded
  • update the passwordfield
  • save the object back using the context's .SaveChanges()method

• 一路走 EF 路，在那种情况下，你会
  • 根据userId提供的加载对象 - 整个对象被加载
  • 更新password字段
  • 使用上下文的.SaveChanges()方法保存对象

In this case, it's up to EF how to handle this in detail. I just tested this, and in the case I only change a single field of an object, what EF creates is pretty much what you'd create manually, too - something like:

在这种情况下，如何详细处理这取决于 EF。我刚刚对此进行了测试，在我只更改对象的单个字段的情况下，EF 创建的内容也几乎是您手动创建的内容 - 类似于：

`UPDATE dbo.Users SET Password = @Password WHERE UserId = @UserId`

So EF is smart enough to figure out what columns have indeed changed, and it will create a T-SQL statement to handle just those updates that are in fact necessary.

所以 EF 足够聪明，可以找出哪些列确实发生了变化，并且它会创建一个 T-SQL 语句来处理那些实际上需要的更新。

• you define a stored procedure that does exactly what you need, in T-SQL code (just update the Passwordcolumn for the given UserIdand nothing else - basically executes UPDATE dbo.Users SET Password = @Password WHERE UserId = @UserId) and you create a function import for that stored procedure in your EF model and you call this function instead of doing the steps outlined above

• 您在 T-SQL 代码中定义了一个完全满足您需要的存储过程（只需更新Password给定的列，UserId而没有其他任何内容 - 基本上执行UPDATE dbo.Users SET Password = @Password WHERE UserId = @UserId），然后在 EF 模型中为该存储过程创建一个函数导入，然后调用它函数而不是执行上述步骤

回答by groggyjava

i'm using this:

我正在使用这个：

entity:

实体：

public class Thing 
{
    [Key]
    public int Id { get; set; }
    public string Info { get; set; }
    public string OtherStuff { get; set; }
}

dbcontext:

数据库上下文：

public class MyDataContext : DbContext
{
    public DbSet<Thing > Things { get; set; }
}

accessor code:

存取码：

MyDataContext ctx = new MyDataContext();

// FIRST create a blank object
Thing thing = ctx.Things.Create();

// SECOND set the ID
thing.Id = id;

// THIRD attach the thing (id is not marked as modified)
db.Things.Attach(thing); 

// FOURTH set the fields you want updated.
thing.OtherStuff = "only want this field updated.";

// FIFTH save that thing
db.SaveChanges();

回答by Edward Brey

In Entity Framework Core, Attachreturns the entry, so all you need is:

在 Entity Framework Core 中，Attach返回条目，因此您只需要：

var user = new User { Id = userId, Password = password };
db.Users.Attach(user).Property(x => x.Password).IsModified = true;
db.SaveChanges();

回答by Doku-so

While searching for a solution to this problem, I found a variation on GONeale's answer through Patrick Desjardins' blog:

在寻找此问题的解决方案时，我通过Patrick Desjardins 的博客发现了 GONeale 的答案的变体：

public int Update(T entity, Expression<Func<T, object>>[] properties)
{
  DatabaseContext.Entry(entity).State = EntityState.Unchanged;
  foreach (var property in properties)
  {
    var propertyName = ExpressionHelper.GetExpressionText(property);
    DatabaseContext.Entry(entity).Property(propertyName).IsModified = true;
  }
  return DatabaseContext.SaveChangesWithoutValidation();
}

"As you can see, it takes as its second parameter an expression of a function. This will let use this method by specifying in a Lambda expression which property to update."

"如您所见，它将函数表达式作为其第二个参数。这将通过在 Lambda 表达式中指定要更新的属性来使用此方法。"

...Update(Model, d=>d.Name);
//or
...Update(Model, d=>d.Name, d=>d.SecondProperty, d=>d.AndSoOn);

( A somewhat similar solution is also given here: https://stackoverflow.com/a/5749469/2115384)

（这里也给出了一个有点类似的解决方案：https: //stackoverflow.com/a/5749469/2115384）

The method I am currently using in my own code, extended to handle also (Linq) Expressions of type ExpressionType.Convert. This was necessary in my case, for example with Guidand other object properties. Those were 'wrapped' in a Convert() and therefore not handled by System.Web.Mvc.ExpressionHelper.GetExpressionText.

我目前在自己的代码中使用的方法，扩展到还处理 (Linq) 类型的表达式ExpressionType.Convert。这在我的情况下是必要的，例如 withGuid和其他对象属性。那些被“包装”在 Convert() 中，因此不由System.Web.Mvc.ExpressionHelper.GetExpressionText.

public int Update(T entity, Expression<Func<T, object>>[] properties)
{
    DbEntityEntry<T> entry = dataContext.Entry(entity);
    entry.State = EntityState.Unchanged;
    foreach (var property in properties)
    {
        string propertyName = "";
        Expression bodyExpression = property.Body;
        if (bodyExpression.NodeType == ExpressionType.Convert && bodyExpression is UnaryExpression)
        {
            Expression operand = ((UnaryExpression)property.Body).Operand;
            propertyName = ((MemberExpression)operand).Member.Name;
        }
        else
        {
            propertyName = System.Web.Mvc.ExpressionHelper.GetExpressionText(property);
        }
        entry.Property(propertyName).IsModified = true;
    }

    dataContext.Configuration.ValidateOnSaveEnabled = false;
    return dataContext.SaveChanges();
}

回答by GONeale

I'm late to the game here, but this is how I am doing it, I spent a while hunting for a solution I was satisified with; this produces an UPDATEstatement ONLY for the fields that are changed, as you explicitly define what they are through a "white list" concept which is more secure to prevent web form injection anyway.

我在这里玩游戏迟到了，但这就是我的方式，我花了一段时间寻找我满意的解决方案；这UPDATE仅为更改的字段生成一个语句，因为您通过“白名单”概念明确定义它们是什么，这更安全地防止 Web 表单注入。

An excerpt from my ISession data repository:

摘自我的 ISession 数据存储库：

public bool Update<T>(T item, params string[] changedPropertyNames) where T 
  : class, new()
{
    _context.Set<T>().Attach(item);
    foreach (var propertyName in changedPropertyNames)
    {
        // If we can't find the property, this line wil throw an exception, 
        //which is good as we want to know about it
        _context.Entry(item).Property(propertyName).IsModified = true;
    }
    return true;
}

This could be wrapped in a try..catch if you so wished, but I personally like my caller to know about the exceptions in this scenario.

如果您愿意，这可以包含在 try..catch 中，但我个人希望我的调用者了解这种情况下的异常。

It would be called in something like this fashion (for me, this was via an ASP.NET Web API):

它将以这种方式调用（对我来说，这是通过 ASP.NET Web API）：

if (!session.Update(franchiseViewModel.Franchise, new[]
    {
      "Name",
      "StartDate"
  }))
  throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound));

回答by PWS

I know this is an old thread but I was also looking for a similar solution and decided to go with the solution @Doku-so provided. I'm commenting to answer the question asked by @Imran Rizvi , I followed @Doku-so link that shows a similar implementation. @Imran Rizvi's question was that he was getting an error using the provided solution 'Cannot convert Lambda expression to Type 'Expression> [] ' because it is not a delegate type'. I wanted to offer a small modification I made to @Doku-so's solution that fixes this error in case anyone else comes across this post and decides to use @Doku-so's solution.

我知道这是一个旧线程，但我也在寻找类似的解决方案，并决定采用@Doku-so 提供的解决方案。我正在评论以回答@Imran Rizvi 提出的问题，我关注了显示类似实现的@Doku-so 链接。@Imran Rizvi 的问题是，他在使用提供的解决方案时遇到错误“无法将 Lambda 表达式转换为类型 'Expression> [] ' 因为它不是委托类型”。我想对@Doku-so 的解决方案进行一个小的修改，以修复这个错误，以防其他人看到这篇文章并决定使用@Doku-so 的解决方案。

The issue is the second argument in the Update method,

问题是 Update 方法中的第二个参数，

public int Update(T entity, Expression<Func<T, object>>[] properties). 

To call this method using the syntax provided...

要使用提供的语法调用此方法...

Update(Model, d=>d.Name, d=>d.SecondProperty, d=>d.AndSoOn); 

You must add the 'params' keyword in front of the second arugment as so.

您必须在第二个参数前面添加 'params' 关键字。

public int Update(T entity, params Expression<Func<T, object>>[] properties)

or if you don't want to change the method signature then to call the Update method you need to add the 'new' keyword, specify the size of the array, then finally use the collection object initializer syntax for each property to update as seen below.

或者，如果您不想更改方法签名，则要调用 Update 方法，您需要添加“ new”关键字，指定数组的大小，然后最后使用每个属性的集合对象初始值设定项语法进行更新，如下所示以下。

Update(Model, new Expression<Func<T, object>>[3] { d=>d.Name }, { d=>d.SecondProperty }, { d=>d.AndSoOn });

In @Doku-so's example he is specifying an array of Expressions so you must pass the properties to update in an array, because of the array you must also specify the size of the array. To avoid this you could also change the expression argument to use IEnumerable instead of an array.

在@Doku-so 的示例中，他指定了一个表达式数组，因此您必须在数组中传递要更新的属性，因为您还必须指定数组的大小。为避免这种情况，您还可以更改表达式参数以使用 IEnumerable 而不是数组。

Here is my implementation of @Doku-so's solution.

这是我对@Doku-so 解决方案的实现。

public int Update<TEntity>(LcmsEntities dataContext, DbEntityEntry<TEntity> entityEntry, params Expression<Func<TEntity, object>>[] properties)
     where TEntity: class
    {
        entityEntry.State = System.Data.Entity.EntityState.Unchanged;

        properties.ToList()
            .ForEach((property) =>
            {
                var propertyName = string.Empty;
                var bodyExpression = property.Body;
                if (bodyExpression.NodeType == ExpressionType.Convert
                    && bodyExpression is UnaryExpression)
                {
                    Expression operand = ((UnaryExpression)property.Body).Operand;
                    propertyName = ((MemberExpression)operand).Member.Name;
                }
                else
                {
                    propertyName = System.Web.Mvc.ExpressionHelper.GetExpressionText(property);
                }

                entityEntry.Property(propertyName).IsModified = true;
            });

        dataContext.Configuration.ValidateOnSaveEnabled = false;

        return dataContext.SaveChanges();
    }

Usage:

用法：

this.Update<Contact>(context, context.Entry(modifiedContact), c => c.Active, c => c.ContactTypeId);

@Doku-so provided a cool approach using generic's, I used the concept to solve my issue but you just can't use @Doku-so's solution as is and in both this post and the linked post no one answered the usage error questions.

@Doku-so 提供了一种使用泛型的很酷的方法，我使用这个概念来解决我的问题，但您不能按原样使用 @Doku-so 的解决方案，并且在这篇文章和链接的文章中都没有人回答使用错误问题。

回答by Bahtiyar ?zdere

Entity framework tracks your changes on objects that you queried from database via DbContext. For example if you DbContext instance name is dbContext

实体框架跟踪您对通过 DbContext 从数据库查询的对象的更改。例如，如果您的 DbContext 实例名称是 dbContext

public void ChangePassword(int userId, string password){
     var user = dbContext.Users.FirstOrDefault(u=>u.UserId == userId);
     user.password = password;
     dbContext.SaveChanges();
}

回答by Alexei

In EntityFramework Core 2.x there is no need for Attach:

在 EntityFramework Core 2.x 中，不需要Attach：

 // get a tracked entity
 var entity = context.User.Find(userId);
 entity.someProp = someValue;
 // other property changes might come here
 context.SaveChanges();

Tried this in SQL Server and profiling it:

在 SQL Server 中尝试了这个并对其进行了分析：

exec sp_executesql N'SET NOCOUNT ON;
UPDATE [User] SET [someProp] = @p0
WHERE [UserId] = @p1;
SELECT @@ROWCOUNT;

',N'@p1 int,@p0 bit',@p1=1223424,@p0=1

Find ensures that already loaded entities do not trigger a SELECT and also automatically attaches the entity if needed (from the docs):

Find 确保已加载的实体不会触发 SELECT 并在需要时自动附加实体（来自文档）：

    ///     Finds an entity with the given primary key values. If an entity with the given primary key values
    ///     is being tracked by the context, then it is returned immediately without making a request to the
    ///     database. Otherwise, a query is made to the database for an entity with the given primary key values
    ///     and this entity, if found, is attached to the context and returned. If no entity is found, then
    ///     null is returned.