EF Core Update updates all columns even though only one has changed

Question

I'm kind of new to using EF Core. It was my understanding that EF Core has a "Change Tracker" that looks at the data to see what columns have been modified and builds a SQL UPDATE statement that only updates the modified columns, as described in an answer here:

Does EF core compares value to include columns in update statement?

However, that is not what I am seeing. For example, I have this entity:

public class Book
{
    public int BookId { get; set; }
    [StringLength(255)]
    public string Title { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; }
    [StringLength(500)]
    public string Description { get; set; }
}

Then I update an existing row with this code:

class Program
{
    static void Main(string[] args)
    {
        var context = new TestContext();
        var book = context.Books.Find(1);
        book.Title += " x";
        context.Books.Update(book);
        context.SaveChanges();
    }
}

I started SQL Profiler and ran this code. I expected to see an UPDATE statement that only updated the column "Title", yet it always udpates every column:

exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Books] SET [AuthorId] = @p0, [Description] = @p1, [Title] = @p2
WHERE [BookId] = @p3;
SELECT @@ROWCOUNT;
',N'@p3 int,@p0 int,@p1 nvarchar(500),@p2 nvarchar(255)',@p3=1,@p0=1,@p1=N'',@p2=N'Some Title x'

For example, the column Description was not changed, yet it is in the UPDATE statement, as is AuthorId.

Why is that? Should it not just have Title in the SET clause?

You don't need the line with Update. Why do you think you need that? — ErikEJ, Mar 28 '20 at 19:37
It's a common mistake to think you need to call update but it's not required in this case and is actually causing your issue. Here's a simple description of the differences. https://www.learnentityframeworkcore.com/dbcontext/modifying-data — Tintow, Mar 28 '20 at 19:51
Thanks, that does it! I knew it had to be something I was overlooking. @ErikEJ, it was in some sample code I found. — JoeD, Mar 28 '20 at 23:41

Gert Arnold · Answer 1 · 2020-04-28T07:09:21.110

It was my understanding that EF Core has a "Change Tracker"

That's correct and that's why, once entities are attached to the context, you're exempted from marking them as updated. After the line...

book.Title += " x";

...EF detects this change and marks marks Title as modified. No need to call the Update method.

This is the so-called "connected" scenario.

The Update method is for the disconnected scenario. That's when an entity object is not attached to a context but its values should be saved to the database, for example, in a web API backend. In its basic form:

public void SaveBook(Book book)
{
    // Here, 'book' is the book with the changed Title.
    using(var context = new TestContext())
    {
        context.Books.Update(book);
        context.SaveChanges();
    }
}

Now, EF can't detect the change because book is already modified when it enters the method. You have to tell EF that the book must be updated. But EF doesn't know what was modified; the best it can do is mark all properties (except the primary key) as modified.

However, it's still possible to make EF update only the properties that are actually updated by turning the disconnected scenario to connected:

public void SaveBook(Book book)
{
    // Here, 'book' is the book with the changed Title.
    using(var context = new TestContext())
    {
        var dbBook = context.Books.Find(book.ID);

        // Copy book's property values to dbBook.
        context.Entry(dbBook).CurrentValues.SetValues(book);

        context.SaveChanges();
    }
}

There may be good reasons to prefer the latter method above the former.

score 3 · Answer 2 · answered Mar 29 '20 at 07:15

3

In your connected scenario, you do not need to call Update method.

answered Mar 29 '20 at 07:15

ErikEJ

40,951
5
75
115

EF Core Update updates all columns even though only one has changed

2 Answers2