Multiple foreign keys to the same table

Question

I have a reference table with all sorts of controlled value lookup data for gender, address type, contact type, etc. Many tables have multiple foreign keys to this reference table

I also have many-to-many association tables that have two foreign keys to the same table. Unfortunately, when these tables are pulled into a Linq model and the DBML is generated, SQLMetal does not look at the names of the foreign key columns, or the names of the constraints, but only at the target table. So I end up with members called Reference1, Reference2, ... not very maintenance-friendly. Example:

  <Association Name="tb_reference_tb_account" Member="tb_reference" <======
  ThisKey="shipping_preference_type_id" OtherKey="id" Type="tb_reference"
  IsForeignKey="true" />
  <Association Name="tb_reference_tb_account1" Member="tb_reference1" <======
  ThisKey="status_type_id" OtherKey="id" Type="tb_reference" 
  IsForeignKey="true" />

I can go into the DBML and manually change the member names, of course, but this would mean I can no longer round-trip my database schema. This is not an option at the current stage of the model, which is still evolving. Splitting the reference table into n individual tables is also not desirable.

I can probably write a script that runs against the XML after each generation and replaces the member name with something derived from ThisKey (since I adhere to a naming convention for these types of keys). Has anybody found a better solution to this problem?

Answer 1

So I went down the partial classes route. For instance, I added the following member to address the first reference member in my original example:

public partial class tb_account
{
    public tb_reference shipping_preference_reference
    {
        get
        {
            return this._tb_reference.Entity;
        }
        set
        {
            this.tb_reference = value;
        }
    }

This is far from perfect. It requires a substantial amount of extra code in a large model, and depends on the order of the attributes to not change (if another foreign key to the reference table is added to the account table, this member may actually point to something else than the shipping preference). There is an upside, too. Since I am already writing partial classes for other purposes, adding these members did not require that I rearchitect the application.