Updating database table from modified list with Entity Framework

Question

Can someone please tell me which is the better way to update a table with a list of values? And which one to use when and what's the reason behind that?

Method 1:

public void SavDetails(List<MyTable> list)
{
        _Entity.MyTable.AddOrUpdate(list.ToArray());

        try
        {
            _Entity.SaveChanges();
        }
        catch (DbUpdateException ex)
        {
            Console.WriteLine(ex);
        }
}

Method 2:

public void SaveDetails(List<MyTable> list)
{
    foreach (var file in list)
    {
        _Entity.MyTable.Add(file);
        _Entity.Entry(file).State = EntityState.Modified;

        try
        {
            _Entity.SaveChanges();
        }
        catch (DbUpdateException ex)
        {
            Console.WriteLine(ex);
        }
    }
}

We need more background to be able to judge what is better (and there are more alternatives). But even then chances are that it will always be opinion-based. — Gert Arnold, Dec 19 '19 at 21:20
In the mean time, look at [these](https://stackoverflow.com/a/30824229/861716) considerations. — Gert Arnold, Dec 19 '19 at 21:22
@GertArnold - So in my case, is the entity updating all the records in the DB or only the modified values are ebing updated? — Nilanjan, Dec 19 '19 at 21:30
Are they both getting the job done? If you can't tell the difference and they both work just pick what method you like best. — Train, Dec 19 '19 at 21:33
@Train - Yes, they are both getting the job done. But I was thinking if this is updating every columns or only desired/modified columns? — Nilanjan, Dec 19 '19 at 21:35
And `AddOrUpdate` updates only what's necessary but queries each entity separately from the database. That's nearly always an inferior approach. — Gert Arnold, Dec 19 '19 at 21:37
@GertArnold - Can you please suggest or redirect me to a better approach to update the columns? — Nilanjan, Dec 19 '19 at 21:40

score 1 · Answer 1 · answered Dec 20 '19 at 01:42

Method 1

The AddOrUpdate method is in the System.Data.Entity.Migrations namespace, because it is intended for use with migrations. If it works for you then it may be a good option, but be aware that this is not how it was designed to be used.

Method 2

This seems to assume that all entities already exist in the database, and it will fail if they do not. If you know this, then it will be more efficient because it doesn't need to check.

Do not call SaveChanges() inside the loop, as this causes multiple calls to the database. Instead, call it once at the end and all the entities will be updated at once. Also, you should replace Add (which implies adding a new object) with Attach (which is for telling the context to track existing entities). This will make your intent easier to follow.

If you can't rely on all entities already existing in the database, then you'll need to manually check:

var existing = _Entity.MyTable.Select(o => o.Id).ToList();

foreach (var file in list)
{
  if (existing.Contains(file.Id))
  {
    _Entity.MyTable.Attach(file);
    _Entity.Entry(file).State = EntityState.Modified;
  }
  else
  {
    _Entity.MyTable.AddObject(file);
  }
}
_Entity.SaveChanges();

If you want to delete rows from the database table which are not in your list, that will require additional logic.

Conclusion

There is no way of knowing for sure which approach is best for your scenario. If both methods work, and they're both fast enough, then choose the one you prefer. If speed is an issue, then benchmark them and choose the faster option.

@James- Thanks for the explanation. Do I really need to do the EntityState.Modified? and What does AddObject(file) imply to here? — Nilanjan, Dec 20 '19 at 15:26
If you change the entity after it is attached, it will automatically be set to Modified. If you set its properties beforehand, you need to tell the Change Tracker that it's modified yourself. AddObject is more or less the same as Add, I think. — James, Dec 20 '19 at 22:35

Updating database table from modified list with Entity Framework

1 Answers1

Method 1

Method 2

Conclusion