Set Identity and Computed Properties in Entity Framework Without Triggers

This article deals with the following:

• StoreGeneratedPattern
• Identity
• Computed
• DefaultValue
• InsertNullBehaviour
• Example
• Conclusion

StoreGeneratedPattern

Entity Framework contains an excellent functionality of server-generated columns of the entity that is intended for defining the primary key values on the database side, concurrency check and so on. This is done by setting StoreGeneratedPattern of columns of the EF-model storage part (SSDL) to either Identity or Computed.

Identity

In a number of databases (Microsoft SQL Server, MySQL, PostgreSQL, SQLite), StoreGeneratedPattern.Identity of the columns’ primary key is defined by the EF-designer when a model is created against the database and if these columns are made auto-increment in the database. Oracle does not have any standard way of making a column auto-increment, thus, in the database, the users have to create a sequence and the BEFORE INSERT trigger that defines the value of an auto-increment column based on the sequence; when the model is created, the users have to set the value of StoreGeneratedPattern manually in the model editor.

Computed

In most cases, supporting this functionality involves writing UPDATE-triggers in the database, which takes time and requires knowledge of a specific programming language other than SQL, for example, PL/SQL in Oracle.

DefaultValue

To facilitate and speed up the process of development as well as to increase its flexibility, we have provided the users of our EF-providers for Oracle, MySQL, PostgreSQL and SQLite with a capability that, in simple cases, obviates the need to write INSERT and UPDATE triggers and to set a default value for columns in the database. This can also be useful when you need, if possible, to use existing objects rather than modify the database. Now, you can assign values to columns by specifying database functions in the column’s DefaultValue attribute in the storage part of the EF model (SSDL).

