Devart Blog

Working with Large Models and Oracle Autoincrement Fields in New Version of Entity Developer

Posted by on March 24th, 2011

Starting from version 3.20, Entity Developer provides you with additional features to simplify working with models. Now you can split your model into diagrams to improve its readability. Entity Developer automatically generates a sequence and a trigger for autoincrement fields in Oracle when generating the database script from the model or when synchronizing the database with a model. We constantly work on improving Entity Developer and now we are focusing on performance increase and usability enhancement. The following enhancements have been added in the latest version:


Large Model Splitting

In the case of large models that contain many entities, the diagram may become too complex to understand and edit. To make your model better understandable, the new version of Entity Developer allows splitting the model into several thematic diagrams, each one having a subset of model entities. For example, all entities, related to sales, may be placed on one diagram, while production entities can be placed on another. You can divide your model by schemas or by logical purpose.

For example, we have an AdventureWorks database that consists of the following schemas: dbo, Human Resources, Person, Production, Purchasing, and Sales.

As we can see, this database consists of more than sixty tables that are divided between six schemas. If all entities of this complex model are placed onto one diagram, the diagram becomes too difficult to read and work with. So, let’s split model into the several diagrams, each one having its share of model entities.

You can split your model using Create Model Wizard on the model creation stage. On the Choose Model Diagram Contents page, choose how the model is to be split.

Wizard Window for Splitting Selection

The entities of our model are grouped by the schemas on the database server and for each group a separate diagram is created.

Diagrams

You can use the Diagrams node of Model Explorer for diagram management. It allows you to:

  • Rename a diagram or delete it from the model.
  • Create a new diagram with any entities, when it is necessary.
  • Select the Default diagram that will be displayed when the model is opened.

The Entity Developer tool provides additional features to manage entities. You can perform a fast searching for the selected model class on the diagrams. When you delete an entity from a diagram, it is possible to choose whether to delete the entity only from the diagram or delete it from the entire model.

Generating Sequences and Triggers for Autoincrement Fields in Oracle

If you want each table row to be identified with a unique number, you can do this easily in SQL Server by specifying “Identity” for your number fields or using serial data type in PostgreSQL. In Oracle, however, this operation is a little more complicated. One way to do that is to create two database objects – a SEQUENCE and a TRIGGER.

The new version New Entity Developer automatically generates a sequence and a trigger for autoincrement fields in Oracle when generating the database script from the model or when synchronizing the database with the model.

Peculiarities of Working with LinqConnect Model

Let’s create a LinqConnect model that contains the Dept entity using the Model-First approach. The Dept entity contains the following properties:

  • Dept_No – primary key, unique identifier. Represents a department number.
  • Dept_Name – Represents a department name.
  • Location – Represents a department location.

The Dept_No property is a primary key, so let’s define an autoincrement logic for it. To do that, we have to set the Auto Generated Value property in the Dept_No column to True.

Run the Update Database From Model wizard to create a DDL script. This wizard generates and runs the script against the specified database. The corresponding SQL code for the table, sequence, and trigger creation is listed below:

-- Script was generated by Devart Entity Developer, Version 3.20.74.0
-- Script date 2/9/2011 5:22:48 PM
-- Target Server: Oracle
-- Server Version: 10gR2

-- 
-- Creating a table CRM_DEMO."Depts" 
-- 
CREATE TABLE CRM_DEMO."Depts" (
   "Dept_No" NUMBER(9) NOT NULL,
   "Dept_Name" CLOB NOT NULL,
   "Location" CLOB NOT NULL,
   CONSTRAINT "PK_Depts" PRIMARY KEY ("Dept_No")
);

-- 
-- Creating a sequence CRM_DEMO."AI$Depts_Dept_No_seq" 
-- 
CREATE SEQUENCE CRM_DEMO."AI$Depts_Dept_No_seq" START WITH 1 INCREMENT BY 1;

-- 
-- Creating a trigger CRM_DEMO."AI$Depts" 
-- 
CREATE OR REPLACE TRIGGER CRM_DEMO."AI$Depts"
   BEFORE INSERT ON CRM_DEMO."Depts"
   FOR EACH ROW
   BEGIN
      SELECT CRM_DEMO."AI$Depts_Dept_No_seq".NEXTVAL INTO :NEW."Dept_No" FROM DUAL;
   END;

The table, the sequence, and the trigger are created on the server after the synchronization between the database and the model is finished.

Peculiarities of Working with Entity Framework Model

In Entity Framework, autoincrement field definition is a bit different. Besides, please check if the Automatic Synchronization Storage Part and Mapping of the Model with its Conceptual Part option is turned on, because database update scripts are generated using the Storage part. When automatic synchronization is turned on, perform the following steps:

  • Select the Dept_No property in the conceptual model.
  • Go to the item properties and change the value of Storage Generate Pattern to Identity. The value of the column in the storage part changes automatically.

When Automatic synchronization of storage part is turned off, perform the following steps:

  • Open the model storage part and then find the Dept entity.
  • Expand the Dept entity and select the Dept_No property.
  • Go to the selected item properties and then change the Store Generated Pattern value to Identity. Please note that the trigger and sequence will not be created if you change the Store Generated Pattern value only in the Conceptual model, when Automatic synchronization of storage part is turned off. In this case, it should be set in the Store model. This situation is described in our Entity Framework: Tips and Tricks, part 4 blog post.

Entity Developer also recognizes generated triggers and sequences when the Database-First approach is used. Corresponding generated properties are marked as autoincrement by default.

In future, we plan to continue working on Entity Developer improvements. If you have any suggestions – don’t hesitate and share your ideas about new Entity Developer features on our UserVoice page or leave your reply in comments to this article.

See also

Comments are closed.