SqlBulkCopy (and Update) with Entity Framework

Question

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 SqlBulkCopy method. The problem is that SqlBulkCopy doesn't allow me to "UPSERT." Again, started looking for solutions and came across the DataTable.Merge(table) function.

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 DataTable.Merge() and then somehow save the changes. Saving the changes is the part where I'm running into issues. I have the following 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 >= 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 >= 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();
        }
    }

How do I alter this to save results of the Merge operation?

Is there a better way that I can code this to accomplish my goal of quickly importing/updating a rather large amount of data?

Further explaination: 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.

My current implementation is entirely Entity Framework based. It:

1. pulls the current day's set of data from the production server
2. loops through that set of data
3. compares it to the local database
4. updates/adds as necessary

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.

Answer 1

Probably the fastest way here (if not using SSIS) will be to use a stored procedure with MERGE statement.
Add linked server in your stored procedure. Something like this should work:

exec sp_addlinkedserver @server = 'ProductionServer'

MERGE [LocalServer].dbName.dbo.TableName AS Target
USING (SELECT * FROM [ProductionServer].dbName.dbo.TableName) AS source
ON TARGET.Id = source.Id
WHEN MATCHED
THEN UPDATE 
SET Field1 = source.Field1, Field2 = source.Field2, ---etc....
WHEN NOT MATCHED BY TARGET THEN
INSERT (Field1, Field2, Field3) 
VALUES (Field1, Field2, Field3) ;

Also, if you need to then remove everything from one of the tables, you can do this by using TRUNCATE which is much faster than Delete from. Like this:

TRUNCATE TABLE TableName;