If you use Entity Data Model Designer, you will need to edit the model’s XML using an XML editor. Bear in mind that the DefaultValue standard attribute is checked by EF-validation and that is why it cannot be used to call a DB-function. For that reason, we have introduced the new custom devart:DefaultValue SSDL attribute (if you need to edit manually the EDMX file, do not forget to add the attribute xmlns:devart=”http://devart.com/schemas/edml/StorageSchemaExtensions/1.0″ to the tag Schema in SSDL). The devart:DefaultValue 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.

If you use Devart Entity Developer, you will be able to set the value for DefaultValue in the designer, and it will store this value correctly either in the DefaultValue attribute or in devart:DefaultValue , as required.

InsertNullBehaviour

To use this functionality, you need to configure the EF-provider. For more information on the specifics of configuration, see “New Features of Entity Framework Support in dotConnect Providers”; for the purpose of the present article we shall review a small example that shows how to customize the DefaultValue processing behavior. In this example, we deal with the dotConnect for Oracle provider, but the procedure is identical for MySQL, PostgreSQL, and SQLite.

  var config = OracleEntityProviderConfig.Instance;

  config.DmlOptions.InsertNullBehaviour = InsertNullBehaviour.InsertDefaultOrNull;
  // or
  config.DmlOptions.InsertNullBehaviour = InsertNullBehaviour.InsertDefaultOrOmit;

New features are configured in the <ProviderName>EntityProviderConfig class. This class is available in the Devart.Data.<ProviderName>.Entity assembly. Don’t forget to add this Devart.Data.<ProviderName>.Entity.dll assembly as Reference for the corresponding Entity Framework version (support for the new functionality is available for EF v1 and v4.x).
For more information on capabilities of InsertNullBehaviour, see here.

Example

As an example, we shall use a table in Oracle, in which we want to define the behavior of the store generated columns in the model rather than create triggers. For the purpose of this example, we also suppose that we already have a sequence (MY_SEQUENCE) and a database function (MY_FUNCTION) that we can now use. Let’s demonstrate all the capabilities within a single example.
Below is the script for creating the table:

CREATE TABLE "Products" (
  ID NUMBER(9) PRIMARY KEY,
  "ProductName" VARCHAR2(160) NOT NULL,
  "Price" NUMBER(10, 2),
  "FunctionGeneratedValue" NUMBER NOT NULL,
  "ModifiedBy" VARCHAR2(30) NOT NULL,
  "LastModified" TIMESTAMP NOT NULL
)

XML-representation of the entity in the storage model (SSDL):

  <EntityType Name="Products">
    <Key>
      <PropertyRef Name="ID" />
    </Key>
    <Property Name="ID" Type="int" Nullable="false" />
    <Property Name="ProductName" Type="VARCHAR2" Nullable="false" MaxLength="160" />
    <Property Name="Price" Type="double" />
    <Property Name="FunctionGeneratedValue" Type="decimal" Nullable="false" />
    <Property Name="ModifiedBy" Type="VARCHAR2" Nullable="false" MaxLength="30" />
    <Property Name="LastModified" Type="TIMESTAMP" Nullable="false" />
  </EntityType>

Now we shall modify mapping by defining the following rules:

• The ID column, which is the primary key, will be defined through the value of the sequence MY_SEQUENCE on INSERT.
• The “FunctionGeneratedValue” column will be defined through the call of the user-defined function MY_FUNCTION() on INSERT.
• The “ModifiedBy” will be defined through the system database function USER on INSERT and UPDATE.
• The “LastModified” column will be defined through the system database function CURRENT_TIMESTAMP on INSERT and UPDATE. This column will be used for concurrency check, so we need to set ConcurrencyMode=”Fixed” for this column in the conceptual model.

XML SSDL after modification is shown below:

  <EntityType Name="Products">
    <Key>
      <PropertyRef Name="ID" />
    </Key>
    <Property Name="ID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" devart:DefaultValue="MY_SEQUENCE.NEXTVAL" />
    <Property Name="ProductName" Type="VARCHAR2" Nullable="false" MaxLength="50" />
    <Property Name="Price" Type="double" />
    <Property Name="FunctionGeneratedValue" Type="decimal" Nullable="false" StoreGeneratedPattern="Identity" devart:DefaultValue="MY_FUNCTION()" />
    <Property Name="ModifiedBy" Type="VARCHAR2" Nullable="false" MaxLength="30" StoreGeneratedPattern="Computed" DefaultValue="USER" />
    <Property Name="LastModified" Type="TIMESTAMP" Nullable="false" StoreGeneratedPattern="Computed" devart:DefaultValue="CURRENT_TIMESTAMP" />
  </EntityType>

Below shown is an example of C# code:

 
  // Here we turn on the monitoring of interaction with the database through  dbMonitor
  var mon = new Devart.Data.Oracle.OracleMonitor { IsActive = true };

  // Here we configure the EF-provider, so that the default values defined in the model are used 
  var config = OracleEntityProviderConfig.Instance;
  config.DmlOptions.InsertNullBehaviour = InsertNullBehaviour.InsertDefaultOrNull; // InsertNullBehaviour.InsertDefaultOrOmit;

  // Here we create the context and perform insert and update of the entity 
  using (var ctx = new Entities()) {

    Products product = new Products() {
      ProductName = "Gadget",
      Price = 220.25
    };
    ctx.Products.AddObject(product);
    ctx.SaveChanges(); // INSERT

    product.Price = 199.99;
    ctx.SaveChanges(); // UPDATE
  }

To monitor queries sent to the server, we recommend that you use Devart dbMonitor, a free tool, that is compatible with Devart EF-providers. However, the use of this monitor is recommended only on the stage of development and debugging of the application, since its use causes a drop in performance.
Shown below is SQL generated on INSERT:

 
DECLARE
  updatedRowid ROWID;
BEGIN
INSERT INTO "Products"(ID, "FunctionGeneratedValue", "ModifiedBy", "LastModified", "ProductName", "Price")
VALUES (MY_SEQUENCE.NEXTVAL, MY_FUNCTION(), USER, CURRENT_TIMESTAMP, :p0, :p1)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT ID, "FunctionGeneratedValue", "ModifiedBy", "LastModified" FROM "Products" WHERE ROWID = updatedRowid;
END;

Shown below is SQL generated on UPDATE:

 
DECLARE
  updatedRowid ROWID;
BEGIN
UPDATE "Products"
   SET "ModifiedBy" = USER, "LastModified" = CURRENT_TIMESTAMP, "Price" = :p0
 WHERE ID = :p1 AND "LastModified" = :p2
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT "ModifiedBy", "LastModified" FROM "Products" WHERE ROWID = updatedRowid;
END;

Conclusion

As we have seen, this functionality is extremely simple to use. If you, as yet, do not use Entity Developer, whose EF-version is shipped with our EF-providers, consider doing so, since it allows modifying the storage model much more easily and more conveniently than a standard EDM designer does and hence is a more suitable tool for this purpose. Nor should you forget to monitor SQL being sent on the development stage, so that you can be sure that everything works as intended.