Raymond Raymond

EntityFramework Core - Update Only One Field

event 2021-02-14 visibility 17,190 comment 0 insights toc
more_vert
insights Stats

Context

When updating records with EntityFramework Core, the default behavior will update all the values for that record in the database even the values are not changing for some fields. This article shows how to update only one field (or multiple specified column names). 

Prerequisites

This article expands on one the previous article: EntityFramework Core - Connect to MySQL.

In that article, it shows how to update record in a database:

using (var dbContext = new MyDbContext())
            {

                var record6 = dbContext.TestModels.Where(record => record.ID == 4).FirstOrDefault();
                record6.Value = "EF Core !";
                dbContext.Update(record6);
                dbContext.SaveChanges();
            }

As there are only two columns with one as identify column, we cannot tell whether the generated updated statement is updating all fields or only the one with value changed. Thus, let's change the table to add one more column:

alter table test_table add value2 varchar(100) null;

The table now looks like this:

mysql> select * from test_table;
+------+-----------+--------+
| id   | value     | value2 |
+------+-----------+--------+
|    0 | Record 0  | NULL   |
|    1 | Record 1  | NULL   |
|    2 | Record 2  | NULL   |
|    3 | Record 3  | NULL   |
|    4 | EF Core ! | NULL   |
+------+-----------+--------+

For the entity class definition, let's also add this newly added column:

[Column("value2")]
public string Value2 { get; set; }

Now run the update function and the console log will show the following:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[@p2='?' (DbType = Int32), @p0='?' (Size = 4000), @p1='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      UPDATE `test_table` SET `value` = @p0, `value2` = @p1
      WHERE `id` = @p2;
      SELECT ROW_COUNT();

Both values are passed to the UPDATE statement.

Update only one field

To only update one field, we can simply change the update method to the following:

static void EntityFrameworkMySQLExampleUpdateRecord()
        {
            using (var dbContext = new MyDbContext())
            {
                // var record6 = dbContext.TestModels.Where(record => record.ID == 4).FirstOrDefault();
                var record6 = new TestModel {ID=4, Value="EF Core !"};
                dbContext.Attach(record6);
                dbContext.Entry(record6).Property(r=>r.Value).IsModified=true;
                dbContext.SaveChanges();
            }
        }

The above function first constructs the object with ID and updated values specified and then attach the object; it then mark Value property as modified explicitly.  

Now the generated UPDATE statement looks like the following:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (8ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      UPDATE `test_table` SET `value` = @p0
      WHERE `id` = @p1;
      SELECT ROW_COUNT();

As printed out in EFCore log, only field 'value' is updated.  

Through this approach, the performance can be slightly improved as the SQL statement is smaller and query execution on the database server can also be faster. 

More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts