DML Options

In This Topic

To increase a configurability of dotConnect for MySQL as Entity Framework provider and provide more flexibility in behavior, we have added a number of DML options that influence the INSERT/UPDATE/DELETE commands and stored procedure calls.

Currently this feature is not yet supported for Entity Framework Core. It is supported only for Entity Framework v1 - v6.

Except for the batch updates settings, which are described in the Batch Updates topic, the list of DML options includes:

ReuseParameters - enables reusing existing parameters instead of creating new ones. For example, if the batch contains 30 INSERT statements, inserting the number 1024, one :p1 parameter will be used instead of creating 30 :p1 - :p30 parameters. Set this property to true only if a large number of the same values is sent to the database. Otherwise, it may even cause performance loss, if there are many parameters in a batch.
ParametersAsLiterals - determines if parameter values are inserted into SQL statements as literals instead of parameters. This behaviour is implemented in Connector .NET and can improve performance a bit in some rare cases.
InsertNullBehaviour. NULL values can be inserted in different ways. This configuration property allows the developer to determine the behaviour suitable for his particular application. Here is the list of possible alternatives:
- InsertNull. In this case NULLs are inserted explicitly (for each column that do not have a non-NULL value specified) like in the following example:
```
INSERT INTO "Company"
  ("CompanyID","CompanyName","Web","Email","Address_AddressTitle","Address_Address",
  "Address_City","Address_Region","Address_PostalCode","Address_Country",
  "Address_Phone","Address_Fax","PrimaryContact_ContactID","PersonContact_ContactID")
VALUES
  (:p0,:p1,:p2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
```
- Omit. In this case provider simply omits all columns that do not have non-NULL values like in the following example:
```
INSERT INTO "Company"("CompanyID","CompanyName","Web")VALUES(:p0,:p1,:p2)
```
- InsertDefaultOrNull. In this case provider determines if there is a default value specified in the SSDL part of the model, and if it is available, this value is persisted to the database. If there is no default value specified, the behaviour is identical to the InsertNull one.
- InsertDefaultOrOmit. In this case provider determines if there is a default value specified in the SSDL part of the model as well. However, if there is no default value specified for a column, it is omitted from the INSERT command completely.
Please note that the last two options work with both common DefaultValue SSDL attribute and the new custom devart:DefaultValue SSDL attribute. The latter attribute does not trigger the type consistency check, so you can use a wider range of default values, like NOW().

Here is a simple example:
```
<Property Name="CreatedDate" Type:TIMESTAMP Nullable="false" 
devart:DefaultValue="NOW()" StoreGeneratedPattern="Identity" />
```
The generated SQL command looks like the following:
```
INSERT INTO "Product"
  ("CreatedDate","ProductName","UnitScale","InStock","Price","DiscontinuedPrice")
  VALUES(NOW(),:p0,:p1,:p2,:p3,:p4)
```
If the InsertNullBehaviour is set to InsertNullBehaviour.InsertDefaultOrNull or InsertNullBehaviour.InsertDefaultOrOmit, and the column has DefaultValue, then the following rules are applied when executing INSERT:
- For Primary Key columns, DefaultValue is always inserted.
- For a column with StoreGeneratedPattern, DefaultValue is always inserted too.
- For a nullable column without StoreGeneratedPattern, if the value is not NULL, then this value is inserted, otherwise, the DefaultValue is inserted.
- For a not null column without StoreGeneratedPattern of .NET reference type (string, byte[]), if the value is not NULL, then this value is inserted, otherwise, the DefaultValue is inserted.
- For a not null column of value type (Int32, DateTime, etc) without StoreGeneratedPattern, the value cannot be NULL, so this value is inserted.
EmptyUpdates. When set to false, the provider turns off the generation of fake updates for parent entities. The default value is true.
LastInsertGuidFunction. Specifies the name of a session variable or function to use instead of the LAST_INSERT_ID() function for Guid Identity properties.

MySQL does not offer any native function to retrieve the value of the last generated UUID. If you use a class with a UUID entity key, generated on the database side (for example, in a trigger), dotConnect for MySQL is not able to retrieve identity values of the newly created objects of this class, inserted to the database.

dotConnect for MySQL offers the LastInsertGuidFunction property as a workaround for such case. You may either create a function, returning the last generated UUID, or a session variable, storing this value, and assign its name to this property. dotConnect for MySQL will use this function or variable to retrieve the UUID values for the newly inserted records. However, you must care about returning correct values on the database side. If you use a function, you must implement it yourself; if you use a variable, you must update its value yourself (for example, in the trigger, which generates these UUID values for new records).

Here is an example of such case. If you use the following trigger to assign the generated UUID primary key values on the database side:
```
CREATE TRIGGER testtrigger
    BEFORE INSERT
    ON test
    FOR EACH ROW
BEGIN
  IF NEW.`ID` = '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'
  THEN
        SET NEW.`ID` = ordered_uuid(UUID());
  END IF;
END
$$
```
Modify it in the following way:
```
CREATE DEFINER = 'root'@'%'
TRIGGER testtrigger
    BEFORE INSERT
    ON test
    FOR EACH ROW
BEGIN
  IF NEW.`ID` = '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'
  THEN
        SET @last_insert_uuid=ordered_uuid(UUID());
        SET NEW.`ID` = @last_insert_uuid;
  END IF;
END
$$
```
where @last_insert_uuid is a session variable we use for storing the last generated UUID value. Then assign it to this configuratin property of our Entity Framework provider:
- C#
```
MySqlEntityProviderConfig config = MySqlEntityProviderConfig.Instance;  
config.DmlOptions.LastInsertGuidFunction = "@last_insert_uuid";
```
- Visual Basic
```
Dim Config As MySqlEntityProviderConfig
Config = MySqlEntityProviderConfig.Instance
Config.DmlOptions.LastInsertGuidFunction = "@last_insert_uuid"
```

These options can be set either in code as the properties of the EntityProviderConfig.DmlOptions object or in the project config file as the attributes of the DmlOptions element of the Devart.Data.MySql.Entity tag.

The example code that enables the ReuseParameters option:

[email protected]