LinqToSql Plus Bulk Insert
Description
INSERT
all entities in the database.
All entities are considered as new rows and are INSERTED
in the database.
// Easy to use context.BulkInsert(list); // Easy to customize context.BulkInsert(list, options => options.BatchSize = 100);
Purpose
Inserting
thousands of entities for an initial load or a file importation is a typical scenario.
BulkInsert
on the other hand, requires as few database round-trips as possible. For example, under the hood in SQL Server, a simple SqlBulkCopy
could be performed.
Performance Comparisons
Operations | 1,000 Entities | 2,000 Entities | 5,000 Entities |
---|---|---|---|
SubmitChanges | 1,000 ms | 2,000 ms | 5,000 ms |
BulkInsert | 6 ms | 10 ms | 15 ms |
How can I specify more than one option?
You can specify more than one option using an anonymous block.
context.BulkInsert(list, options => { options.BatchSize = 100; options.ColumnInputExpression = c => new {c.Name, c.Description}; });
How can I specify the Batch Size?
You can specify a custom batch size using the BatchSize
option.
Read more: BatchSize
context.BulkInsert(list, options => options.BatchSize = 100);
How can I specify custom columns to Insert?
You can specify custom columns using the ColumnInputExpression
option.
Read more: ColumnInputExpression
context.BulkInsert(list, options => options.ColumnInputExpression = c => new {c.Name, c.Description});
Why doesn't BulkInsert use the ChangeTracker?
To provide the best performance possible!
Since using the ChangeTracker
can greatly reduce performance, we chose to let the SubmitChanges
method handle the scenarios with ChangeTracker
, and BulkInsert
the scenarios without it.
Why BulkInsert is faster than SubmitChanges?
The major difference between both methods is SubmitChanges
uses the ChangeTracker
but not the BulkInsert
method.
By skipping the ChangeTracker
, some methods like Add
, AddRange
, DetectChanges
are no longer required which greatly helps to improve the performance.