EF6 Add a new row from DB1 to DB2 with the same structure-CodePudding

I have two different databases that have a table each with the same exact structure. I would like to upload the registers retrieved from DB1 into DB2. I tried some methods using the same entity or converting it to a List object, as follows. The list of the registers from DB1 is generated properly, but how to insert it into DB2?

object pendingRowsList = new object();     
   
//Get registers from DB1
using (var context = new DB1())
{
  pendingRowsList = context.table_local.OrderByDescending(x => 
  x.id).Take(numberOfRows).ToList();
}
 
//Upload registers into DB2
using (var context = new DB2())
{   
  for (int i = numberOfRows; i > 0; i--)
  {
     var newDbRow = new table_remote(pendingRowsList[i]); //that is what I am trying to achieve

     context.table_remote.Add(newDbRow);

     context.SaveChanges();
      }
}

CodePudding user response：

A couple of things: for (int i = numberOfRows; i > 0; i--) should be for (int i = numberOfRows-1; i >= 0; i--) otherwise i will be greater than the length of your pendingRowsList right away and you will never get to 0. Also, depending on the number of records, this method will either take a VERY long time or will not be possible to do all at once. There are limits to the number of rows you can insert all at once with Entity Framework (or any ORM for that matter) If you are using Sql Server, the best way to do this is with direct INSERT and SELECT - something like this:

INSERT INTO dbo.MyTable (Field1, Field2)
SELECT Field1, Field2 FROM dbo.MyOtherTable
WHERE 1 = 1 --or other qualifying set of records

Alternatively you can use the SqlBulkCopy approach which is also blazingly fast. There is a helper class for this linked in this answer with an example:

https://stackoverflow.com/a/9162325/396005

Or, you can "roll your own" using something like this which leverages the ToDataTable() extension method from the MoreLinq package

var dataTable = pendingRowsList.ToDataTable();
// Depending on your entity model you might have to use 
// something like this to get the actual fields you need to insert
// var dataTable = pendingRowsList.Select(r => new { r.Name, RelatedId = r.Related.Id, ... }).ToDataTable();
using (var bulkCopy = new SqlBulkCopy(connection))
{
    for (var c = 0; c < dataTable.Columns.Count; c  )
    {
        var col = dataTable.Columns[c];
        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
    }
    bulkCopy.DestinationTableName = "MyTableName";
    bulkCopy.BulkCopyTimeout = 0;
    connection.Open();
    bulkCopy.WriteToServer(dataTable);
}