Joiner tables with extra fields

[Robert Lautenbach]

I am in the process of reverse mapping a database into generated classes. I came across an issue with joiner tables that have one or more extended properties in them.

An example:

• Person table
• Family joiner table

Each person can have multiple links to other people in the person table through the Family joiner table. The Family joiner table also contains a few extra fields to define the relationship type, marital status, related by marriage, etc.

Since that joiner table has extra fields beyond the joiner id's, the reverse mapper wants to make it a class rather than just a joiner. Is that standard practice? Or is that something that folks with experience would recommend handling a different way either at the database level or in the persistant classes?

[Zoran]

Hello Robert Lautenbach,

The only tables that are treated as join tables are the ones with two foreign key constraints in them. Otherwise the table is treated as normal table and it is mapped to an entity. The reason behind this is that join tables are mapped to associations between entities in the object model and their fields are populated from the values of the Primary Keys of the related objects. Hence, there is no way to insert values in the remaining fields. So if you would like to be able to set values to the remaining fields of the join table, it has to be mapped to an entity. Nevertheless, if you like to threat it as a normal join table and just make a link between two persons(self reference), it is doable using the new visual designer from the Q1 2010 release. You just have to "unmap" the family table, and then drag an association link on the design surface that starts and ends with the person entity. The relationship definition window will pop up and there you can fine tune your association so it threats the Family table as "join-table".

I hope my answer helps you to better accumulate this area of Object Relational Mapping. For any future questions, please contact us back.

Kind regards,
Zoran
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[jay]

Hello,

I got stuck with a relational database update issue in a RIA implementation and decided to take a look at OpenAccess ORM. I just saw this thread and I believe someone might throw light on my problem.  I have a silverlight datagrid with some linked tables  - let's call them A, B and C. I want to present fields from all three tables on a datagrid (displaying a list of records) and another grid (displaying details of the selected record on the datagrid, and from which i can make updates). Because i wanted to do sorting by columns from both A and B on the "list" datagrid, I decided to use an SQL SERVER View which is updatable in SQL SERVER. My associated RIA entitiy which is the source of data for the datagrid was created as ReadOnly with multiple primary keys. An attempt to update any field on the "details" grid results in a Read-Only error.
Can using ORM get over this limitation and, if so, how? This will be one of the few reasons for me to adopt ORM.

Thanks!

[Zoran]

Hi jay,

 Yes, Telerik OpenAccess ORM is able to update database views as specified in SQL Server. Our RIA services support allows this and it works perfectly well in this scenario. You should not get these errors in the scenario with OpenAccess. You should just have in mind that you should manually set the keys on your view after importing the data in the OpenAccess model designer.

If you have any further questions/suggestions please do not hesitate to contact us back.

Best wishes,
Zoran
the Telerik team