How to optimize save multiple records in Entity Framework WITHOUT extension?

Question

I am looking for a way to save thousands of records (10K+) without using an extension. Any ideas on how exactly to bulk update this amount of data with a reasonable execution time?

My current issue is that when calling dbContext.SaveChanges() it is saving all records in memory and not only the modified ones resulting in updating 20K+ records when only about 10K records have been modified.

You should be able to find many duplicates of this question. You're not the first one asking this. — Gert Arnold, Sep 19 '17 at 14:59

score 1 · Answer 1 · answered Sep 19 '17 at 15:07

You say you don't want to use SqlBulkCopy, so my answer is: AFAIK you won't be able to insert|update that amount of records using EF in a performant time.

Anyway here's an example of SqlBulkCopy

DataTable dt = new DataTable("Table");
dt = GetDataTableFromSomewhereElse();
using (SqlConnection conn = new SqlConnection("YourConnectionString"))
                {
                    using (SqlCommand command = new SqlCommand("", conn))
                    {
                        try
                        {
                            conn.Open();
                            command.CommandText = @"Create Table #TmpTable(
                                [Col1] [int] NOT NULL,
                                [Col2] [nvarchar](max) NOT NULL,
                                [Col3] [decimal](18, 2) NOT NULL)";
                            command.ExecuteNonQuery();
                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                            {
                                bulkCopy.BulkCopyTimeout = 660;
                                bulkCopy.DestinationTableName = "#TmpTable";
                                bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col1", "Col1"));
                                bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col2", "Col2"));
                                bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("Col3", "Col3"));

                                bulkCopy.WriteToServer(dt);
                                bulkCopy.Close();
                            }

                            command.CommandTimeout = 300;
                            command.CommandText = $@"MERGE INTO Table p
                                USING #TmpTable t ON p.Col1 = t.Col1
                                WHEN MATCHED THEN UPDATE SET 
                                p.Col2 = t.Col2,
                                p.Col3 = t.Col3,
                                WHEN NOT MATCHED THEN
                                INSERT VALUES(
                                t.Col1,
                                t.Col2,
                                t.Col3
                                );
                                Drop Table #TmpTable;";
                            command.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                          ...
                        }
                    }
                }

Just to give you an insight, when I was trying to update|insert 14000 lines using EF it took... "forever" to complete, meanwhile using SQLBulkCopy it took about 2-3 seconds

How to optimize save multiple records in Entity Framework WITHOUT extension?

1 Answers1