How to map a foreign key column referring two tables

RELATED TO:

Article information

Article relates to

 Telerik OpenAccess ORM

Created by

 Ivailo Ivanov

Last modified

 July 27, 2011

Last modified by

 Ivailo Ivanov


DESCRIPTION

 In some database models a table can contain a foreign key column that relates to two (or more) tables in the same time. In such scenarios, despite the successful mapping of the domain model from the database, an exception is thrown during runtime while performing select queries on the particular table.

The exception message is in the following format:

Field ‘(foreign key field)' of class '(foreign key class)' is the master of a shared column '(foreign key column)', but a second reference field '(foreign key target table)' tries to master this column too.
Columns can only be shared between a simple and a reference field. Usually, the reference field is the master and the simple field is the slave.

SOLUTION

Suppose we have the following tables:


The Domain Model diagram would look like this:



Note that TableA is linked both to itself and to TableB through one foreign key column – ForeignKeyA. 
In this case the described exception will be thrown when querying TableA, like in the following example:

EntitiesModel model = new EntitiesModel();
 
var exceptionQuery =  from objectA in model.TableAs
              where objectA.PrimaryKeyA == 1
              select objectA;

In order to avoid it, there is only one approach that is not error prone – to split the ForeignKeyA into two columns, each of them participating in only one foreign key relation. In our example, let’s leave the first column as it is and create a new column called TableBForeignKeyA. Here is the sequence of actions for implementing the change properly:

For reverse mapping:

1. Create a new column in the database table (TableBForeignKeyA) with the same type as ForeignKeyA
2. Reallocate the foreign key between TableA and TableB to point to the new column in TableA (TableBForeignKey instead of ForeignKeyA)
3. Open your Domain Model
4. Update from database TableA and TableB
5. Remove the classes TableA and TableB
6. Add the classes again (to ensure the associations are properly refreshed)
7. Save the Domain Model

For forward mapping:

1. Add new property in the Domain Model TableA class and name it TableBForeignKeyA. The type should be the same as ForeignKeyA’s type.
2. Double-click on the association between TableA and TableB. Change the property for TableA (Source Class section) to TableBForeignKeyA. 
3. Save the Domain Model
4. Update the database from the Domain Model

In our sample Domain Model, the diagram will become:





Telerik OpenAccess ORM will keep the proper data integrity between the two foreign key columns and the targets of the relations. However, ensuring the synchronization between the two columns – ForeignKeyA and TableBForeignKeyA – will have to be handled manually. Here is an example of how it can be implemented:

public partial class TableA
{
    public int? GetForeignKeyA()
    {
        if (ForeignKeyA != null)
            return ForeignKeyA.PrimaryKeyA;
 
        if (TableB != null)
            return TableB.PrimaryKeyB;
 
        return null;
    }
 
    public void SetForeignKeyA(WorkaroundEntitiesModel model, int? foreignKey)
    {
        ForeignKeyA = model.TableAs.FirstOrDefault(ta => ta.PrimaryKeyA == foreignKey.Value);
 
        if (ForeignKeyA == null)
            ForeignKeyA = new TableA { PrimaryKeyA = foreignKey.Value };
 
        TableB = model.TableBs.FirstOrDefault(tb => tb.PrimaryKeyB == foreignKey.Value);
 
        if (TableB == null)
            TableB = new TableB { PrimaryKeyB = foreignKey.Value };
    }
}

For a complete demonstration of the described problem and its solution, you can download the sample C# and VB projects using the links below.

Files
Related Products
Data Access

Resources Buy Try

More Resources

  • Forums

    Find answers to thousands of questions or reach out to our active community.

  • Videos

    Browse the Data Access videos to learn about handy features and tips and tricks.

  • Documentation

    Detailed help articles and API references for Telerik Data Access.

  • Knowledge Base

    Tutorials and working examples on how to achieve common scenarios.

  • Code Library

    Sample projects submitted by Telerik staff and community members.

  • Blog

    Stay up-to-date with the latest functionality, how-to articles, and industry trends.

  • Release History

    Up-to-date release notes for all new features and improvements.