By default Entity Framework updates data one statement at a time. dotConnect for MySQL allows grouping several INSERT/UPDATE/DELETE statements into one SQL block and merging their parameters. Now, instead of sending each DML statement separately, SaveChanges generates and executes statement batches. It increases SaveChanges performance substantially because of reducing number of server calls. This feature is called Batch Updates.

Note that in situations when other applications significantly load the database server, you will get even more performance gain with batch updates, because they reduce the number of database server calls.

Batch Updates Settings

Batch Updates are configured with a number of parameters either in code with the MySqlEntityProviderConfig class or in the project config file as the attributes of the BatchUpdates element of the DmlOptions tag.

• Enabled - enables batch updates. If set to false (the default value), other Batch Updates settings are ignored.
• BatchSize - specifies the maximal number of commands in a batch. Optimal batch size is different for each specific case and DBMS, however, the default value 30 provides good enough results in most cases. If this property is set to 0, all commands will be encapsulated in one batch. In this case, the performance goes down, and DBMS will probably fail with an error because of the too large command size.

• AsynchronousBatch - determines whether batches are executed asynchronously (false by default), or a new batch will be created and executed only after the successful execution of the previous batch. It is recommended to enable asynchronous batch execution when the batch size is large. If the batch size is small, the performance gain is compensated by synchronization costs. However, if the batch size is too large, the performance gain will not be significant. To conclude: BatchSize should be large enough to take some time to execute, and small enough not to make the main thread wait for the background thread for too long.

  Activating asynchronous batch execution makes sense only if your application will be run on multiple-core processors. On single-core processors asynchronous batch execution provides even less performance than synchronous one.

  This property affects only the provider for Entity Framework v1 - v6. It does not affect the Entity Framework Core provider.

• ConcurrencyCheck - enables concurrency check. By default, concurrency check is disabled when using batch updates. Then the concurrency is checked by comparing the number of affected rows to the batch size. It should be remembered that concurrency check in this case is performed for batches, not for individual rows. One command can influence 0 entries, and another can influence 2 entries, this situation will not be treated as an exception if these commands are in one batch. Of course, such situations are very uncommon.

  When concurrency check is enabled, the StateEntries property of the generated OptimisticConcurrencyException, if any, is not initialized because batch updates are managed by provider at ADO.NET level, and the provider does not have the Entity Framework level information about states of entities in the context's collection.

For example, this code is used to enable batch updates:

MySqlEntityProviderConfig config = MySqlEntityProviderConfig.Instance;  
config.DmlOptions.BatchUpdates.Enabled = true;  

Dim Config As MySqlEntityProviderConfig
Config = MySqlEntityProviderConfig.Instance
Config.DmlOptions.BatchUpdates.Enabled = True

Batch Updates Limitations

This section describes Batch Updates limitations in Entity Framework v1 - v6. It is not applicable to Entity Framework Core.

TransactionScope cannot be used with batch updates. When using batch updates, concurrency check is not performed. However, you may still enable concurrency check as it is described above.

Some statements cannot be united into batches. When such a statement is encountered, the batch with previously added commands is executed immediately, then this statement is executed separately.

The following statements cannot be united into batches:

• Statements with the RETURNING clause, for example, statements that update or insert entities with columns having the StoreGeneratedPattern attribute value set to Identity or Computed.
• Calls of stored procedures or functions that have non-empty CommandText.

It is not recommended to create transactions manually when committing changes. If you create such a transaction (for example, execute context.Database.BeginTransaction() prior to SaveChanges() ), the behavior will be the following:

SaveChanges() will perform the number of database calls (UPDATE/INSERT/DELETE or stored procedure calls) that is multiple of the BatchSize property value. Remainder changes will stay cached, and they will be sent to the database in the following cases:

• When committing a transaction.
• When calling SaveChanges() again, if there is enough database call to be more or equal to BatchSize together with the remainder changes.
• When performing a SELECT operation.

Enabling Batch Updates in Entity Framework Core

In Entity Framework Core, you can turn Batch Updates explicitly either per context or globally.

Per context:

optionbuilder.UseMySql(connString , b => b.MaxBatchSize(100)); 

optionbuilder.UseMySql(connString, Function(b) b.MaxBatchSize(100))

Globally, for all DbContext instances:

var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
config.DmlOptions.BatchUpdates.Enabled = true;
config.DmlOptions.BatchUpdates.BatchSize = 100;

Dim config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance
config.DmlOptions.BatchUpdates.Enabled = True
config.DmlOptions.BatchUpdates.BatchSize = 100