C# – Entity Framework 5 Updating a Record

asp.net-mvc-3centity-framework-5

I have been exploring different methods of editing/updating a record within Entity Framework 5 in an ASP.NET MVC3 environment, but so far none of them tick all of the boxes I need. I'll explain why.

I have found three methods to which I'll mention the pros and cons:

Method 1 – Load original record, update each property

var original = db.Users.Find(updatedUser.UserId);

if (original != null)
{
    original.BusinessEntityId = updatedUser.BusinessEntityId;
    original.Email = updatedUser.Email;
    original.EmployeeId = updatedUser.EmployeeId;
    original.Forename = updatedUser.Forename;
    original.Surname = updatedUser.Surname;
    original.Telephone = updatedUser.Telephone;
    original.Title = updatedUser.Title;
    original.Fax = updatedUser.Fax;
    original.ASPNetUserId = updatedUser.ASPNetUserId;
    db.SaveChanges();
}

Pros

Can specify which properties change
Views don't need to contain every property

Cons

2 x queries on database to load original then update it

Method 2 – Load original record, set changed values

var original = db.Users.Find(updatedUser.UserId);

if (original != null)
{
    db.Entry(original).CurrentValues.SetValues(updatedUser);
    db.SaveChanges();
}

Pros

Only modified properties are sent to database

Cons

Views need to contain every property
2 x queries on database to load original then update it

Method 3 – Attach updated record and set state to EntityState.Modified

db.Users.Attach(updatedUser);
db.Entry(updatedUser).State = EntityState.Modified;
db.SaveChanges();

Pros

1 x query on database to update

Cons

Can't specify which properties change
Views must contain every property

Question

My question to you guys; is there a clean way that I can achieve this set of goals?

Can specify which properties change
Views don't need to contain every property (such as password!)
1 x query on database to update

I understand this is quite a minor thing to point out but I may be missing a simple solution to this. If not method one will prevail 😉

Best Answer

You are looking for:

db.Users.Attach(updatedUser);
var entry = db.Entry(updatedUser);
entry.Property(e => e.Email).IsModified = true;
// other changed properties
db.SaveChanges();

Related Solutions

C# – SqlException from Entity Framework – New transaction is not allowed because there are other threads running in the session

After much pulling out of hair I discovered that the foreach loops were the culprits. What needs to happen is to call EF but return it into an IList<T> of that target type then loop on the IList<T>.

Example:

IList<Client> clientList = from a in _dbFeed.Client.Include("Auto") select a;
foreach (RivWorks.Model.NegotiationAutos.Client client in clientList)
{
   var companyFeedDetailList = from a in _dbRiv.AutoNegotiationDetails where a.ClientID == client.ClientID select a;
    // ...
}

C# – Fastest Way of Inserting in Entity Framework

To your remark in the comments to your question:

"...SavingChanges (for each record)..."

That's the worst thing you can do! Calling SaveChanges() for each record slows bulk inserts extremely down. I would do a few simple tests which will very likely improve the performance:

Call SaveChanges() once after ALL records.
Call SaveChanges() after for example 100 records.
Call SaveChanges() after for example 100 records and dispose the context and create a new one.
Disable change detection

For bulk inserts I am working and experimenting with a pattern like this:

using (TransactionScope scope = new TransactionScope())
{
    MyDbContext context = null;
    try
    {
        context = new MyDbContext();
        context.Configuration.AutoDetectChangesEnabled = false;

        int count = 0;            
        foreach (var entityToInsert in someCollectionOfEntitiesToInsert)
        {
            ++count;
            context = AddToContext(context, entityToInsert, count, 100, true);
        }

        context.SaveChanges();
    }
    finally
    {
        if (context != null)
            context.Dispose();
    }

    scope.Complete();
}

private MyDbContext AddToContext(MyDbContext context,
    Entity entity, int count, int commitCount, bool recreateContext)
{
    context.Set<Entity>().Add(entity);

    if (count % commitCount == 0)
    {
        context.SaveChanges();
        if (recreateContext)
        {
            context.Dispose();
            context = new MyDbContext();
            context.Configuration.AutoDetectChangesEnabled = false;
        }
    }

    return context;
}

I have a test program which inserts 560.000 entities (9 scalar properties, no navigation properties) into the DB. With this code it works in less than 3 minutes.

For the performance it is important to call SaveChanges() after "many" records ("many" around 100 or 1000). It also improves the performance to dispose the context after SaveChanges and create a new one. This clears the context from all entites, SaveChanges doesn't do that, the entities are still attached to the context in state Unchanged. It is the growing size of attached entities in the context what slows down the insertion step by step. So, it is helpful to clear it after some time.

Here are a few measurements for my 560000 entities:

commitCount = 1, recreateContext = false: many hours (That's your current procedure)
commitCount = 100, recreateContext = false: more than 20 minutes
commitCount = 1000, recreateContext = false: 242 sec
commitCount = 10000, recreateContext = false: 202 sec
commitCount = 100000, recreateContext = false: 199 sec
commitCount = 1000000, recreateContext = false: out of memory exception
commitCount = 1, recreateContext = true: more than 10 minutes
commitCount = 10, recreateContext = true: 241 sec
commitCount = 100, recreateContext = true: 164 sec
commitCount = 1000, recreateContext = true: 191 sec

The behaviour in the first test above is that the performance is very non-linear and decreases extremely over time. ("Many hours" is an estimation, I never finished this test, I stopped at 50.000 entities after 20 minutes.) This non-linear behaviour is not so significant in all other tests.

Best Answer

Related Solutions

C# – SqlException from Entity Framework – New transaction is not allowed because there are other threads running in the session

C# – Fastest Way of Inserting in Entity Framework

Related Topic