You ever find yourself just wishing you could fire off three, four, maybe five thousand records all at once? Or even more than that? I bet you do. I found myself in this situation recently and usually I rely on Entity Framework to accomplish my data-related needs as I am firmly entrenched in the .NET ecosystem. You can imagine my dismay upon discovering that Entity Framework isn’t really fit for this purpose, a bit like trying to use a tractor to compete in Formula One.
There is a solution! Step outside of Entity Framework, briefly, and use SqlBulkCopy. You may be thinking “Why? I’m not copying anything, I want to insert new records.” That was my immediate reaction, too, but don’t worry – we’re actually going to use SqlBulkCopy to achieve mass creation of new records.
If you’ve tried this with Entity Framework, you know that you’re (probably) spending a lot of time waiting for the operation to finish. There are various workarounds, such as disabling automatic change detection. These workarounds come with pretty intense warnings, usually, about why you have to be careful and re-enable all of the things you disabled as soon as you can afterwards. They’re also not guaranteed to work, as they did not work in my case (This is anecdotal evidence. Your mileage may vary.) Instead of hammering our tractor into the shape of a Formula One car and hoping it goes faster, let’s just go get ourselves a race car.
We’re going to use SqlBulkCopy, your data, and the DataTable class. I’ll be using something simple as an example, but you should be able to extrapolate this to whatever your use case is because it isn’t especially difficult. My example is, say, a two column table that stores an identifier and integer values from 0 to 9999. Fascinating! Also, it conveniently happens to be quite a few records. We use DataTable as an interim holding zone for our information and subject it to the bulk copying experience.
How might this look? About like this:
var connectionString = context.Database.Connection.ConnectionString;
var dt = new DataTable();
dt.Columns.Add(“ID”);
dt.Columns.Add(“Value”);
for(var i = 0; i < 10000; ++i)
{
var value = i;
dt.Rows.Add(i, value);
}
using(var sqlBulk = new SqlBulkCopy(connectionString))
{
sqlBulk.DestinationTableName = “MyFanciestTable”;
sqlBulk.WriteToServer(dt);
}
That’s pretty minimal. I simply grabbed the relevant connection string from what you should absolutely assume is my EntityFramework DbContext. I went with a perfectly throwaway name for my DataTable instance, but you should probably name it something better. The important thing to note is that I have prior knowledge about the structure of the table I called “MyFanciestTable” above. This table is “shaped” exactly like the little example data table I created to hold my values in the interim. There are some other things I know about it, such as (for this example) the ID field is generated by the database engine so whatever value I stick in there is really a throwaway. It looks a bit redundant that I just chunked the same value in there twice, for the ID and Value fields (and just set up a useless variable named “value”), but I simply wanted to illustrate using it for two things.
This example is actually minimal enough to run pretty fast via something like AddRange(), and for this it’s not really worth worrying about SQLBulkCopy at all. I hope that doesn’t detract from it’s illustrative value, though. I hope it looks like something you could easily extend to other use cases. If you find that you need to work with an even larger set of records, you may well need to break them up somehow because even SqlBulkCopy has an upper limit on the amount of things it can handle all at once.
As usual, if you can see any improvements or corrections, please feel free to contact me! I would be happy to incorporate suggestions!
Joe Writes Software 