多个外键到同一个表 [英] Multiple foreign keys to the same table

问题描述

我有一个引用表，包含性别，地址类型，联系类型等各种控制值查找数据。许多表有多个外键给这个引用表

<我也有多对多的关联表，有两个外键到同一个表。不幸的是，当这些表被拉入Linq模型并生成DBML时，SQLMetal不会查看外键列的名称或约束的名称，只能在目标表上查看。所以我最终得到了名为Reference1，Reference2的成员... ...不是很好的维护。例如：

 < Association Name =tb_reference_tb_accountMember =tb_reference< ====== 
 ThisKey =shipping_preference_type_idOtherKey =idType =tb_reference
 IsForeignKey =true/> 
< Association Name =tb_reference_tb_account1Member =tb_reference1< ====== 
 ThisKey =status_type_idOtherKey =idType =tb_reference
 IsForeignKey = true/> 
  

我可以进入DBML并手动更改成员名称，当然，但这意味着我不能再往返我的数据库模式。在目前这一模式仍在发展中，这不是一种选择。
将引用表拆分为n个单独的表也是不可取的。

我可以编写一个脚本，在每一代之后运行XML并替换成员名称与从ThisKey派生的东西（因为我坚​​持这些类型的键的命名约定）。
有没有人找到更好的解决这个问题？

解决方案

例如，我添加了下面的成员来解决在我的原始示例中的第一个引用成员：

pre $ public $ b $ c $ $ $ $ b {
public tb_reference shipping_preference_reference
{
get
{
return this._tb_reference.Entity;
}
set
{
this.tb_reference = value;
}
}

这远非完美。它需要大量的额外的代码在一个大的模型，并取决于属性的顺序不会改变（如果另外一个外键添加到帐户表的参考表，这个成员可能实际上指向除了运输偏好）。
还有一个好处。由于我已经为其他目的编写了部分类，添加这些成员并不要求我重新构建应用程序。

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?

解决方案

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.

这篇关于多个外键到同一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！