dotConnect for MySQL Documentation
In This Topic
    Batch Updates
    In This Topic

    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.

    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:

    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:

    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