[Linq to SQL] Multiple foreign keys to the same table
- by cdonner
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?
