I'm looking for the fastest way of inserting in Entity Framework.
I'm asking this because of the scenario where you have an active TransactionScope and the insertion is huge (4000+). It can potentially last more than 10 minutes (default timeout of transactions), and this will lead to an incomplete transaction.
To your remark in the comments to your question:
"...SavingChanges (for each record)..."
That's the worst thing you can do! Calling SaveChanges() for each record slows bulk inserts extremely down. I would do a few simple tests which will very likely improve the performance:
SaveChanges() once after ALL records.SaveChanges() after for example 100 records.SaveChanges() after for example 100 records and dispose the context and create a new one.For bulk inserts I am working and experimenting with a pattern like this:
using (TransactionScope scope = new TransactionScope())
{
MyDbContext context = null;
try
{
context = new MyDbContext();
context.Configuration.AutoDetectChangesEnabled = false;
int count = 0;
foreach (var entityToInsert in someCollectionOfEntitiesToInsert)
{
++count;
context = AddToContext(context, entityToInsert, count, 100, true);
}
context.SaveChanges();
}
finally
{
if (context != null)
context.Dispose();
}
scope.Complete();
}
private MyDbContext AddToContext(MyDbContext context,
Entity entity, int count, int commitCount, bool recreateContext)
{
context.Set<Entity>().Add(entity);
if (count % commitCount == 0)
{
context.SaveChanges();
if (recreateContext)
{
context.Dispose();
context = new MyDbContext();
context.Configuration.AutoDetectChangesEnabled = false;
}
}
return context;
}
I have a test program which inserts 560.000 entities (9 scalar properties, no navigation properties) into the DB. With this code it works in less than 3 minutes.
For the performance it is important to call SaveChanges() after "many" records ("many" around 100 or 1000). It also improves the performance to dispose the context after SaveChanges and create a new one. This clears the context from all entites, SaveChanges doesn't do that, the entities are still attached to the context in state Unchanged. It is the growing size of attached entities in the context what slows down the insertion step by step. So, it is helpful to clear it after some time.
Here are a few measurements for my 560000 entities:
The behaviour in the first test above is that the performance is very non-linear and decreases extremely over time. ("Many hours" is an estimation, I never finished this test, I stopped at 50.000 entities after 20 minutes.) This non-linear behaviour is not so significant in all other tests.
AutoDetectChangesEnabled = false; on the DbContext. It also has a big additional performance effect: #5943894 –
Otherworld DbContext, NOT ObjectContext? –
Otherworld TransactionScope class in System.Transactions which is disposable: msdn.microsoft.com/en-us/library/… –
Otherworld IsolationLevel for my transaction like so: using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted })) - I'm not sure if it's necessary, but I'm really hoping to avoid any deadlocks or long wait times while the data inserts. –
Binns TransactionScopeOption.Suppress - see also: #2922420 –
Binns SaveChanges calls into a single database transaction. In most cases it's not needed. –
Otherworld Entity directly as a generic parameter of a generic version AddToContext<Entity>(...) where Entity : class. –
Otherworld AutoDetectChangesEnabled affects related entities as well, yes, and probably also with better performance. I don't have concrete numbers though. However, you must be careful with disabling change detection and test the code more thoroughly if relationships are involved: #23637420 –
Otherworld TransactionScope is at the outermost? –
Subdual AddOrUpdate as well. It's not great, but it's better than nothing. Long term I think I would want to change it to a MERGE. –
Kirchner Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool. exception. –
Passable This combination increase speed well enough.
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
as it was never mentioned here I want to recomment EFCore.BulkExtensions here
context.BulkInsert(entitiesList); context.BulkInsertAsync(entitiesList);
context.BulkUpdate(entitiesList); context.BulkUpdateAsync(entitiesList);
context.BulkDelete(entitiesList); context.BulkDeleteAsync(entitiesList);
context.BulkInsertOrUpdate(entitiesList); context.BulkInsertOrUpdateAsync(entitiesList); // Upsert
context.BulkInsertOrUpdateOrDelete(entitiesList); context.BulkInsertOrUpdateOrDeleteAsync(entitiesList); // Sync
context.BulkRead(entitiesList); context.BulkReadAsync(entitiesList);
System.Data.SqlClient.SqlBulkCopy. Not simple, but fast. –
Crossfertilize You should look at using the System.Data.SqlClient.SqlBulkCopy for this. Here's the documentation, and of course there are plenty of tutorials online.
Sorry, I know you were looking for a simple answer to get EF to do what you want, but bulk operations are not really what ORMs are meant for.
The fastest way would be using bulk insert extension, which I developed
note: this is a commercial product, not free of charge
It uses SqlBulkCopy and custom datareader to get max performance. As a result it is over 20 times faster than using regular insert or AddRange
usage is extremely simple
context.BulkInsert(hugeAmountOfEntities);
I agree with Adam Rackis. SqlBulkCopy is the fastest way of transferring bulk records from one data source to another. I used this to copy 20K records and it took less than 3 seconds. Have a look at the example below.
public static void InsertIntoMembers(DataTable dataTable)
{
using (var connection = new SqlConnection(@"data source=;persist security info=True;user id=;password=;initial catalog=;MultipleActiveResultSets=True;App=EntityFramework"))
{
SqlTransaction transaction = null;
connection.Open();
try
{
transaction = connection.BeginTransaction();
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
{
sqlBulkCopy.DestinationTableName = "Members";
sqlBulkCopy.ColumnMappings.Add("Firstname", "Firstname");
sqlBulkCopy.ColumnMappings.Add("Lastname", "Lastname");
sqlBulkCopy.ColumnMappings.Add("DOB", "DOB");
sqlBulkCopy.ColumnMappings.Add("Gender", "Gender");
sqlBulkCopy.ColumnMappings.Add("Email", "Email");
sqlBulkCopy.ColumnMappings.Add("Address1", "Address1");
sqlBulkCopy.ColumnMappings.Add("Address2", "Address2");
sqlBulkCopy.ColumnMappings.Add("Address3", "Address3");
sqlBulkCopy.ColumnMappings.Add("Address4", "Address4");
sqlBulkCopy.ColumnMappings.Add("Postcode", "Postcode");
sqlBulkCopy.ColumnMappings.Add("MobileNumber", "MobileNumber");
sqlBulkCopy.ColumnMappings.Add("TelephoneNumber", "TelephoneNumber");
sqlBulkCopy.ColumnMappings.Add("Deleted", "Deleted");
sqlBulkCopy.WriteToServer(dataTable);
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
}
AsDataReader() extension method, explained in this answer: https://mcmap.net/q/29000/-sqlbulkcopy-from-a-list-lt-gt –
Phenology [2019 Update] EF Core 3.1
Following what have been said above, disabling AutoDetectChangesEnabled in EF Core worked perfectly: the insertion time was divided by 100 (from many minutes to a few seconds, 10k records with cross tables relationships)
The updated code is :
context.ChangeTracker.AutoDetectChangesEnabled = false;
foreach (IRecord record in records) {
//Add records to your database
}
context.ChangeTracker.DetectChanges();
context.SaveChanges();
context.ChangeTracker.AutoDetectChangesEnabled = true; //do not forget to re-enable
context.ChangeTracker.AutoDetectChangesEnabled = true if context now should be disposed?? –
Haukom I would recommend this article on how to do bulk inserts using EF.
Entity Framework and slow bulk INSERTs
He explores these areas and compares perfomance:
I've investigated Slauma's answer (which is awesome, thanks for the idea man), and I've reduced batch size until I've hit optimal speed. Looking at the Slauma's results:
It is visible that there is speed increase when moving from 1 to 10, and from 10 to 100, but from 100 to 1000 inserting speed is falling down again.
So I've focused on what's happening when you reduce batch size to value somewhere in between 10 and 100, and here are my results (I'm using different row contents, so my times are of different value):
Quantity | Batch size | Interval
1000 1 3
10000 1 34
100000 1 368
1000 5 1
10000 5 12
100000 5 133
1000 10 1
10000 10 11
100000 10 101
1000 20 1
10000 20 9
100000 20 92
1000 27 0
10000 27 9
100000 27 92
1000 30 0
10000 30 9
100000 30 92
1000 35 1
10000 35 9
100000 35 94
1000 50 1
10000 50 10
100000 50 106
1000 100 1
10000 100 14
100000 100 141
Based on my results, actual optimum is around value of 30 for batch size. It's less than both 10 and 100. Problem is, I have no idea why is 30 optimal, nor could have I found any logical explanation for it.
As other people have said SqlBulkCopy is the way to do it if you want really good insert performance.
It's a bit cumbersome to implement but there are libraries that can help you with it. There are a few out there but I will shamelesslyplug my own library this time: https://github.com/MikaelEliasson/EntityFramework.Utilities#batch-insert-entities
The only code you would need is:
using (var db = new YourDbContext())
{
EFBatchOperation.For(db, db.BlogPosts).InsertAll(list);
}
So how much faster is it? Very hard to say because it depends on so many factors, computer performance, network, object size etc etc. The performance tests I've made suggests 25k entities can be inserted at around 10s the standard way on localhost IF you optimize your EF configuration like mentioned in the other answers. With EFUtilities that takes about 300ms. Even more interesting is that I have saved around 3 millions entities in under 15 seconds using this method, averaging around 200k entities per second.
The one problem is ofcourse if you need to insert releated data. This can be done efficently into sql server using the method above but it requires you to have an Id generation strategy that let you generate id's in the app-code for the parent so you can set the foreign keys. This can be done using GUIDs or something like HiLo id generation.
EFBatchOperation had a constructor which you pass in the DbContext to rather than passing to every static method. Generic versions of InsertAll and UpdateAll which automatically find the collection, similar to DbContext.Set<T>, would be good too. –
Passage Dispose() context create problems if the entities you Add() rely on other preloaded entities (e.g. navigation properties) in the context
I use similar concept to keep my context small to achieve the same performance
But instead of Dispose() the context and recreate, I simply detach the entities that already SaveChanges()
public void AddAndSave<TEntity>(List<TEntity> entities) where TEntity : class {
const int CommitCount = 1000; //set your own best performance number here
int currentCount = 0;
while (currentCount < entities.Count())
{
//make sure it don't commit more than the entities you have
int commitCount = CommitCount;
if ((entities.Count - currentCount) < commitCount)
commitCount = entities.Count - currentCount;
//e.g. Add entities [ i = 0 to 999, 1000 to 1999, ... , n to n+999... ] to conext
for (int i = currentCount; i < (currentCount + commitCount); i++)
_context.Entry(entities[i]).State = System.Data.EntityState.Added;
//same as calling _context.Set<TEntity>().Add(entities[i]);
//commit entities[n to n+999] to database
_context.SaveChanges();
//detach all entities in the context that committed to database
//so it won't overload the context
for (int i = currentCount; i < (currentCount + commitCount); i++)
_context.Entry(entities[i]).State = System.Data.EntityState.Detached;
currentCount += commitCount;
} }
wrap it with try catch and TrasactionScope() if you need,
not showing them here for keeping the code clean
I know this is a very old question, but one guy here said that developed an extension method to use bulk insert with EF, and when I checked, I discovered that the library costs $599 today (for one developer). Maybe it makes sense for the entire library, however for just the bulk insert this is too much.
Here is a very simple extension method I made. I use that on pair with database first (do not tested with code first, but I think that works the same). Change YourEntities with the name of your context:
public partial class YourEntities : DbContext
{
public async Task BulkInsertAllAsync<T>(IEnumerable<T> entities)
{
using (var conn = new SqlConnection(Database.Connection.ConnectionString))
{
await conn.OpenAsync();
Type t = typeof(T);
var bulkCopy = new SqlBulkCopy(conn)
{
DestinationTableName = GetTableName(t)
};
var table = new DataTable();
var properties = t.GetProperties().Where(p => p.PropertyType.IsValueType || p.PropertyType == typeof(string));
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
table.Columns.Add(new DataColumn(property.Name, propertyType));
}
foreach (var entity in entities)
{
table.Rows.Add(
properties.Select(property => property.GetValue(entity, null) ?? DBNull.Value).ToArray());
}
bulkCopy.BulkCopyTimeout = 0;
await bulkCopy.WriteToServerAsync(table);
}
}
public void BulkInsertAll<T>(IEnumerable<T> entities)
{
using (var conn = new SqlConnection(Database.Connection.ConnectionString))
{
conn.Open();
Type t = typeof(T);
var bulkCopy = new SqlBulkCopy(conn)
{
DestinationTableName = GetTableName(t)
};
var table = new DataTable();
var properties = t.GetProperties().Where(p => p.PropertyType.IsValueType || p.PropertyType == typeof(string));
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
table.Columns.Add(new DataColumn(property.Name, propertyType));
}
foreach (var entity in entities)
{
table.Rows.Add(
properties.Select(property => property.GetValue(entity, null) ?? DBNull.Value).ToArray());
}
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.WriteToServer(table);
}
}
public string GetTableName(Type type)
{
var metadata = ((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace;
var objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace));
var entityType = metadata
.GetItems<EntityType>(DataSpace.OSpace)
.Single(e => objectItemCollection.GetClrType(e) == type);
var entitySet = metadata
.GetItems<EntityContainer>(DataSpace.CSpace)
.Single()
.EntitySets
.Single(s => s.ElementType.Name == entityType.Name);
var mapping = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace)
.Single()
.EntitySetMappings
.Single(s => s.EntitySet == entitySet);
var table = mapping
.EntityTypeMappings.Single()
.Fragments.Single()
.StoreEntitySet;
return (string)table.MetadataProperties["Table"].Value ?? table.Name;
}
}
You can use that against any collection that inherit from IEnumerable, like that:
await context.BulkInsertAllAsync(items);
await bulkCopy.WriteToServerAsync(table); –
Avesta Yes, SqlBulkUpdate is indeed the fastest tool for this type of task. I wanted to find "least effort" generic way for me in .NET Core so I ended up using great library from Marc Gravell called FastMember and writing one tiny extension method for entity framework DB context. Works lightning fast:
using System.Collections.Generic;
using System.Linq;
using FastMember;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
namespace Services.Extensions
{
public static class DbContextExtensions
{
public static void BulkCopyToServer<T>(this DbContext db, IEnumerable<T> collection)
{
var messageEntityType = db.Model.FindEntityType(typeof(T));
var tableName = messageEntityType.GetSchema() + "." + messageEntityType.GetTableName();
var tableColumnMappings = messageEntityType.GetProperties()
.ToDictionary(p => p.PropertyInfo.Name, p => p.GetColumnName());
using (var connection = new SqlConnection(db.Database.GetDbConnection().ConnectionString))
using (var bulkCopy = new SqlBulkCopy(connection))
{
foreach (var (field, column) in tableColumnMappings)
{
bulkCopy.ColumnMappings.Add(field, column);
}
using (var reader = ObjectReader.Create(collection, tableColumnMappings.Keys.ToArray()))
{
bulkCopy.DestinationTableName = tableName;
connection.Open();
bulkCopy.WriteToServer(reader);
connection.Close();
}
}
}
}
}
I'm looking for the fastest way of inserting into Entity Framework
There are some third-party libraries supporting Bulk Insert available:
See: Entity Framework Bulk Insert library
Be careful, when choosing a bulk insert library. Only Entity Framework Extensions supports all kind of associations and inheritances and it's the only one still supported.
Disclaimer: I'm the owner of Entity Framework Extensions
This library allows you to perform all bulk operations you need for your scenarios:
Example
// Easy to use
context.BulkSaveChanges();
// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);
// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);
// Customize Primary Key
context.BulkMerge(customers, operation => {
operation.ColumnPrimaryKeyExpression =
customer => customer.Code;
});
One of the fastest ways to save a list you must apply the following code
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
AutoDetectChangesEnabled = false
Add, AddRange & SaveChanges: Doesn't detect changes.
ValidateOnSaveEnabled = false;
Doesn't detect change tracker
You must add nuget
Install-Package Z.EntityFramework.Extensions
Now you can use the following code
var context = new MyContext();
context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;
context.BulkInsert(list);
context.BulkSaveChanges();
Try to use a Stored Procedure that will get an XML of the data that you want to insert.
I have made an generic extension of @Slauma s example above;
public static class DataExtensions
{
public static DbContext AddToContext<T>(this DbContext context, object entity, int count, int commitCount, bool recreateContext, Func<DbContext> contextCreator)
{
context.Set(typeof(T)).Add((T)entity);
if (count % commitCount == 0)
{
context.SaveChanges();
if (recreateContext)
{
context.Dispose();
context = contextCreator.Invoke();
context.Configuration.AutoDetectChangesEnabled = false;
}
}
return context;
}
}
Usage:
public void AddEntities(List<YourEntity> entities)
{
using (var transactionScope = new TransactionScope())
{
DbContext context = new YourContext();
int count = 0;
foreach (var entity in entities)
{
++count;
context = context.AddToContext<TenancyNote>(entity, count, 100, true,
() => new YourContext());
}
context.SaveChanges();
transactionScope.Complete();
}
}
Here is a performance comparison between using Entity Framework and using SqlBulkCopy class on a realistic example: How to Bulk Insert Complex Objects into SQL Server Database
As others already emphasized, ORMs are not meant to be used in bulk operations. They offer flexibility, separation of concerns and other benefits, but bulk operations (except bulk reading) are not one of them.
SqlBulkCopy is super quick
This is my implementation:
// at some point in my calling code, I will call:
var myDataTable = CreateMyDataTable();
myDataTable.Rows.Add(Guid.NewGuid,tableHeaderId,theName,theValue); // e.g. - need this call for each row to insert
var efConnectionString = ConfigurationManager.ConnectionStrings["MyWebConfigEfConnection"].ConnectionString;
var efConnectionStringBuilder = new EntityConnectionStringBuilder(efConnectionString);
var connectionString = efConnectionStringBuilder.ProviderConnectionString;
BulkInsert(connectionString, myDataTable);
private DataTable CreateMyDataTable()
{
var myDataTable = new DataTable { TableName = "MyTable"};
// this table has an identity column - don't need to specify that
myDataTable.Columns.Add("MyTableRecordGuid", typeof(Guid));
myDataTable.Columns.Add("MyTableHeaderId", typeof(int));
myDataTable.Columns.Add("ColumnName", typeof(string));
myDataTable.Columns.Add("ColumnValue", typeof(string));
return myDataTable;
}
private void BulkInsert(string connectionString, DataTable dataTable)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction = null;
try
{
transaction = connection.BeginTransaction();
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
{
sqlBulkCopy.DestinationTableName = dataTable.TableName;
foreach (DataColumn column in dataTable.Columns) {
sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
sqlBulkCopy.WriteToServer(dataTable);
}
transaction.Commit();
}
catch (Exception)
{
transaction?.Rollback();
throw;
}
}
}
Use SqlBulkCopy:
void BulkInsert(GpsReceiverTrack[] gpsReceiverTracks)
{
if (gpsReceiverTracks == null)
{
throw new ArgumentNullException(nameof(gpsReceiverTracks));
}
DataTable dataTable = new DataTable("GpsReceiverTracks");
dataTable.Columns.Add("ID", typeof(int));
dataTable.Columns.Add("DownloadedTrackID", typeof(int));
dataTable.Columns.Add("Time", typeof(TimeSpan));
dataTable.Columns.Add("Latitude", typeof(double));
dataTable.Columns.Add("Longitude", typeof(double));
dataTable.Columns.Add("Altitude", typeof(double));
for (int i = 0; i < gpsReceiverTracks.Length; i++)
{
dataTable.Rows.Add
(
new object[]
{
gpsReceiverTracks[i].ID,
gpsReceiverTracks[i].DownloadedTrackID,
gpsReceiverTracks[i].Time,
gpsReceiverTracks[i].Latitude,
gpsReceiverTracks[i].Longitude,
gpsReceiverTracks[i].Altitude
}
);
}
string connectionString = (new TeamTrackerEntities()).Database.Connection.ConnectionString;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
{
sqlBulkCopy.DestinationTableName = dataTable.TableName;
foreach (DataColumn column in dataTable.Columns)
{
sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
sqlBulkCopy.WriteToServer(dataTable);
}
transaction.Commit();
}
}
return;
}
As per my knowledge there is no BulkInsert in EntityFramework to increase the performance of the huge inserts.
In this scenario you can go with SqlBulkCopy in ADO.net to solve your problem
WriteToServer that takes a DataTable. –
Ceramic All the solutions written here don't help because when you do SaveChanges(), insert statements are sent to database one by one, that's how Entity works.
And if your trip to database and back is 50 ms for instance then time needed for insert is number of records x 50 ms.
You have to use BulkInsert, here is the link: https://efbulkinsert.codeplex.com/
I got insert time reduced from 5-6 minutes to 10-12 seconds by using it.
Another option is to use SqlBulkTools available from Nuget. It's very easy to use and has some powerful features.
Example:
var bulk = new BulkOperations();
var books = GetBooks();
using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager
.ConnectionStrings["SqlBulkToolsTest"].ConnectionString))
{
bulk.Setup<Book>()
.ForCollection(books)
.WithTable("Books")
.AddAllColumns()
.BulkInsert()
.Commit(conn);
}
trans.Complete();
}
See the documentation for more examples and advanced usage. Disclaimer: I am the author of this library and any views are of my own opinion.
[NEW SOLUTION FOR POSTGRESQL] Hey, I know it's quite an old post, but I have recently run into similar problem, but we were using Postgresql. I wanted to use effective bulkinsert, what turned out to be pretty difficult. I haven't found any proper free library to do so on this DB. I have only found this helper: https://bytefish.de/blog/postgresql_bulk_insert/ which is also on Nuget. I have written a small mapper, which auto mapped properties the way Entity Framework:
public static PostgreSQLCopyHelper<T> CreateHelper<T>(string schemaName, string tableName)
{
var helper = new PostgreSQLCopyHelper<T>("dbo", "\"" + tableName + "\"");
var properties = typeof(T).GetProperties();
foreach(var prop in properties)
{
var type = prop.PropertyType;
if (Attribute.IsDefined(prop, typeof(KeyAttribute)) || Attribute.IsDefined(prop, typeof(ForeignKeyAttribute)))
continue;
switch (type)
{
case Type intType when intType == typeof(int) || intType == typeof(int?):
{
helper = helper.MapInteger("\"" + prop.Name + "\"", x => (int?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
break;
}
case Type stringType when stringType == typeof(string):
{
helper = helper.MapText("\"" + prop.Name + "\"", x => (string)typeof(T).GetProperty(prop.Name).GetValue(x, null));
break;
}
case Type dateType when dateType == typeof(DateTime) || dateType == typeof(DateTime?):
{
helper = helper.MapTimeStamp("\"" + prop.Name + "\"", x => (DateTime?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
break;
}
case Type decimalType when decimalType == typeof(decimal) || decimalType == typeof(decimal?):
{
helper = helper.MapMoney("\"" + prop.Name + "\"", x => (decimal?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
break;
}
case Type doubleType when doubleType == typeof(double) || doubleType == typeof(double?):
{
helper = helper.MapDouble("\"" + prop.Name + "\"", x => (double?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
break;
}
case Type floatType when floatType == typeof(float) || floatType == typeof(float?):
{
helper = helper.MapReal("\"" + prop.Name + "\"", x => (float?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
break;
}
case Type guidType when guidType == typeof(Guid):
{
helper = helper.MapUUID("\"" + prop.Name + "\"", x => (Guid)typeof(T).GetProperty(prop.Name).GetValue(x, null));
break;
}
}
}
return helper;
}
I use it the following way (I had entity named Undertaking):
var undertakingHelper = BulkMapper.CreateHelper<Model.Undertaking>("dbo", nameof(Model.Undertaking));
undertakingHelper.SaveAll(transaction.UnderlyingTransaction.Connection as Npgsql.NpgsqlConnection, undertakingsToAdd));
I showed an example with transaction, but it can also be done with normal connection retrieved from context. undertakingsToAdd is enumerable of normal entity records, which I want to bulkInsert into DB.
This solution, to which I've got after few hours of research and trying, is as you could expect much faster and finally easy to use and free! I really advice you to use this solution, not only for the reasons mentioned above, but also because it's the only one with which I had no problems with Postgresql itself, many other solutions work flawlessly for example with SqlServer.
Taking several notes, this is my implementation with improvements mine and from other answers and comments.
Improvements:
Getting the SQL connection string from my Entity
Using SQLBulk just in some parts, the rest only Entity Framework
Using the same Datetable column names that uses the SQL Database without need of mapping each column
Using the same Datatable name that uses SQL Datatable
public void InsertBulkDatatable(DataTable dataTable)
{
EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder(ConfigurationManager.ConnectionStrings["MyDbContextConnectionName"].ConnectionString);
string cs = entityBuilder.ProviderConnectionString;
using (var connection = new SqlConnection(cs))
{
SqlTransaction transaction = null;
connection.Open();
try
{
transaction = connection.BeginTransaction();
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
{
sqlBulkCopy.DestinationTableName = dataTable.TableName; //Uses the SQL datatable to name the datatable in c#
//Maping Columns
foreach (DataColumn column in dataTable.Columns) {
sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
sqlBulkCopy.WriteToServer(dataTable);
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
}
The secret is to insert into an identical blank staging table. Inserts are lightening quick. Then run a single insert from that into your main large table. Then truncate the staging table ready for the next batch.
ie.
insert into some_staging_table using Entity Framework.
-- Single insert into main table (this could be a tiny stored proc call)
insert into some_main_already_large_table (columns...)
select (columns...) from some_staging_table
truncate table some_staging_table
Have you ever tried to insert through a background worker or task?
In my case, im inserting 7760 registers, distributed in 182 different tables with foreign key relationships ( by NavigationProperties).
Without the task, it took 2 minutes and a half.
Within a Task ( Task.Factory.StartNew(...) ), it took 15 seconds.
Im only doing the SaveChanges() after adding all the entities to the context. (to ensure data integrity)
You may use Bulk package library. Bulk Insert 1.0.0 version is used in projects having Entity framework >=6.0.0 .
More description can be found here- Bulkoperation source code
TL;DR I know it is an old post, but I have implemented a solution starting from one of those proposed by extending it and solving some problems of this; moreover I have also read the other solutions presented and compared to these it seems to me to propose a solution that is much more suited to the requests formulated in the original question.
In this solution I extend Slauma's approach which I would say is perfect for the case proposed in the original question, and that is to use Entity Framework and Transaction Scope for an expensive write operation on the db.
In Slauma's solution - which incidentally was a draft and was only used to get an idea of the speed of EF with a strategy to implement bulk-insert - there were problems due to:
I also extended the case study presented by Slauma by reporting an example that includes the contextual insertion of several dependent entities.
The performances that I have been able to verify have been of 10K rec/min inserting in the db a block of 200K wide records approximately 1KB each. The speed was constant, there was no degradation in performance and the test took about 20 minutes to run successfully.
The solution in detail
the method that presides over the bulk-insert operation inserted in an example repository class:
abstract class SomeRepository {
protected MyDbContext myDbContextRef;
public void ImportData<TChild, TFather>(List<TChild> entities, TFather entityFather)
where TChild : class, IEntityChild
where TFather : class, IEntityFather
{
using (var scope = MyDbContext.CreateTransactionScope())
{
MyDbContext context = null;
try
{
context = new MyDbContext(myDbContextRef.ConnectionString);
context.Configuration.AutoDetectChangesEnabled = false;
entityFather.BulkInsertResult = false;
var fileEntity = context.Set<TFather>().Add(entityFather);
context.SaveChanges();
int count = 0;
//avoids an issue with recreating context: EF duplicates the first commit block of data at the end of transaction!!
context = MyDbContext.AddToContext<TChild>(context, null, 0, 1, true);
foreach (var entityToInsert in entities)
{
++count;
entityToInsert.EntityFatherRefId = fileEntity.Id;
context = MyDbContext.AddToContext<TChild>(context, entityToInsert, count, 100, true);
}
entityFather.BulkInsertResult = true;
context.Set<TFather>().Add(fileEntity);
context.Entry<TFather>(fileEntity).State = EntityState.Modified;
context.SaveChanges();
}
finally
{
if (context != null)
context.Dispose();
}
scope.Complete();
}
}
}
interfaces used for example purposes only:
public interface IEntityChild {
//some properties ...
int EntityFatherRefId { get; set; }
}
public interface IEntityFather {
int Id { get; set; }
bool BulkInsertResult { get; set; }
}
db context where I implemented the various elements of the solution as static methods:
public class MyDbContext : DbContext
{
public string ConnectionString { get; set; }
public MyDbContext(string nameOrConnectionString)
: base(nameOrConnectionString)
{
Database.SetInitializer<MyDbContext>(null);
ConnectionString = Database.Connection.ConnectionString;
}
/// <summary>
/// Creates a TransactionScope raising timeout transaction to 30 minutes
/// </summary>
/// <param name="_isolationLevel"></param>
/// <param name="timeout"></param>
/// <remarks>
/// It is possible to set isolation-level and timeout to different values. Pay close attention managing these 2 transactions working parameters.
/// <para>Default TransactionScope values for isolation-level and timeout are the following:</para>
/// <para>Default isolation-level is "Serializable"</para>
/// <para>Default timeout ranges between 1 minute (default value if not specified a timeout) to max 10 minute (if not changed by code or updating max-timeout machine.config value)</para>
/// </remarks>
public static TransactionScope CreateTransactionScope(IsolationLevel _isolationLevel = IsolationLevel.Serializable, TimeSpan? timeout = null)
{
SetTransactionManagerField("_cachedMaxTimeout", true);
SetTransactionManagerField("_maximumTimeout", timeout ?? TimeSpan.FromMinutes(30));
var transactionOptions = new TransactionOptions();
transactionOptions.IsolationLevel = _isolationLevel;
transactionOptions.Timeout = TransactionManager.MaximumTimeout;
return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
}
private static void SetTransactionManagerField(string fieldName, object value)
{
typeof(TransactionManager).GetField(fieldName, BindingFlags.NonPublic | BindingFlags.Static).SetValue(null, value);
}
/// <summary>
/// Adds a generic entity to a given context allowing commit on large block of data and improving performance to support db bulk-insert operations based on Entity Framework
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="context"></param>
/// <param name="entity"></param>
/// <param name="count"></param>
/// <param name="commitCount">defines the block of data size</param>
/// <param name="recreateContext"></param>
/// <returns></returns>
public static MyDbContext AddToContext<T>(MyDbContext context, T entity, int count, int commitCount, bool recreateContext) where T : class
{
if (entity != null)
context.Set<T>().Add(entity);
if (count % commitCount == 0)
{
context.SaveChanges();
if (recreateContext)
{
var contextConnectionString = context.ConnectionString;
context.Dispose();
context = new MyDbContext(contextConnectionString);
context.Configuration.AutoDetectChangesEnabled = false;
}
}
return context;
}
}
Configuration.LazyLoadingEnabled = false; Configuration.ProxyCreationEnabled = false;
these are too effect to speed without AutoDetectChangesEnabled = false; and i advise to use different table header from dbo. generally i use like nop,sop,tbl etc..
But, for more than (+4000) inserts i recommend to use stored procedure. attached the time elapsed.
I did inserted it 11.788 rows in 20"
thats it code
public void InsertDataBase(MyEntity entity)
{
repository.Database.ExecuteSqlCommand("sp_mystored " +
"@param1, @param2"
new SqlParameter("@param1", entity.property1),
new SqlParameter("@param2", entity.property2));
}
Use this technique to increase the speed of inserting records in Entity Framework. Here I use a simple stored procedure to insert the records. And to execute this stored procedure I use .FromSql() method of Entity Framework which executes Raw SQL.
CREATE PROCEDURE TestProc
@FirstParam VARCHAR(50),
@SecondParam VARCHAR(50)
AS
Insert into SomeTable(Name, Address) values(@FirstParam, @SecondParam)
GO
Next, loop through all your 4000 records and add the Entity Framework code which executes the stored
procedure onces every 100th loop.
For this I create a string query to execute this procedure, keep on appending to it every sets of record.
Then check it the loop is running in the multiples of 100 and in that case execute it using .FromSql().
So for 4000 records I only have to execute the procedure for only 4000/100 = 40 times.
string execQuery = "";
var context = new MyContext();
for (int i = 0; i < 4000; i++)
{
execQuery += "EXEC TestProc @FirstParam = 'First'" + i + "'', @SecondParam = 'Second'" + i + "''";
if (i % 100 == 0)
{
context.Student.FromSql(execQuery);
execQuery = "";
}
}
© 2022 - 2024 — McMap. All rights reserved.