1

I have a button with a list of users in the table ButtonUsers. A parent Button is updated with the list of users, and all other Buttons' UserList should be updated. This isn't the case every time, therefore each button has it's own list. The problem is that I have maybe 5000 buttons, that should each be updated.

I tried pulling all buttons at once, then pull the users necessary, and set each buttons state as modified. When I've iterated through all the buttons, call SaveChanges(). 

public bool UpdatePreviousButtons(Button button, List<string> userList)
{
   var buttons = fieldRepo.GetFieldsCreatedFrom<Button>(button.ID);
   var users = fieldRepo.GetUsersFromIDs(userList);

   foreach(var btn in buttons)
   {
      btn.UserList = users;
      fieldRepo.UpdateFieldGeneric<Button>(btn);
   }

   return fieldRepo.Commit();
}

The problem seems to be that it doesn't bulk insert the records, instead it inserts one record per call. This inevitably leads to a time out, and takes far to long.

I have been thinking about doing it manually with Context.Database.ExecuteSqlCommand(). Is there a better way, using Entity Framework?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chimera
  • 149
  • 1
  • 13
  • It is common for ORMs (like EF) to work on a per-object basis; tools like `SqlBulkCopy` exist (and can be used with raw objects if needed), but they have fewer guarantees ("ACID" etc); you usually have to implement such things fairly manually. Alternatively, maybe consider "table valued parameters" – Marc Gravell Aug 28 '19 at 09:00

1 Answers1

2

You could try the SqlBulkCopy class:

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?view=netframework-4.8

As a simple example of how it is used:

var data = new DataTable();
data.Columns.Add("Name");
data.Columns.Add("Id");

for (var i = 1; i< 10_000; i++)
{
    data.Rows.Add($"Name={i+1}", i+1); 
}

using (var sqlBulk = new SqlBulkCopy(_connectionstring))
{
    sqlBulk.DestinationTableName = "People";
    sqlBulk.WriteToServer(data);
}
MDBarbier
  • 379
  • 3
  • 12
  • That looks like something that might work. Thanks for the example – Chimera Aug 28 '19 at 09:03
  • Wow that worked really quick Thank you. As a bonus, is there something like this, to delete the current records, so that old data isn't left behind? – Chimera Aug 28 '19 at 09:52
  • SqlBulkCopy just inserts as far as I know, this thread discusses ways to deal with requirements to update etc: https://stackoverflow.com/questions/4889123/any-way-to-sqlbulkcopy-insert-or-update-if-exists – MDBarbier Aug 28 '19 at 10:01