Perform a Minimal Update in the Entity Framework

Question

What I want to do is append to a record's note column.

What I do not want to do is fetch the whole record, which is very significant.

Here's code that does not work:

string AppendNote(int key, string note)
{
    using (MyEntities _Context = new MyEntities())
    {
        var _Record = _Context.Records
            .Where(x => x.Id == key)
            .Select(x => new Record { Id = x.Id, Notes = x.Notes })
            .First();
        _Record.Notes += note;
        _Context.SaveChanges();
    }
}

What is the correct way?

I realize it is very similar to this VERY OLD question: Update statement with Entity Framework and this similar but ultimately dissimilar question: How to update a record without selecting that record again in ADO.NET Entity Framework? but, still, it seems there has to be a way.

LambdaCruiser · Accepted Answer · 2011-09-08T08:12:34.467

As for simple minimal updates in EF, you can do them by attaching an object to the context like this:

string AppendNote(int key, string note)
{
    using (MyEntities _Context = new MyEntities())
    {
        var _Record = new Record { Id = key };
        _Context.Entry(_Record).State = System.Data.EntityState.Unchanged;
        _Record.Notes = note; //a simple update
        _Context.SaveChanges();
    }
}

However, you want to append to a current value. The code below is a bit messy because of the anonymously typed variable _R but it should fetch just the Notes column and append to it:

string AppendNote(int key, string note)
{
    using (MyEntities _Context = new MyEntities())
    {
        var _R = _Context.Records
            .Select(x=>new {Id = x.Id, Notes=x.Notes})
            .Where(x=>x.Id == key)
            .First();
        var _Record = new Record { Id = _R.Id, Notes=_R.Notes };
        _Context.Entry(_Record).State = System.Data.EntityState.Unchanged;
        _Record.Notes+= note;
        _Context.SaveChanges();
    }
}

I just *knew* it was possible. – Jerry Nixon Sep 13 '11 at 19:24 — Jerry Nixon, Sep 13 '11 at 19:24

score 1 · Answer 2 · answered Sep 07 '11 at 20:53

This is not possible with Entity Framework afaik. The only optimization you can do is to lazy load all properties that cause the "significant load" when loading the record entity and update the entity without ever loading them.

If you want to avoid loading the entity in the first place, you can use ExecuteStoreQuery() to execute SQL directly for these performance critical updates.

In the end EF is an ORM that makes CRUD much easier, but you do pay a performance price using this abstraction.

I was hoping you were not right; it's also annoying that this WAS possible with Linq to SQL. But it is what it is. Thanks for your feedback on this. — Jerry Nixon, Sep 07 '11 at 20:55

score 1 · Answer 3 · answered Sep 08 '11 at 05:04

The easiest way to do this with Entity Framework (or any other ORM) would be to use a stored procedure for this - something like:

CREATE PROCEDURE dbo.UpdateNote(@Key INT, @Note VARCHAR(500))

and then in that stored procedure have a simple UPDATE statement to do the work.

From Entity Framework, you can import that stored procedure into your object context and then you can call it as a method on the object context:

string AppendNote(int key, string note)
{
    using (MyEntities _Context = new MyEntities())
    {
        _Context.UpdateNote(key, note);
    }
}

and only this single SQL UPDATE statement will be executed - the row won't be transferred to your client unnecessarily.

Perform a Minimal Update in the Entity Framework

3 Answers3