title: SqlBulkCopy (and Update) with Entity Framework
body: <p>I recently came to a point where Entity Framework is no longer meeting my needs for copying data over from one database to another (it's far too slow as it is and I want to pull even more data than I have been). So I started looking into alternatives and found the <code>SqlBulkCopy</code> method. The problem is that <code>SqlBulkCopy</code> doesn't allow me to "UPSERT." Again, started looking for solutions and came across the <code>DataTable.Merge(table)</code> function.</p>
  
  <p>According to my research it seems that the "best practice" is to use SqlBulkCopy to import my data into a "staging table" and then use <code>DataTable.Merge()</code> and then somehow save the changes. Saving the changes is the part where I'm running into issues. I have the following code:</p>
  
  <pre><code>static void Main(string[] args)
      {
          using(var mdb = new meldbContext())
          using(var odb = new ocmgccazTestEnvDbContext())
          {
              /*Is there a better way to clear the staging table that
                doesn't require me to write actual SQL?*/
              odb.Database.ExecuteSqlCommand("DELETE FROM almCallDetail_staging");
  
              var lastUpdateTime = (from p in odb.almCallDetail
                                    select p.time_of_contact).Max();
  
              var query = from p in mdb.cl_contact_event
                          where p.time_of_contact &gt;= lastUpdateTime
                          select new almCallDetail
                          {
                              id = p.id,
                              contact_list_name = p.contact_list_name,
                              account_number = p.account_number,
                              time_of_contact = p.time_of_contact
                          };
  
              var conn = new SqlConnection(odb.Database.Connection.ConnectionString);
              var bulkCopy = new SqlBulkCopy(conn)
              {
                  BatchSize = 5000,
                  DestinationTableName = "almCallDetail_staging"
              };
  
              conn.Open();
                  bulkCopy.WriteToServer(query.ToDataTable());    
  
                  var originalTable = (from p in odb.almCallDetail
                                       where p.time_of_contact &gt;= lastUpdateTime
                                       select p).ToDataTable();
  
                  var stagingTable = (from p in odb.almCallDetail_staging
                                      select p).ToDataTable();
  
                  /*Merge happens but the data is not actually saved to the almCallDetail
                    Table (originalTable)...*/
                  originalTable.Merge(stagingTable);
              conn.Close();
          }
      }
  </code></pre>
  
  <p>How do I alter this to save results of the Merge operation?</p>
  
  <p>Is there a better way that I can code this to accomplish my goal of quickly importing/updating a rather large amount of data?</p>
  
  <p><strong>Further explaination:</strong> I'm basically just replicating the data from a production server's tables in order to (later) create various datasets that I can turn into reports for our group. I have a Scheduled Task that will run this code every 30 minutes or so to keep the data relatively up to date and I want the entire process to be as efficient as possible. I.E. Pulling the minimum amount of data required from the production server and copying it to my local database.</p>
  
  <p>My current implementation is entirely Entity Framework based. It:</p>
  
  <ol>
  <li>pulls the current day's set of data from the production server</li>
  <li>loops through that set of data</li>
  <li>compares it to the local database </li>
  <li>updates/adds as necessary</li>
  </ol>
  
  <p>It does exactly what I need but it's super slow (for several reasons, all of which I understand). Hence, my desire to update it.</p>