user5997884 user5997884 - 1 year ago 52
C# Question

EF: Best/Quickest way to update field by Id

Currently, a database-record from the get loaded into a simplified model, which also stores the ID-key. After some computations, the one or more fields of the original record needs to be updated.

The Model is quite large (alot of strings) and the entrycount is likely to be > 100.000 entries per table. So loading them into memory results into a OutOfMemoryException.

class Model // Database-Table
{
public Int Id { get; set; }
public int Field { get; set; }
}

class SimpleModel
{
int Id;
int Field;
}

void Update( SimpleModel[] simpleModels )
{
using( var ctx = new DbContext() )
{
foreach( var simpleModel in simpleModels )
{
var entry = ctx.ModelTable
.Where( x => x.Id == simpleModel.Id )
.FirstOrDefault();

if( entry == null )
continue;

ctx.ModelTable.Attach( entry );

entry.Field = simpleModel.Field;
}

if( ctx.ChangeTracker.HasChanges() )
ctx.SaveChanges();
}
}


However, this is painfully slow. Is there a way to speed this up, using the EntityFramework (without direct string-sql-queries)?

Answer Source

Each time you are using FirstOrDefault() you run new database query. You can load all entities in one query instead:

var ids = simpleModels.Select(sm => sm.Id);
var entries = ctx.ModelTable.Where(m => ids.Contains(m.Id)).ToList();
// or
var entriesById = ctx.ModelTable.Where(m => ids.Contains(m.Id)).ToDictionary(x => x.Id);

Then you can work with loaded entries in memory without additional database queries. E.g.

 foreach( var simpleModel in simpleModels )
 {
      Model entry;
      if (!entriesById.TryGetValue(simpleModel.Id, out entry))
         continue;

      entry.Field = simpleModel.Field;
 }
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download