To increase a configurability of dotConnect for PostgreSQL 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 CURRENT_TIMESTAMP or my_sequence.nextval.
Here is a simple example:
<Property Name="ProductID" Type="int" Nullable="false" devart:DefaultValue="my_sequence.nextval" StoreGeneratedPattern="Identity" />
The generated SQL command looks like the following:
INSERT INTO "Product" ("ProductID","ProductName","UnitScale","InStock","Price","DiscontinuedPrice") VALUES(my_sequence.nextval,: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:
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.PostgreSql.Entity tag.
The example code that enables the ReuseParameters option: