Use Z Entity Framework Extensions or other tools for Bulk Commands

clmckinley

I need to insert ~100,000 records between data contexts from existing projects. This is something that only needs to be run for testing and migration, which is why i thought linqpad might be a good option. I wrote the logic in Linqpad and like the way it works, except that it takes a very long time to do the inserts. This turns out to be a problem because i need to lock production for the time it takes to migrate and validate the data.

If have a license for https://entityframework-extensions.net/ which is what i would normally use for bulk sql commands, but cannot figure out how to make it work in Linqpad.

I am hoping someone can point to a solution, even if the answer is that i should convert this project out of linqpad.

TIA

nescafe

Are you sure you need bulk operations? SQL will accept 100000 direct inserts in separate statements in ~30 seconds or ~6 seconds when parallellized. Note that this approach bypasses object tracking, so it will invalidate LINQ-to-SQL state.

ExecuteCommand("CREATE TABLE MyTable ( Id INT PRIMARY KEY IDENTITY, Number INT )");

// Sequential (~30 seconds)

for (int i = 0; i < 100_000; i++)
{
  ExecuteCommand("INSERT MyTable VALUES ( @p0 )", i);
}

// Parallel (~6 seconds)

var ctx = new ThreadLocal<TypedDataContext>(() => new TypedDataContext(Connection.ConnectionString));

ParallelEnumerable.Range(0, 100_000).ForAll(i => ctx.Value.ExecuteCommand("INSERT MyTable VALUES ( @p0 )", i));