Fastest Way of Inserting in Entity Framework

Question

I m looking for the fastest way of inserting into 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

User · Answer

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

User · Answer

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

User · Answer

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    commitCount   1  recreateContext   true  more than 10 minutes commitCount   10  recreateContext   true  241 sec   commitCount   100  recreateContext   true  164 sec commitCount   1000  recreateContext   true  191 sec   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

User · Answer

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  The stored procedure code  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   Check the below code  string execQuery    quot  quot   var context   new MyContext    for  int i   0  i  lt  4000  i          execQuery     quot EXEC TestProc  FirstParam    First  quot    i    quot      SecondParam    Second  quot    i    quot    quot        if  i   100    0                context Student FromSql execQuery           execQuery    quot  quot

User · Answer

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

User · Answer

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

User · Answer

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   the timeout of the transaction  by default 1 minute extendable via code to max 10 minutes   the duplication of the first block of data with a width equal to the size of the commit used at the end of the transaction  this problem is quite weird and circumvented by means of a workaround    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 lt TChild  TFather gt  List lt TChild gt  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 lt TFather gt    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 lt TChild gt  context  null  0  1  true                    foreach  var entityToInsert in entities                                          count                      entityToInsert EntityFatherRefId   fileEntity Id                      context   MyDbContext AddToContext lt TChild gt  context  entityToInsert  count  100  true                                      entityFather BulkInsertResult   true                  context Set lt TFather gt    Add fileEntity                   context Entry lt TFather gt  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 lt MyDbContext gt  null           ConnectionString   Database Connection ConnectionString                   lt summary gt          Creates a TransactionScope raising timeout transaction to 30 minutes          lt  summary gt           lt param name  quot  isolationLevel quot  gt  lt  param gt           lt param name  quot timeout quot  gt  lt  param gt           lt remarks gt          It is possible to set isolation-level and timeout to different values  Pay close attention managing these 2 transactions working parameters           lt para gt Default TransactionScope values for isolation-level and timeout are the following  lt  para gt           lt para gt Default isolation-level is  quot Serializable quot  lt  para gt           lt para gt 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  lt  para gt           lt  remarks gt      public static TransactionScope CreateTransactionScope IsolationLevel  isolationLevel   IsolationLevel Serializable  TimeSpan  timeout   null                SetTransactionManagerField  quot  cachedMaxTimeout quot   true           SetTransactionManagerField  quot  maximumTimeout quot   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                    lt summary gt          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          lt  summary gt           lt typeparam name  quot T quot  gt  lt  typeparam gt           lt param name  quot context quot  gt  lt  param gt           lt param name  quot entity quot  gt  lt  param gt           lt param name  quot count quot  gt  lt  param gt           lt param name  quot commitCount quot  gt defines the block of data size lt  param gt           lt param name  quot recreateContext quot  gt  lt  param gt           lt returns gt  lt  returns gt      public static MyDbContext AddToContext lt T gt  MyDbContext context  T entity  int count  int commitCount  bool recreateContext  where T   class               if  entity    null              context Set lt T gt    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

User · Answer

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

User · Answer

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

User · Answer

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 lt Book gt                 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

User · Answer

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    Call SaveChanges   once after ALL records  Call SaveChanges   after for example 100 records  Call SaveChanges   after for example 100 records and dispose the context and create a new one  Disable change detection   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 lt Entity gt    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    commitCount   1        recreateContext   false  many hours  That s your current procedure  commitCount   100      recreateContext   false  more than 20 minutes commitCount   1000     recreateContext   false  242 sec commitCount   10000    recreateContext   false  202 sec commitCount   100000   recreateContext   false  199 sec commitCount   1000000  recreateContext   false  out of memory exception commitCount   1        recreateContext   true   more than 10 minutes commitCount   10       recreateContext   true   241 sec commitCount   100      recreateContext   true   164 sec commitCount   1000     recreateContext   true   191 sec   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  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

User · Answer

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

User · Answer

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    Default EF  57 minutes to complete adding 30 000 records  Replacing with ADO NET Code  25 seconds for those same 30 000  Context Bloat- Keep the active Context Graph small by using a new context for each Unit of Work  same 30 000 inserts take 33 seconds  Large Lists - Turn off AutoDetectChangesEnabled  brings the time down to about 20 seconds  Batching  down to 16 seconds  DbTable AddRange   -  performance is in the 12 range

User · Answer

Use stored procedure that takes input data in form of xml to insert data   From your c  code pass insert data as xml   e g in c   syntax would be like this   object id application   db ExecuteScalar  procSaveApplication   xml

User · Answer

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

User · Answer

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

User · Answer

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  amp  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

