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:
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)
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:
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:
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: