Implement Inheritances in Reverse Mapped Database

[Jay]

Hi all,

I was wondering if anyone had any experience implementing inheritances in reverse mapped database. I saw this post, but is very out of date and seems to no longer be applicable. I have a database with 50 or so tables and most, if not all of them, inherit properties from a base class. It would be nice to translate this into the ORM Model somehow.

Any timely help will be greatly appreciated.

Thanks,

Jay

[Kosta Hristov]

Hello Jay,

The article you mentioned is actually refers to the "classic" wizards of OpenAccess. As we do not have such article about the visual designer, I will give you some directions how to achieve that.

Consider the following scenario:
1. We will use the Person and Employee tables described in the post you saw. The only change we need to do is to modify Employee's primary key column name from "EmployeeID" to "PersonID". The primary key columns of the two tables need to be with the same name. 

2. A new domain model should be generated from the database. This can be done by right-clicking on the project, selecting OpenAccess>Add domain model and following the wizard steps.

3. After the domain model is generated, some modifications have to be done. First of all, remove the relations between the classes (right-click on the connection and click Delete).

4. Then remove the PersonID property from the Employee class, as it will inherit the identity of the Person base class.

5. Add inheritance relationship between the two classes so that Employee inherits from Person. This can be done by clicking on the Toolbox button (this will open the toolbox), selecting the "Inheritance" connection and dragging it from Employee to Person.

6. As the two classes are mapped to separate tables, Vertical inheritance will have to be used. To set it, select the Employee class and at the bottom of Visual Studio select the Mapping Details Editor tab. Then set the Inheritance strategy to Vertical.


7. Open the Discriminator combo box located in the Mapping Details Editor and select <NONE>, you should do this even if it's already selected. This is a small visual bug we are working on.

8. Finally, check if the classes are mapped to the tables . The Employee mapping will probably be gone, so it has to be added again.


Now everything should be ready. You can test it by using few simple lines of code, for example :

EntitiesModel model = new EntitiesModel();
Employee emp = new Employee();
// assign values to members
model.Add(emp);
model.SaveChanges();

The appropriate record should be added to the database.

(Note that for Vertical mapping, the Discriminator column is not mandatory, more information on the different kinds of mapping can be found here. )

If you want to use a discriminator column, you have two options :

1. Create it manually in the initial table and then select it from the Mapping Details Editor.

2. Create the discriminator column manually in the Model Schema Explorer (Tables>Person>Add new column)


and then in the Mapping Details Editor select it to be a Discriminator. Check if the mapping is correct. Right-click on the rlinq designer and click Update database from model. The discriminator column will be generated in the Person table, along with any other modifications.

I hope this helps. If you need further assistance, do not hesitate to ask.

Best Regards
Kosta
the Telerik team

[Gunnar]

This worked for for me, however,  there is one thing to add:
(Database: MySql)
If your "children database tables" primary key fields originally where set to AUTOINC, you will need to
remove these statements in the database-diagram-name.rlinq.diagram

<orm:identity>
  <orm:key-generator name="autoinc" />
</orm:identity>

(Not sure what happens if they where never set to AUTOINC)
They are not removed when updating these tables from the database setting AUTOINC to false, and they cause this runtime exception:
Telerik.OpenAccess.OpenAccessException: The db-key-generator extension is only allowed for the least derived class in a hierarchy

Gunnar

 

 

 

[Kosta Hristov]

Hi Gunnar,

Thanks for making this note. As an alternative to changing the XML, you can take the same action in the UI via the Visual Designer.  You should just select the derived entity and set the Identity mechanism property from DatabaseServerCalculated to Default. This will cause OpenAccess to internally calculate the correct value for the identity property of your objects e.g. to take the value from the base class.

All the best,
Kosta
the Telerik team

[Gunnar]

voa:
Reverted back to having Telerik ORM create the primary keys:
Doing just the above instructions I get:

Invalid object name 'voa_keygen'. Telerik.OpenAccess.RT.sql.SQLException: Invalid object name 'voa_keygen'.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.executeQuery(String sql)
   at OpenAccessRuntime.Relational.sql.HighLowRelationalKeyGenerator.IsCached(Connection con, RelationalKeyGeneratorInfoCache infoCache, String tab, String kCol, String vCol, String key, Boolean& ret)
   at OpenAccessRuntime.Relational.sql.HighLowRelationalKeyGenerator.init(String className, RelationalTable theClassTable, Connection con, RelationalKeyGeneratorInfoCache relationalKeyGeneratorInfoCache)
   at OpenAccessRuntime.Relational.RelationalStorageManagerFactory.init(Boolean full)

Cannot get the "Update Database from Model" (tried both MySql and MSSQL 2008) to generate the voa_keygen table.
Also it seems from Exceptions I get that the Mother table is expected to have a voa_class field...

I do get around this by adding the neccessary table voa_keygen and the field voa_class to all mother tables.
However, my database script should ideally be kept clean of proprietary ORM stuff, so I would very much prefer this to work from Telerik.OpenAccess (having an extra db script is also kind of messy).

Thanks

Gunnar

[Zoran]

Hi Gunnar,

Please, let me apologize for the little misunderstanding I believe we caused with the posts in this discussion. Here are a few words on vertical inheritance so you can understand the situation better:

1. In vertical inheritance, the primary key is always obtained from the 'Parent' table.
2. The above means that the child table should not have any identity mechanism set. That is why disabling autoinc in the designer is propose in one of the previous posts. However this should be done only in cases where OpenAccess will create your database schema.
3. If you have AUTOINC identity mechanism on the child table in your database, you will not be able to setup a valid vertical inheritance where this table is mapped to the inheritor in the relationship. One of the workarounds is to make the class mapped to this table the parent or base class in the relationship if your business logic allows it.

So, to summarize, in your reverse mapping case you can create vertical inheritance only if the table that has autoinc set in the database(and detected by OpenAccess in the designer) is in the 'Parent' role of the inheritance relationship. The table that would have the 'Child' role in this case must not have any identity mechanism set.

Kind regards,
Zoran
the Telerik team

[Gunnar]

Thanks Zoran,

First of all, as stated in my post above, I removed the AUTOINC from all tables in the database.
I am aware about the vertial inheritance parent and child tables, however, we do have a primary key in the child tables as well which is also a foreign key to the parent table (we have as a principle that all tables must have a primary key).
As I understand this might be what trips up the voa_class and keygen generation from OpenAccess inn this case - will test this when I get the time and have talked to the db designer.

Thanks


Gunnar

[Zoran]

Hi Gunnar,

Since you have removed all of the AUTOINC settings from your database, I guess you would like to set the identity field manually. However if you have selected 'Default' as the identity mechanism in the Visual Designer, OpenAccess takes the internal HIGHLOW identity mechanism as the default identity generation mechanism. That is why the voa_keygen table is tried to be generated. In order to avoid this, you should explicitly set the Identity Mechanism to 'None' in the Visual Designer for the entities involved. Also, I would like to mention that removing the AUTOINC settings from the database is not necessary, you can have AUTOINC as identity mechanism on the base class with no problems.

Kind regards,
Zoran 
the Telerik team