User · Answer

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 lt TEntity gt  List lt TEntity gt  entities  where TEntity   class    const int CommitCount   1000    set your own best performance number here int currentCount   0   while  currentCount  lt  entities Count            make sure it don t commit more than the entities you have     int commitCount   CommitCount      if   entities Count - currentCount   lt  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  lt   currentCount   commitCount   i                     context Entry entities i   State   System Data EntityState Added            same as calling  context Set lt TEntity gt    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  lt   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

User · Answer

I m looking for the fastest way of inserting into Entity Framework   There are some third-party libraries supporting Bulk Insert available    Z EntityFramework Extensions  Recommended  EFUtilities EntityFramework BulkInsert   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    Bulk SaveChanges Bulk Insert Bulk Delete Bulk Update Bulk Merge   Example     Easy to use context BulkSaveChanges        Easy to customize context BulkSaveChanges bulk   gt  bulk BatchSize   100       Perform Bulk Operations context BulkDelete customers   context BulkInsert customers   context BulkUpdate customers       Customize Primary Key context BulkMerge customers  operation   gt       operation ColumnPrimaryKeyExpression            customer   gt  customer Code

User · Answer

Yes  SqlBulkUpdate is indeed the fastest tool for this type of task  I wanted to find  quot least effort quot  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 lt T gt  this DbContext db  IEnumerable lt T gt  collection                        var messageEntityType   db Model FindEntityType typeof T                 var tableName   messageEntityType GetSchema      quot   quot    messageEntityType GetTableName                var tableColumnMappings   messageEntityType GetProperties                    ToDictionary p   gt  p PropertyInfo Name  p   gt  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

User · Answer

Try to use a Stored Procedure that will get an XML of the data that you want to insert

User · Answer

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

User · Answer

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

User · Answer

This combination increase speed well enough   context Configuration AutoDetectChangesEnabled   false  context Configuration ValidateOnSaveEnabled   false

User · Answer

I have made an generic extension of  Slauma s example above   public static class DataExtensions       public static DbContext AddToContext lt T gt  this DbContext context  object entity  int count  int commitCount  bool recreateContext  Func lt DbContext gt  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 lt YourEntity gt  entities        using  var transactionScope   new TransactionScope                  DbContext context   new YourContext            int count   0          foreach  var entity in entities                          count              context   context AddToContext lt TenancyNote gt  entity  count  100  true                       gt  new YourContext                       context SaveChanges            transactionScope Complete

User · Answer

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 lt T gt  IEnumerable lt T gt  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   gt  p PropertyType IsValueType    p PropertyType    typeof string                 foreach  var property in properties                                Type propertyType   property PropertyType                  if  propertyType IsGenericType  amp  amp                      propertyType GetGenericTypeDefinition      typeof Nullable lt  gt                                          propertyType   Nullable GetUnderlyingType propertyType                                      table Columns Add new DataColumn property Name  propertyType                               foreach  var entity in entities                                table Rows Add                      properties Select property   gt  property GetValue entity  null     DBNull Value  ToArray                                bulkCopy BulkCopyTimeout   0              await bulkCopy WriteToServerAsync table                        public void BulkInsertAll lt T gt  IEnumerable lt T gt  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   gt  p PropertyType IsValueType    p PropertyType    typeof string                 foreach  var property in properties                                Type propertyType   property PropertyType                  if  propertyType IsGenericType  amp  amp                      propertyType GetGenericTypeDefinition      typeof Nullable lt  gt                                          propertyType   Nullable GetUnderlyingType propertyType                                      table Columns Add new DataColumn property Name  propertyType                               foreach  var entity in entities                                table Rows Add                      properties Select property   gt  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 lt EntityType gt  DataSpace OSpace                   Single e   gt  objectItemCollection GetClrType e     type            var entitySet   metadata              GetItems lt EntityContainer gt  DataSpace CSpace               Single                EntitySets              Single s   gt  s ElementType Name    entityType Name            var mapping   metadata GetItems lt EntityContainerMapping gt  DataSpace CSSpace                   Single                    EntitySetMappings                  Single s   gt  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

User · Answer

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 lt T gt  CreateHelper lt T gt  string schemaName  string tableName                        var helper   new PostgreSQLCopyHelper lt T gt   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   gt   int  typeof T  GetProperty prop Name  GetValue x  null                                break                                                case Type stringType when stringType    typeof string                                                         helper   helper MapText        prop Name         x   gt   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   gt   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   gt   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   gt   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   gt   float  typeof T  GetProperty prop Name  GetValue x  null                                break                                                case Type guidType when guidType    typeof Guid                                                         helper   helper MapUUID        prop Name         x   gt   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 lt Model Undertaking gt   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

[c#] Fastest Way of Inserting in Entity Framework

Examples related to c#

Examples related to sql

Examples related to entity-framework