CODEWITHSUNDEEP
c#entity-framework-core
mannaggia
mannaggia

Reputation: 535

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

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?

Upvotes: 15

Views: 10320

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109079

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.

Upvotes: 21

ErikEJ
ErikEJ

Reputation: 41759

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

Upvotes: 3

Related Questions