How to: Model Complex Inheritance with the Visual Designer

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

The purpose of this tutorial is to show you how to model complex inheritance hierarchies (mixing vertical and flat inheritance).

  1. Create a new empty console application in Visual Studio.
  2. For this walkthrough, you will need to create a new empty database named AnimalKingdom on your server.
  3. Add a new Telerik Data Access Domain Model to your project. Name the domain model AnimalKingdomDomainModel. In the Select Domain Model Type Dialog, select the Empty Domain Model option.

  4. Click Finish to generate empty model. The next several steps demonstrate how to create a sample inheritance hierarchy in the Visual Designer.

  5. If the generated domain model is not opened in the Visual Designer, double click it in the Solution Explorer to open it.
  6. Add two interfaces to your domain model. For that purpose, open the Toolbox pane (from the main menu, point View and select Toolbox). Select the Interface item and drag it on the design surface.

    1. Name the first interface IAnimal. It exposes one string property - Name.
    2. Name the second interface ICat. It exposes one integer property - LivesLeft.

  7. Next, you have to declare several entities in the Visual Designer. To add a new entity in the design surface, open the Toolbox and drag the Domain Class item onto the design surface. To add a new property to a domain class, right-click the target domain class, point to Add, and then click Property. Another way to add a new property is to right-click the Properties section in the domain class, and then select Add new Property. Add five entities with the following characteristics:

    1. Animal - it exposes the following properties:
      1. AnimalId - this is the primary key for the entity. The type of this property is Int32. The Identity property of AnimalId is set to True.
      2. Age - the type of this property is Int32.
    2. Dog - it exposes the following properties:
      1. DogId - this is the primary key for the entity. The type of this property is Int32. The Identity property is set to True.
      2. BestFriend - the type of this property is String.
    3. Cat - it exposes the following properties:
      1. CatId - this is the primary key for the entity. The type of this property is Int32. The Identity property is set to True.
      2. Breed - the type of this property is String.
    4. Rottweiler - it exposes the following properties
      1. RottweilerId - this is the primary key for the entity. The type of this property is Int32. The Identity property is set to True.
      2. DogsEaten - the type of this property is Int32.
    5. WienerDog - it exposes the following properties:
      1. WienerDogId - this is the primary key for the entity. The type of this property is Int32. The Identity property is set to True.
      2. Length - the type of this property is Int32.
    6. Pet - the class exposes the following properties:
      1. PetId - this is the primary key for the entity. The type of this property is Int32. The Identity property of PetId is set to True.
      2. IsDead - the type of this property is Boolean.
  8. Finally, your model should look like the snapshot below.

    Note that initially, you are defining primary keys for all entities. However, when you define the inheritance strategies, some of the primary key properties will be deleted.

  9. The next step is to define inheritance associations. For that purpose, open the Toolbox pane. Select the Inheritance item.

  10. Select the objects to be included in the inheritance by clicking the derived entity first on the design surface and then clicking the base entity on the design surface:

    1. The Animal class derives (implements) from the IAnimal interface.
    2. The Pet class derives from the Animal class.
    3. The Cat class derives (implements) from the ICat interface and from the Pet class.
    4. The Dog class derives from the Pet class.
    5. The Rottweiler class derives from the Dog class.
    6. The WienerDog class derives from the Dog class.
  11. When you specify all inheritance relationships, your model should look like the snapshot below:

  12. Note that all properties of the IAnimal interface are copied to the Implementation section of the Animal entity. All properties of the ICat interface are copied to the Implementation section of the Cat entity. The next step is to specify the different inheritance strategies.

    1. Select the Cat class. Open the Mapping Details Editor and set its strategy to Vertical. With vertical mapping each class has its own table containing only its fields.

      Subclasses in vertical mapping should not have primary keys. That's why you have to delete the primary key property of the Cat class.

    2. Perform the same steps for the WienerDog and Rottweiler classes. Set their inheritance strategies to Vertical. Delete the RottweilerId and WienerDogId primary keys.

    3. Select the Dog class and set its strategy to Flat. With flat mapping the entire hierarchy is stored in a single table, i.e. all fields for the Dog entity will be stored into the base class table (the Pet table).

      Subclasses in flat mapping should not have primary keys. That's why you have to delete the primary key for the Dog class.

    4. Perform the same steps for the Pet entity. Set its inheritance strategy to Flat. Delete the PetId primary key.

  13. When you specify all inheritance relationships, your model should look like the snapshot below:

  14. Next, you have to create tables in the relational part of the model for each entity. The Cat, Rottweiler and WienerDog entities will be stored in separate tables (because they participate in vertical mapping). For the sake of simplicity, default mapping will be used. Select the Cat entity in the Visual Designer and open the Mapping Details Editor (View->Other Windows->Entity Diagrams Details Editor). In the Table Mappings section, check the Use Default Mapping option.

    Do the same for the WienerDog, Rottweiler and Animal entities. The Animal entity is a base class in Flat mapping. That's why it will be stored in its own table. Now take a look at the mapping for the Pet and Dog classes. They are mapped automatically to the animal table. This is because both entities are subclasses in flat mapping.

  15. The only step left is to update the database with the changes made so far to the model. To do so, rebuild the project and start the Update Database from Model Wizard (right-click on an empty area in the Visual Designer and choose Update Database from Model). In the Setup Database Connection dialog specify a connection string to the AnimalKingdom database which was created on step 2. On the second page of the wizard, you need to select the Create Database strategy. On the Summary Page (the third page), you could review the generated script and execute it. Execute the script. For more information about the Update Database from Model wizard, take a look at How to: Update an Existing Database Schema.

  16. Spend some time to analyze the generated script. The generated script should be similar to the SQL script listed below:

    -- Animal
    CREATE TABLE [animal] (
       [voa_class] varchar(255) NULL,          -- _voaClass
       [nme] varchar(255) NULL,                -- _nme
       [is_dead] tinyint NULL,                 -- _isDead
       [best_friend] varchar(255) NULL,        -- _bestFriend
       [animal_id] INT NOT NULL,               -- _animalId
       [age] varchar(255) NULL,                -- _age
       CONSTRAINT [pk_animal] PRIMARY KEY ([animal_id])
    )
    GO
    -- Cat
    CREATE TABLE [cat] (
       [lives_left] INT NULL,                  -- _livesLeft
       [breed] varchar(255) NULL,              -- _breed
       [animal_id] INT NOT NULL,               -- _animalId
       CONSTRAINT [pk_cat] PRIMARY KEY ([animal_id])
    )
    GO
    -- Rottweiler
    CREATE TABLE [rottweiler] (
       [dogs_eaten] INT NULL,                  -- _dogsEaten
       [animal_id] INT NOT NULL,               -- _animalId
       CONSTRAINT [pk_rottweiler] PRIMARY KEY ([animal_id])
    )
    GO
    -- Wiener_dog
    CREATE TABLE [wiener_dog] (
       [lngth] INT NULL,                       -- _lngth
       [animal_id] INT NOT NULL,               -- _animalId
       CONSTRAINT [pk_wiener_dog] PRIMARY KEY ([animal_id])
    )
    GO
    CREATE INDEX [idx_cat_animal_id] ON [cat]([animal_id])
    GO
    CREATE INDEX [idx_rttwlr_nmal_id] ON [rottweiler]([animal_id])
    GO
    CREATE INDEX [idx_wnr_dg_nmal_id] ON [wiener_dog]([animal_id])
    GO
    ALTER TABLE [cat] ADD CONSTRAINT [ref_cat_animal] FOREIGN KEY ([animal_id]) 
    REFERENCES [animal]([animal_id])
    GO
    ALTER TABLE [rottweiler] ADD CONSTRAINT [ref_rottweiler_animal] FOREIGN KEY ([animal_id]) 
    REFERENCES [animal]([animal_id])
    GO
    ALTER TABLE [wiener_dog] ADD CONSTRAINT [ref_wiener_dog_animal] FOREIGN KEY ([animal_id]) 
    REFERENCES [animal]([animal_id])
    GO