[Linq to SQL] 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

The current ms tooling for LINQ is some what limited and it doesn't really look like much effort will be coming for vs 2010. You can write your own code generator, look at Damien's t4 templates or PLINQO. I have also found EDMDesigner which might be worth a look.

Answer 2

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.

Answer 3

my xml from sqlmetal had much more sensible defaults. not perfect, but better. The first FK got the table name, but the second got the field name.

  <Association Name="FK_Product_DefaultOutputTypeID" Member="OutputType"
   ThisKey="DefaultOutputTypeID" OtherKey="OutputTypeID" Type="OutputType"
   IsForeignKey="true" />
  <Association Name="FK_Product_DefaultTileOutputTypeID" Member="DefaultTileOutputType"
   ThisKey="DefaultTileOutputTypeID" OtherKey="OutputTypeID" Type="OutputType"
   IsForeignKey="true" />

sqlmetal version:

  C:\Program Files\Microsoft Visual Studio 9.0\VC>sqlmetal /?
  Microsoft (R) Database Mapping Generator 2008 version 1.00.21022
  for Microsoft (R) .NET Framework version 3.5
  Copyright (C) Microsoft Corporation. All rights reserved.
  ...

Answer 4

In VS2010 You can actually rename the properties for parent and child in the view. It is a bit hidden though.

1. In the dbml viewer select the relationship that bothers you.
2. In the Properties grid you will have two rows Child and Parent Property.
3. Expand them there you can change the Name of the property

More details can be found here: http://weblogs.asp.net/scottgu/archive/2007/05/29/linq-to-sql-part-2-defining-our-data-model-classes.aspx

Which is where I got it from.