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

c# entity-framework-core

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?

1
2
3/28/2020 9:09:25 PM

Popular Answer

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

1
3/29/2020 7:29:44 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow