Entity framework core bulk insert

Entity framework core bulk insert DEFAULT

EFCore.BulkExtensions

EntityFrameworkCore extensions:
-Bulk operations (Insert, Update, Delete, Read, Upsert, Sync, Truncate) and
-Batch ops (Delete, Update).
Library is Lightweight and very Efficient, having all mostly used CRUD operation.
Was selected in top 20 EF Core Extensions recommended by Microsoft.
Current version is using EF Core 5 and is targeting NetStandard 2.1 so it can be used on project targeting Net 5.
At the moment supports Microsoft SQL Server(2008+) and SQLite.
Under the hood uses SqlBulkCopy for Insert, for Update/Delete combines BulkInsert with raw Sql MERGE.
For SQLite there is no BulkCopy, instead library uses plain SQL combined with UPSERT.
Bulk Tests can not have UseInMemoryDb because InMemoryProvider does not support Relational-specific methods.

Available on
Package manager console command for installation: Install-Package EFCore.BulkExtensions

NugetTargetUsed EF v.For projects targeting
6.xNet 6.0EF Core 6.0Net 6.0+
5.xNetStandard 2.1EF Core 5.0Net 5.0+
3.xNetStandard 2.0EF Core 3.nNetCore(3.0+) or NetFrm(4.6.1+) MoreInfo
2.xNetStandard 2.0EF Core 2.nNetCore(2.0+) or NetFrm(4.6.1+)
1.xNetStandard 1.4EF Core 1.0NetCore(1.0+)

Contributing

If you find this project useful you can mark it by leaving a Github Star.
If you would like to support the Project by making a Donation ($10) #BuyMeBeer, you are welcome to do so:
Button or Button(0 fee)
Please read CONTRIBUTING for details on code of conduct, and the process for submitting pull requests.
NuGet
Want to Contact us for Hire (Development & Consulting): www.codis.tech

Usage

It's pretty simple and straightforward.
Bulk Extensions are made on DbContext class and can be used like this (supported both regular and Async methods):

context.BulkInsert(entitiesList); context.BulkInsertAsync(entitiesList); context.BulkInsertOrUpdate(entitiesList); context.BulkInsertOrUpdateAsync(entitiesList); //Upsertcontext.BulkInsertOrUpdateOrDelete(entitiesList); context.BulkInsertOrUpdateOrDeleteAsync(entitiesList);//Synccontext.BulkUpdate(entitiesList); context.BulkUpdateAsync(entitiesList); context.BulkDelete(entitiesList); context.BulkDeleteAsync(entitiesList); context.BulkRead(entitiesList); context.BulkReadAsync(entitiesList); context.Truncate<Entity>(); context.TruncateAsync<Entity>();

SQLite requires package: SQLitePCLRaw.bundle_e_sqlite3 with call to

Batch Extensions are made on IQueryable DbSet and can be used as in the following code segment.
They are done as pure sql and no check is done whether some are prior loaded in memory and are being Tracked.
(updateColumns is optional param in which PropertyNames added explicitly when need update to it's default value)

// Deletecontext.Items.Where(a=>a.ItemId>500).BatchDelete(); context.Items.Where(a=>a.ItemId>500).BatchDeleteAsync(); // Update (using Expression arg.) supports Increment/Decrement context.Items.Where(a=>a.ItemId<=500).BatchUpdate(a=>newItem { Quantity=a.Quantity+100 }); // can be as value '+100' or as variable '+incrementStep' (int incrementStep = 100;)// Update (via simple object)context.Items.Where(a=>a.ItemId<=500).BatchUpdate(newItem { Description="Updated" }); context.Items.Where(a=>a.ItemId<=500).BatchUpdateAsync(newItem { Description="Updated" }); // Update (via simple object) - requires additional Argument for setting to Property default valuevarupdateColumns=newList<string> { nameof(Item.Quantity) }; // Update 'Quantity' to default value('0'-zero)varq=context.Items.Where(a=>a.ItemId<=500); intaffected=q.BatchUpdate(newItem { Description="Updated" }, updateColumns);//result assigned to variable

Bulk info

If Windows Authentication is used then in ConnectionString there should be Trusted_Connection=True; because Sql credentials are required to stay in connection.

When used directly each of these operations are separate transactions and are automatically committed.
And if we need multiple operations in single procedure then explicit transaction should be used, for example:

using (vartransaction=context.Database.BeginTransaction()) { context.BulkInsert(entities1List); context.BulkInsert(entities2List); transaction.Commit(); }

BulkInsertOrUpdate method can be used when there is need for both operations but in one connection to database.
It makes Update when PK(PrimaryKey) is matched, otherwise does Insert.

BulkInsertOrUpdateOrDelete effectively synchronizes table rows with input data.
Those in Db that are not found in the list will be deleted.
Partial Sync can be done on table subset using expression set on config with method:

For Sqlite Not supported since lite only has UPSERT statement. Way to achieve there sync functionality is to Select or BulkRead existing data from DB, split list into sublists and call separately Bulk methods for BulkInsertOrUpdate and Delete.

BulkRead does SELECT and JOIN based on one or more Unique columns that are specified in Config .
More info in the Example at the bottom.

Note: Bulk ops have optional argument Type type that can be set to type of Entity if list has dynamic runtime objects or is inhereted from Entity class.

BulkConfig arguments

Bulk methods can have optional argument BulkConfig with properties (bool, int, object, List):

PROPERTY : DEFAULTvalueSqlBulkCopyOptions: Default-----------------------PropertiesToInclude: null, PreserveInsertOrder: true, PropertiesToIncludeOnCompare: null, SetOutputIdentity: false, PropertiesToIncludeOnUpdate: null, BatchSize: 2000, PropertiesToExclude: null, NotifyAfter: null, PropertiesToExcludeOnCompare: null, BulkCopyTimeout: null, PropertiesToExcludeOnUpdate: null, EnableStreaming: false, UpdateByProperties: null, UseTempDB: false, EnableShadowProperties: false, UniqueTableNameTempDb: true, IncludeGraph: false, CustomDestinationTableName: null, OmitClauseExistsExcept: false, TrackingEntities: false, DoNotUpdateIfTimeStampChanged: false, WithHoldlock: true, SRID: 4326, CalculateStats: false, DateTime2PrecisionForceRound: false, -----------------------METHOD: SetSynchronizeFilter<T>

If we want to change defaults, BulkConfig should be added explicitly with one or more bool properties set to true, and/or int props like BatchSize to different number.
Config also has DelegateFunc for setting Underlying-Connection/Transaction, e.g. in UnderlyingTest.
When doing update we can chose to exclude one or more properties by adding their names into PropertiesToExclude, or if we need to update less then half column then PropertiesToInclude can be used. Setting both Lists are not allowed.

When using the BulkInsert_/OrUpdate methods, you may also specify the PropertiesToIncludeOnCompare and PropertiesToExcludeOnCompare properties. By adding a column name to the PropertiesToExcludeOnCompare, will allow it to be inserted and updated but will not update the row if any of the other columns in that row did not change. For example, if you are importing bulk data and want to keep an internal CreateDate or UpdateDate, you add those columns to the PropertiesToExcludeOnCompare.
Another option that may be used in the same scenario are the PropertiesToIncludeOnUpdate and PropertiesToExcludeOnUpdate properties. These properties will allow you to specify insert-only columns such as CreateDate and CreatedBy.

If we want Insert only new and skip existing ones in Db (Insert_if_not_Exist) then use BulkInsertOrUpdate with config

Additionaly there is UpdateByProperties for specifying custom properties, by which we want update to be done.
When setting multiple props in UpdateByProps then match done by columns combined, like unique constrain based on those cols.
Using UpdateByProperties while also having Identity column requires that Id property be Excluded.
If NotifyAfter is not set it will have same value as BatchSize while BulkCopyTimeout when not set has SqlBulkCopy default which is 30 seconds and if set to 0 it indicates no limit.

SetOutputIdentity have purpose only when PK has Identity (usually int type with AutoIncrement), while if PK is Guid(sequential) created in Application there is no need for them.
Also Tables with Composite Keys have no Identity column so no functionality for them in that case either.

varbulkConfig=newBulkConfig { SetOutputIdentity=true, BatchSize=4000 }; context.BulkInsert(entList, bulkConfig); context.BulkInsertOrUpdate(entList, newBulkConfig { SetOutputIdentity=true }); context.BulkInsertOrUpdate(entList, b=>b.SetOutputIdentity=true); // example of BulkConfig set with Action arg.

PreserveInsertOrder is true by default and makes sure that entites are inserted to Db as ordered in entitiesList.
When table has Identity column (int autoincrement) with 0 values in list they will temporary be automatically changed from 0s into range -N:-1.
Or it can be manually set with proper values for order (Negative values used to skip conflict with existing ones in Db).
Here single Id value itself doesn't matter, db will change it to next in sequence, what matters is their mutual relationship for sorting.
Insertion order is implemented with TOP in conjuction with ORDER BY. stackoverflow:merge-into-insertion-order.
This config should remain true when SetOutputIdentity is set to true on Entity containing NotMapped Property. issues/76
When using SetOutputIdentity Id values will be updated to new ones from database.
With BulkInsertOrUpdate for those that will be updated it has to match with Id column, or other unique column(s) if using UpdateByProperties.
For Sqlite combination of BulkInsertOrUpdate and IdentityId automatic set will not work properly since it does not have full MERGE capabilities like SqlServer. Instead list can be split into 2 lists, and call separately BulkInsert and BulkUpdate.

SetOutputIdentity is useful when BulkInsert is done to multiple related tables, that have Identity column.
After Insert is done to first table, we need Id-s (if using Option 1) that were generated in Db because they are FK(ForeignKey) in second table.
It is implemented with OUTPUT as part of MERGE Query, so in this case even the Insert is not done directly to TargetTable but to TempTable and then Merged with TargetTable.
When used Id-s will be updated in entitiesList, and if PreserveInsertOrder is set to false then entitiesList will be cleared and reloaded.
Example of SetOutputIdentity with parent-child FK related tables:

intnumberOfEntites=1000; varentities=newList<Item>(); varsubEntities=newList<ItemHistory>(); for (inti=1; i<=numberOfEntites; i++) { varentity=newItem { Name=$"Name {i}" }; entity.ItemHistories=newList<ItemHistory>() { newItemHistory { Remark=$"Info {i}.1" }, newItemHistory { Remark=$"Info {i}.2" } }; entities.Add(entity); } // Option 1using (vartransaction=context.Database.BeginTransaction()) { context.BulkInsert(entities, newBulkConfig { SetOutputIdentity=true }); foreach (varentityinentities) { foreach (varsubEntityinentity.ItemHistories) { subEntity.ItemId=entity.ItemId; // setting FK to match its linked PK that was generated in DB } subEntities.AddRange(entity.ItemHistories); } context.BulkInsert(subEntities); transaction.Commit(); } // Option 2 using Graph (only for SQL Server) - all entities in relationship with main ones in list are BulkInsertUpdatedcontext.BulkInsert(entities, b=>b.IncludeGraph=true);

When CalculateStats set to True the result returned in (StatsNumber-Inserted/Updated/Deleted).
If used for pure Insert (with Batching) then SetOutputIdentity should also be configured because Merge is required.
TrackingEntities can be set to True if we want to have tracking of entities from BulkRead or if SetOutputIdentity is set.
UseTempDB when set then BulkOperation has to be inside Transaction.
UniqueTableNameTempDb when changed to false temp table name will be only 'Temp' without random numbers.
CustomDestinationTableName can be set with 'TableName' only or with 'Schema.TableName'.
EnableShadowProperties to add (normal) Shadow Property and persist value. Disables automatic discrimator, use manual method.
IncludeGraph when set all entites that have relations with main ones from the list are also merged into theirs tables.
OmitClauseExistsExcept removes the clause from Merge statement, useful when need to active triggers even for same data.
_ Also in some sql collation small and capital letters are considered same (case-insensitive) so for BulkUpdate set it false.
DoNotUpdateIfTimeStampChanged if set checks TimeStamp for Concurrency, ones with conflict will not be updated.
SRID Spatial Reference Identifier - for SQL Server with NetTopologySuite.
DateTime2PrecisionForceRound If dbtype datetime2 has precision less then default 7, example 'datetime2(3)' SqlBulkCopy does Floor instead of Round so when this Property is set then Rounding will be done in memory to make sure inserted values are same as with regular SaveChanges.

SqlBulkCopyOptions is Enum with [Flags] attribute which enables specifying one or more options:
Default, KeepIdentity, CheckConstraints, TableLock, KeepNulls, FireTriggers, UseInternalTransaction
If need to set Identity PK in memory, Not let DB do the autoincrement, then need to use KeepIdentity:

Useful for example when copying from one Db to another.

SetSynchronizeFilter A method that receives and sets expresion filter on entities to delete when using BulkInsertOrUpdateOrDelete.

Last optional argument is Action progress (Example in EfOperationTest.csRunInsert() with WriteProgress()).

context.BulkInsert(entitiesList, null, (a) =>WriteProgress(a));

Library supports Global Query Filters and Value Conversions as well.
Additionally BatchUpdate and named Property works with EnumToString Conversion.
It can map OwnedTypes, also next are links with info how to achieve NestedOwnedTypes and OwnedInSeparateTable.
Table splitting are somewhat specific but could be configured in way Set TableSplit.
With Computed and Timestamp Columns it will work in a way that they are automatically excluded from Insert. And when combined with SetOutputIdentity they will be Selected.
Spatial types, like Geometry, also supported and if Entity has one, clause EXIST ... EXCEPT is skipped because it's not comparable.
Performance for bulk ops measured with named: 'BulkExecute' (tags: 'operationType', 'entitiesCount')
Bulk Extension methods can be Overridden if required, for example to set AuditInfo.
If having problems with Deadlock there is useful info in issue/46.

TPH inheritance

When having TPH (Table-Per-Hierarchy) inheritance model it can be set in 2 ways.
First is automatically by Convention in which case Discriminator column is not directly in Entity but is Shadow Property.
And second is to explicitly define Discriminator property in Entity and configure it with .
Important remark regarding the first case is that since we can not set directly Discriminator to certain value we need first to add list of entities to DbSet where it will be set and after that we can call Bulk operation. Note that SaveChanges are not called and we could optionally turn off TrackingChanges for performance. Example:

publicclassStudent : Person { ... } context.Students.AddRange(entities); // adding to Context so that Shadow property 'Discriminator' gets setcontext.BulkInsert(entities);

TPT (Table-Per-Type) as of v5 is partially supported.

Read example

When we need to Select from big List of some Unique Prop./Column use BulkRead (JOIN done in Sql) for Efficiency:

// instead of WhereIN which will TimeOut for List with several thousand recordsvarentities=context.Items.Where(a=>itemsNames.Contains(a.Name)).AsNoTracking().ToList(); //SQL IN operator// or JOIN in Memory that loads entire tablevarentities=context.Items.Join(itemsNames, a=>a.Name, p=>p, (a, p) =>a).AsNoTracking().ToList(); // USEvaritems=itemsNames.Select(a=>newItem { Name=a }).ToList(); // creating list of Items where only Name is setvarbulkConfig=newBulkConfig { UpdateByProperties=newList<string> { nameof(Item.Name) } }; context.BulkRead(items, bulkConfig); // Items list will be loaded from Db with data(other properties)

Performances

Following are performances (in seconds for SQL Server):

Operations\Rows100,000 EF100,000 EFBulk1,000,000 EFBulk
Insert38.98 s2.10 s17.99 s
Update109.25 s3.96 s31.45 s
Delete7.26 s2.04 s12.18 s
---------------------------------------------------------------
Together70.70 s5.88 s56.84 s

TestTable has 6 columns (Guid, string, string, int, decimal?, DateTime).
All were inserted and 2 of them (string, DateTime) were updated.
Test was done locally on following configuration: INTEL Core i5-3570K 3.40GHz, DDRIII 8GB x 2, SSD 840 EVO 128 GB.
For small data sets there is an overhead since most Bulk ops need to create Temp table and also Drop it after finish. Probably good advice would be to use Bulk ops for sets greater than 1000.

Sours: https://github.com/borisdj/EFCore.BulkExtensions

Entity Framework Core Bulk Insert

Introduction

When you want to insert hundreds, thousands, or millions of entities using method, you will notice that your application performance is INSANELY slow. Because requires one database round-trip for every entity to insert. So if you need to insert 10000 entities, then 10000 database round-trips will be performed and your application suffers from performances issues.

Entity Framework Core has made great improvement and is way faster than EF6, but your application performance can even faster than that by using a 3rd party library Entity Framework Extensions.

BulkInsert

Entity Framework Extensions provides an extension method BulkInsert which requires the minimum database round-trips as compared to . By example under the hood for SQL Server, a simple could be performed.

Performance Comparisons

Operations1,000 Entities2,000 Entities5,000 Entities
SaveChanges1,000 ms2,000 ms5,000 ms
BulkInsert6 ms10 ms15 ms

Steps to use BulkInsert

  1. CREATE a list
  2. ADD entity to the list
  3. USE BulkInsert
  4. Done!
using (var ctx = new CustomerContext()) { List<Customer> customers = new List<Customer>(); foreach(var line in lines) { var customer = new Customer(); customers.Add(customer); } ctx.BulkInsert(customers); }

Read more: BulkInsert


Sours: https://entityframeworkcore.com/saving-data-bulk-insert
  1. Linux (beta apps)
  2. Visa gift card nintendo eshop
  3. Best hip hop instrumentals 2020
  4. Harmonic balancer puller near me
  5. Albuquerque journal pets for sale

Entity Framework Classic Bulk Insert

Description

The EF Bulk Insert feature let you insert thousands of entities in your database efficiently.

This feature is provided by the library EF Extensions(Included with EF Classic). EF Extensions is used by over 2000 customers all over the world and supports all Entity Framework versions (EF4, EF5, EF6, EF Core, EF Classic).

context.BulkInsert(list); context.BulkInsert(list, options => options.BatchSize = 100);

Try it: NET Core | NET Framework

Performance Comparison

Operations1,000 Entities2,000 Entities5,000 Entities
SaveChanges1,200 ms2,400 ms6,000 ms
BulkInsert50 ms55 ms75 ms

Try it: NET Core | NET Framework

HINT:

Performance may differ from a database to another. A lot of factors might affect the benchmark time such as index, column type, latency, throttling, etc.

Why BulkInsert is faster than SaveChanges?

Inserting thousands of entities for an initial load or a file importation is a typical scenario.

The method makes it quite impossible to handle this kind of situation due to the number of database round-trips required. The perform one database round-trip for every entity to insert. So, if you need to insert 10,000 entities, 10,000 database round-trips will be performed which is INSANELY slow.

The in counterpart requires the minimum database round-trips possible. For example, under the hood of SQL Server, a is performed to insert 10,000 entities which is the most effective tactic available.

Real Life Scenarios

Bulk Insert invoice and related invoice items (Include Graph)

You need to insert a list of and include related . By default, the doesn't include the graph but you can enable it with the IncludeGraph option.

context.BulkInsert(invoices, options => options.IncludeGraph = true);

Try it: NET Core | NET Framework

Bulk Insert customers that don't already exist

You need to insert a list of , but only the ones that doesn't already exists using the customer codes as the key.

  • The option let you insert customers that doesn't already exists.
  • The ColumnPrimaryKey option let you choose the key.
context.BulkInsert(customers, options => { options.InsertIfNotExists = true; options.ColumnPrimaryKeyExpression = x => new { x.Code }; });

Try it: NET Core | NET Framework

Bulk Insert specific columns

You need to insert a list of but only insert some specific column. The ColumnInputExpression option let you choose a column to insert.

context.BulkInsert(customers, options => { options.ColumnInputExpression = x => new { x.Code, x.CreatedDate }; });

Try it: NET Core | NET Framework

Documentation

BulkInsert

Methods
NameDescriptionExample
Bulk insert entities in your database.NET Core / NET Framework
Bulk insert entities in your database.NET Core / NET Framework
Bulk insert entities asynchronously in your database.NET Core / NET Framework
Bulk insert entities asynchronously in your database.NET Core / NET Framework
Bulk insert entities asynchronously in your database.NET Core / NET Framework
Bulk insert entities asynchronously in your database.NET Core / NET Framework

Learn more

More documentation can be found here: EF Extensions - Bulk Insert


Sours: https://entityframework-classic.net/bulk-insert
Dapper Free Bulk Insert Example [ASP.NET Core]

EF Core Tools & Extensions

These tools and extensions provide additional functionality for Entity Framework Core 2.1 and later.

Important

Extensions are built by a variety of sources and aren't maintained as part of the Entity Framework Core project. When considering a third party extension, be sure to evaluate its quality, licensing, compatibility, support, etc. to ensure it meets your requirements. In particular, an extension built for an older version of EF Core may need updating before it will work with the latest versions.

Tools

LLBLGen Pro

LLBLGen Pro is an entity modeling solution with support for Entity Framework and Entity Framework Core. It lets you easily define your entity model and map it to your database, using database first or model first, so you can get started writing queries right away. For EF Core: 2, 3, 5.

Website

Devart Entity Developer

Entity Developer is a powerful O/RM designer for ADO.NET Entity Framework, NHibernate, LinqConnect, Telerik Data Access, and LINQ to SQL. It supports designing EF Core models visually, using model first or database first approaches, and C# or Visual Basic code generation. For EF Core: 2, 3, 5.

Website

nHydrate ORM for Entity Framework

An O/RM that creates strongly-typed, extendable classes for Entity Framework. The generated code is Entity Framework Core. There is no difference. This is not a replacement for EF or a custom O/RM. It is a visual, modeling layer that allows a team to manage complex database schemas. It works well with SCM software like Git, allowing multi-user access to your model with minimal conflicts. The installer tracks model changes and creates upgrade scripts. For EF Core: 3.

Github repository

EF Core Power Tools

EF Core Power Tools is a Visual Studio extension that exposes various EF Core design-time tasks in a simple user interface. It includes reverse engineering of DbContext and entity classes from existing databases and SQL Server DACPACs, management of database migrations, and model visualizations. For EF Core: 3, 5, 6.

GitHub wiki

Entity Framework Visual Editor

Entity Framework Visual Editor is a Visual Studio extension that adds an O/RM designer for visual design of EF 6, and EF Core classes. Code is generated using T4 templates so can be customized to suit any needs. It supports inheritance, unidirectional and bidirectional associations, enumerations, and the ability to color-code your classes and add text blocks to explain potentially arcane parts of your design. For EF Core: 2, 3, 5.

Marketplace

CatFactory

CatFactory is a scaffolding engine for .NET Core that can automate the generation of DbContext classes, entities, mapping configurations, and repository classes from a SQL Server database. For EF Core: 2.

GitHub repository

LoreSoft's Entity Framework Core Generator

Entity Framework Core Generator (efg) is a .NET Core CLI tool that can generate EF Core models from an existing database, much like , but it also supports safe code regeneration via region replacement or by parsing mapping files. This tool supports generating view models, validation, and object mapper code. For EF Core: 2.

TutorialDocumentation

Extensions

Microsoft.EntityFrameworkCore.AutoHistory

A plugin library that enables automatically recording the data changes performed by EF Core into a history table. For EF Core: 2, 3, 5.

GitHub repository

EFCoreSecondLevelCacheInterceptor

Second level caching is a query cache. The results of EF commands will be stored in the cache, so that the same EF commands will retrieve their data from the cache rather than executing them against the database again. For EF Core: 3, 5.

GitHub repository

Geco

Geco (Generator Console) is a simple code generator based on a console project, that runs on .NET Core and uses C# interpolated strings for code generation. Geco includes a reverse model generator for EF Core with support for pluralization, singularization, and editable templates. It also provides a seed data script generator, a script runner, and a database cleaner. For EF Core: 2.

GitHub repository

EntityFrameworkCore.Scaffolding.Handlebars

Allows customization of classes reverse engineered from an existing database using the Entity Framework Core toolchain with Handlebars templates. For EF Core: 2, 3, 5.

GitHub repository

NeinLinq.EntityFrameworkCore

NeinLinq extends LINQ providers such as Entity Framework to enable reusing functions, rewriting queries, and building dynamic queries using translatable predicates and selectors. For EF Core: 2, 3, 5.

GitHub repository

Microsoft.EntityFrameworkCore.UnitOfWork

A plugin for Microsoft.EntityFrameworkCore to support repository, unit of work patterns, and multiple databases with distributed transaction supported. For EF Core: 2, 3.

GitHub repository

EFCore.BulkExtensions

EF Core extensions for Bulk operations (Insert, Update, Delete). For EF Core: 2, 3, 5.

GitHub repository

Bricelam.EntityFrameworkCore.Pluralizer

Adds design-time pluralization. For EF Core: 2, 3, 5.

GitHub repository

Toolbelt.EntityFrameworkCore.IndexAttribute

Revival of [Index] attribute (with extension for model building). For EF Core: 2, 3, 5.

GitHub repository

Verify.EntityFramework

Extends Verify to allow snapshot testing with EntityFramework. For EF Core: 3, 5.

GitHub repository

LocalDb

Provides a wrapper around SQL Server Express LocalDB to simplify running tests against Entity Framework. For EF Core: 3, 5.

GitHub repository

EfFluentValidation

Adds FluentValidation support to Entity Framework. For EF Core: 3, 5.

GitHub repository

EFCore.TemporalSupport

An implementation of temporal support. For EF Core: 2.

GitHub repository

EfCoreTemporalTable

Easily perform temporal queries on your favourite database using introduced extension methods: , , , , . For EF Core: 3, 5.

GitHub repository

EntityFrameworkCore.TemporalTables

Extension library for Entity Framework Core which allows developers who use SQL Server to easily use temporal tables. For EF Core: 2, 3, 5.

GitHub repository

EntityFrameworkCore.Cacheable

A high-performance second-level query cache. For EF Core: 2.

GitHub repository

EntityFrameworkCore.NCache

NCache Entity Framework Core Provider is a distributed second level cache provider for caching query results. The distributed architecture of NCache makes it more scalable and highly available. For EF Core 2, 3.

Website

EntityFrameworkCore.Projectables

Flexible projection magic for EF Core. Use properties, methods, and extension methods in your query without client evaluation. For EF Core 3, 5, 6.

GitHub repository

EntityFrameworkCore.Triggered

Triggers for EF Core. Respond to changes in your DbContext before and after they are committed to the database. Triggers are fully asynchronous and support dependency injection, inheritance, cascading and more. For EF Core: 3, 5, 6.

GitHub repository

Entity Framework Plus

Extends your DbContext with features such as: Include Filter, Auditing, Caching, Query Future, Batch Delete, Batch Update, and more. For EF Core: 2, 3, 5.

WebsiteGitHub repository

Entity Framework Extensions

Extends your DbContext with high-performance bulk operations: BulkSaveChanges, BulkInsert, BulkUpdate, BulkDelete, BulkMerge, and more. For EF Core: 2, 3, 5.

Website

Expressionify

Add support for calling extension methods in LINQ lambdas. For EF Core: 3, 5.

GitHub repository

ELinq

Language Integrated Query (LINQ) technology for relational databases. It allows you to use C# to write strongly typed queries. For EF Core: 3.

  • Full C# support for query creation: multiple statements inside lambda, variables, functions, etc.
  • No semantic gap with SQL. ELinq declares SQL statements (like , , ) as first class C# methods, combining familiar syntax with intellisense, type safety and refactoring.

As a result SQL becomes just "another" class library exposing its API locally, literally "Language Integrated SQL".

Website

Ramses

Lifecycle hooks (for SaveChanges). For EF Core: 2, 3.

GitHub repository

EFCore.NamingConventions

This will automatically make all your table and column names have snake_case, all UPPER or all lower case naming. For EF Core: 3, 5.

GitHub repository

SimplerSoftware.EntityFrameworkCore.SqlServer.NodaTime

Adds native support to EntityFrameworkCore for SQL Server for the NodaTime types. For EF Core: 3, 5.

GitHub repository

Dabble.EntityFrameworkCore.Temporal.Query

LINQ extensions to Entity Framework Core 3.1 to support Microsoft SQL Server Temporal Table Querying. For EF Core: 3.

GitHub repository

EntityFrameworkCore.SqlServer.HierarchyId

Adds hierarchyid support to the SQL Server EF Core provider. For EF Core: 3, 5.

GitHub repository

linq2db.EntityFrameworkCore

Alternative translator of LINQ queries to SQL expressions. For EF Core: 2, 3, 5, 6.

Includes support for advanced SQL features such as CTEs, bulk copy, table hints, windowed functions, temporary tables, and database-side create/update/delete operations.

GitHub repository

EFCore.SoftDelete

An implementation for soft deleting entities. For EF Core: 3.

NuGet

EntityFrameworkCore.ConfigurationManager

Extends EF Core to resolve connection strings from App.config. For EF Core: 3.

GitHub repository

Detached Mapper

A DTO-Entity mapper with composition/aggregation handling (similar to GraphDiff). For EF Core: 3, 5.

NuGet

EntityFrameworkCore.Sqlite.NodaTime

Adds support for NodaTime types when using SQLite. For EF Core: 5.

GitHub repository

ErikEJ.EntityFrameworkCore.SqlServer.Dacpac

Enables reverse engineering an EF Core model from a SQL Server data-tier application package (.dacpac). For EF Core: 3, 5.

GitHub wiki

ErikEJ.EntityFrameworkCore.DgmlBuilder

Generate DGML (Graph) content that visualizes your DbContext. Adds the AsDgml() extension method to the DbContext class. For EF Core: 3, 5.

GitHub wiki

ErikEJ.EntityFrameworkCore.SqlServer.SqlQuery

Provides the and methods to help you populate arbitrary classes or a list of primitive types from a raw SQL query. For EF Core: 5.

GitHub repository

EntityFramework.Exceptions

When using Entity Framework Core all database exceptions are wrapped in DbUpdateException. EntityFramework.Exceptions handles all the database-specific details to find which constraint was violated and allows you to use typed exceptions such as , , , , when your query violates database constraints.

Supports SQL Server, Postgres, MySql, SQLite and Oracle

For EF Core: 3, 5.

GitHub Repository

EFCoreAuditing

A Library for Entity Framework Core to support automatically recording data changes history (audit logging), soft-delete, and snake_case naming convention functionality. For EF Core: 2.

GitHub Repository

EntityFrameworkCore.FSharp

Adds F# design-time support to EF Core. For EF Core: 5.

GitHub repository

EntityFrameworkCore.VisualBasic

Adds VB design-time support to EF Core. For EF Core: 5.

GitHub repository

Krzysztofz01.EFCore.QueryFilterBuilder

Extension for Entity Framework that allows you to create and manage multiple query filters. For EF Core: 5.

GitHub repository

Sours: https://docs.microsoft.com/en-us/ef/core/extensions/

Bulk core entity insert framework

Fastest Way of Inserting in Entity Framework

To your remark in the comments to your question:

"...SavingChanges (for each record)..."

That's the worst thing you can do! Calling for each record slows bulk inserts extremely down. I would do a few simple tests which will very likely improve the performance:

  • Call once after ALL records.
  • Call after for example 100 records.
  • Call 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:

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 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, doesn't do that, the entities are still attached to the context in state . 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.

Sours: https://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework
CRUD using Basic BULK Operations in ASP.NET Core - Process 01

Entity Framework Extensions Bulk Insert

Description

The EF extension method let you insert a large number of entities in your database.

context.BulkInsert(customers); context.BulkInsert(invoices, options => options.IncludeGraph = true);

Try it in EF6 | Try it in EF Core

Performance Comparison

Operations1,000 Entities2,000 Entities5,000 Entities
SaveChanges1,200 ms2,400 ms6,000 ms
BulkInsert50 ms55 ms75 ms

Try it in EF6 | Try it in EF Core

HINT:

A lot of factors might affect the benchmark time such as index, column type, latency, throttling, etc.

Scenarios

The method is fast but also flexible to let you handle various scenarios in Entity Framework such as:

What is supported?

  • All Entity Framework versions (EF4, EF5, EF6, EF Core, EF Classic)
  • All Inheritances (TPC, TPH, TPT)
  • Complex Type/Owned Entity Type
  • Enum
  • Value Converter (EF Core)
  • And more!

Advantages

  • Easy to use
  • Flexible
  • Increase performance
  • Increase application responsiveness
  • Reduce database load
  • Reduce database round-trips

Getting Started

Bulk Insert

The and methods extend your to let you insert a large number of entities in your database.

context.BulkInsert(customers); context.BulkInsertAsync(customers, cancellationToken);

Try it in EF6 | Try it in EF Core

Bulk Insert with options

The parameter let you use a lambda expression to customize the way entities are inserted.

context.BulkInsert(customers, options => options.BatchSize = 100); context.BulkInsert(customers, options => { options.InsertIfNotExists = true; options.PrimaryKeyExpression = customer => customer.Code; });

Try it in EF6 | Try it in EF Core

Why BulkInsert is faster than SaveChanges?

Inserting thousands of entities for an initial load or a file importation is a typical scenario.

The method makes it quite slow/impossible to handle this kind of situation due to the number of database round-trips required. The perform one database round-trip for every entity to insert. So, if you need to insert 10,000 entities, 10,000 database round-trips will be performed which is INSANELY slow.

The in counterpart requires the minimum number of database round-trips possible. For example, under the hood for SQL Server, a is performed to insert 10,000 entities which is the fastest way available.

Real Life Scenarios

Insert and keep identity value

Your entity has an identity property, but you want to force to insert a specific value instead. The option allows you to keep the identity value of your entity.

context.BulkInsert(customers, options => options.InsertKeepIdentity = true);

Try it in EF6 | Try it in EF Core

Insert and include/exclude properties

You want to insert your entities but only for specific properties.

  • : This option let you choose which properties to map.
  • : This option let you ignore properties that are auto-mapped.
context.BulkInsert(customers, options => options.ColumnInputExpression = c => new { c.CustomerID, c.Name} ); context.BulkInsert(customers, options => options.IgnoreOnInsertExpression = c => new { c.ColumnToIgnore } );

Try it in EF6 | Try it in EF Core

Insert only if the entity does not already exists

You want to insert entities but only those that don't already exist in the database.

  • : This option let you insert only entities that doesn't already exists.
  • : This option let you customize the key to use to check if the entity already exists or not.
context.BulkInsert(customers, options => { options.InsertIfNotExists = true; options.ColumnPrimaryKeyExpression = c => c.Code; });

Try it in EF6 | Try it in EF Core

Insert with related child entities (Include Graph)

You want to insert entities but also automatically insert related child entities.

  • : This option lets you to automatically insert all entities part of the graph.
  • : This option lets you customize how to insert entities for a specific type.
context.BulkInsert(invoices, options => options.IncludeGraph = true);

Try it in EF6 | Try it in EF Core

Insert with future action

You want to insert entities, but you want to defer the execution.

By default, is an immediate operation. That means, it's executed as soon as you call the method.

: This option let you defer the execution of a Bulk Insert. : This option trigger and execute all pending .

context.FutureAction(x => x.BulkInsert(customers)); context.FutureAction(x => x.BulkInsert(invoices, options => options.IncludeGraph = true)); context.ExecuteFutureAction();

Try it in EF6 | Try it in EF Core

Insert without returning identity value

By default, the EF method already returns the identity when inserting.

However, such behavior impacts performance. For example, when the identity must be returned, a temporary table is created in SQL Server instead of directly using into the destination table.

You can improve your performance by turning off the option.

context.BulkInsert(customers, options => options.AutoMapOutputDirection = false);

Try it | Try it in EF Core

More scenarios

Hundreds of scenarios have been solved and are now supported.

The best way to ask for a special request or to find out if a solution for your scenario already exists is by contacting us: [email protected]

Documentation

BulkInsert

Methods
NameDescriptionExample
Bulk insert entities in your database.EF6 / EFCore
Bulk insert entities in your database.EF6 / EFCore
Bulk insert entities asynchronously in your database.EF6 / EFCore
Bulk insert entities asynchronously in your database.EF6 / EFCore
Bulk insert entities asynchronously in your database.EF6 / EFCore
Bulk insert entities asynchronously in your database.EF6 / EFCore
Options

More options can be found here:

Limitations

Hidden Navigation

The doesn't use the ChangeTracker to optimize the performance unless no other alternative exists.

For example, you want to insert but there is no relation toward the parent . In this case, you will need to add your entities in the . The will be used to find the related for your .

try { context.BulkInsert(items); } catch { Console.WriteLine("An error is throw because the Invoice relation cannot be found."); } context.Invoices.AddRange(invoices); context.BulkInsert(items);

Try it in EF6 | Try it in EF Core


Sours: https://entityframework-extensions.net/bulk-insert

Now discussing:

Entity Framework Core 3.0 Bulk Insert Update and Delete – Asp.Net Core 3.0

In this tutorial, we are going to cover Entity Framework Core (EF Core) bulk insert, update and delete options using Asp.Net Core 3.0 in Visual Studio 2019. We will use entity framework core Bulk extension to insert, update and delete multiple records. There are some other solutions to insert, update and delete multiple records in the fastest way, but using Bulk extension is the most significant way to insert, update, and delete multiple records. 

So, first-of-all, we will create a new project using Asp.Net Core 3.0 and Visual studio 2019 and then we will add a new Model class and then create a new database. Then we will install the entity framework core bulk extension and then we will see how to use bulk extension to insert, update and delete multiple records.

Entity Framework Core 3.0 Bulk insert, update and delete using Asp.Net Core 3.0

Let’s see how to use bulk insert, update and delete using entity framework core 3.0 in step by step.

Step # 1: Create a new project using Visual Studio 2019

So, first-of-all, in this step we will create a new Asp.Net Core 3.0 project using visual studio 2019. So, open visual studio 2019 and then “Create a new project” and then choose “ASP.NET Core Web Application” and then enter project name and then click on the “Create” button. Then choose template “Web Application (Model-View-Controller)” and then change authentication from “No Authentication” to “Individual User Account” and then click on the “Create” button.

Step # 2: Create a new Model class

Now, in this step, we will create a new model class and then create a new database. So, right click on the “Models” folder and then add new class with the name of “Employee” as you do see in the below file.

Now, go to ApplicationDbContext class and then enter the DbSet as you do see below in the file line # 17.

Now, go to project manager console and then run these below two commands.

=>

=>

Step # 3: Install Entity Framework Core Bulk extension

Now, in this step, we will install entity framework core Bulk extension. So, go to nuget package manager and then install the EFCore.BulkExtensions as you do see below in the screenshot.

Step # 4: How to use Bulk extension in Entity Framework Core 3.0 using Asp.Net Core 3.0

In this step, we will see how to use Bulk extension to insert, update and delete multiple records in entity framework core 3.0.

How to insert multiple records using linq entity framework core bulk insert.

Now, in this step, we will see linq entity framework insert multiple records using BulkInsert() option. So, go to Controllers folder and then Home Controller and then add the code as you do see in the below file. 

Let’s understand the above code.

Line # 27: In this line, we are calling the Insert Record function. And this InsertRecord() function will return the list of employees with one hundred thousand rows and then assign to employee list.

Line # 28: Now, in this line we are using BulkInsert() to insert the many records at a time. This is the same as the SaveChanges() function. But it is more significant and fast than SaveChanges() function.

Line # 30 to 43: this block of code inserting many records into a list and then returning a list.

Now, run your application and this Index() function will be called automatically. So, once the application compiled successfully, then go to the database and then you will see the one hundred thousand employees’ record is inserted in the employee table.

Execution time difference between BulkInsert() and SaveChanges()

The execution time of one hundred thousand records using BulkInsert() function is 11250 milliseconds and the execution time of the same record using SaveChanges() is 331675 milliseconds

How to update multiple records using entity framework core bulk update

Now, in this step, we will see how to insert multiple records using entity framework core bulk update option. So, write the code as you do see in the below file.

Let’s understand the above code.

Line # 30: In this line, we are calling the UpdateRecord() function. And this UpdateRecord() function will return the list of employees with their ids and then assign to employee list. 

Line # 31: Now, in this line we are using BulkUpdate() to update the many reocrds at a time.

Line # 35 to 51: this block of code will return the list of employees with their ids.

Execution time difference between BulkUpdate() and SaveChanges()

The execution time of one hundred thousand records using BulkUpdate() function is 11239 milliseconds and the execution time of the same record using SaveChanges() is 347288 milliseconds

How to delete multiple records using entity framework core bulk delete

Now, in this step, we will see how to delete multiple records using entity framework core bulk delete option. So, write the code as you do see in the below file.

Let’s understand the above file code.

Line # 27: In this line, we are getting first 50000 records from employee table and then assigning to employee list.

Line # 28: this line of code will delete the record using BulkDelete().

Execution time difference between BulkDelete() and SaveChanges()

The execution time of one fifty thousand record using BulkDelete() is 17293 milliseconds and the execution time of the same record using SaveChanges() is 151501 milliseconds

Thank you for reading. Please keep visiting and sharing within your community!

Filed Under: ASP.NET Core, Entity FrameworkTagged With: Asp.Net Core 3.0, Bulk Extensions, EF Core, EF Core 3, Entity Framework Core

Sours: https://dotnetdetail.net/entity-framework-core-3-0-bulk-insert-update-and-delete/


5421 5422 5423 5424 5425