C# 如何在代码中建模多对多关系？

How to model a Many to many-relationship in code?

提问by Natrium

Suppose I have 2 tables in a database. eg: Dog & Boss This is a many to many relationship, cause a boss can have more than 1 dog, and a dog can have more than 1 owner. I am the owner of Bobby, but so is my wife.

假设我在数据库中有 2 个表。例如：Dog & Boss 这是一个多对多的关系，因为一个老板可以有超过 1 只狗，而一只狗可以有超过 1 个主人。我是 Bobby 的所有者，但我的妻子也是。

But many to many is not allowed, so there is a helpertable: DogsPerBoss

但是多对多是不允许的，所以有一个帮助表：DogsPerBoss

How to model this in code?

如何在代码中对此进行建模？

Class Boss can have a collection of Dogs. Class Dog can have a collection of Bosses. --> at least, that is what I think. Perhaps there are better solutions?

班级 Boss 可以拥有狗的集合。狗类可以拥有一组 Boss。--> 至少，我是这么认为的。也许有更好的解决方案？

How about extra data that is in the helper-table? Should that be in de Boss-class or in the Dog-class? eg: Nickname (I call the dog "good boy" and my wife calls him "doggie")

辅助表中的额外数据如何？应该在de Boss级还是在Dog级？例如：昵称（我称这只狗为“好孩子”，我的妻子称他为“小狗”）

I hope my question is kinda clear? Are there any best-practices on what is the best way to achieve this? Can you give me some references?

我希望我的问题有点清楚？是否有关于实现这一目标的最佳方法的最佳实践？你能给我一些参考吗？

An ORM (like NHibernate) is not an option.

ORM（如 NHibernate）不是一种选择。

采纳答案by John Saunders

Why are you talking about tables? Are you creating an object model or a database model?

你为什么说桌子？您是在创建对象模型还是数据库模型？

For an object model, there's no reason a Dog can't have a List<Owner>and an owner have a List<Dog>. Only if you have attributes on the relationship do you need an intermediate class (what UML calls an Association Class). That's when you'd have a DogOwnership class with extra properties, and each Owner would have a List<DogOwnership>, and so would each Dog. The DogOwner would have a Dog, an Owner, and the extra properties.

对于对象模型，没有理由狗不能有 ，List<Owner>而所有者没有List<Dog>。只有当您在关系上有属性时，您才需要一个中间类（UML 称之为关联类）。那时你会有一个带有额外属性的 DogOwnership 类，每个 Owner 都有一个List<DogOwnership>，每个 Dog 也是如此。DogOwner 将有一个 Dog、一个所有者和额外的属性。

回答by Graham

If you didn't need to record the nickname, then Dog should have a list of Bosses and Boss should have a list of Dogs.

如果你不需要记录昵称，那么Dog应该有一个Bosses列表，Boss应该有一个Dogs列表。

If the relationship between Dog and Boss has attributes, in this case nickname, then you should create a class to represent that relationship and have Dog and Boss both hold lists of that type.

如果 Dog 和 Boss 之间的关系具有属性，在本例中为昵称，那么您应该创建一个类来表示该关系，并让 Dog 和 Boss 都包含该类型的列表。

I've been using NHibernatefor a while now and find it very useful for easing this sort of object relational impedance mismatch.

我已经使用NHibernate一段时间了，发现它对于缓解这种对象关系阻抗不匹配非常有用 。

回答by waqasahmed

Not sure on what your asking for. But this is the table structure you want:

不确定你的要求。但这是您想要的表结构：

Dog Table

狗桌

DOG_ID int PK DOG_Name varchar(50)

DOG_ID int PK DOG_Name varchar(50)

DogsPerBoss

DogsPerBoss

ID int DOG_ID int BOSS_ID int DogNickName varchar(15)

ID int DOG_ID int BOSS_ID int DogNickName varchar(15)

Boss

老板

BOSS_ID int PK BOSS_Name varchar(50)

BOSS_ID int PK BOSS_Name varchar(50)

回答by Greg Dean

I guess am missing something. Why is many to many not allowed?

我想我错过了一些东西。为什么不允许多对多？

public class Boss
{
    Dog[] dogs;
}

public class Dog
{
    Boss[] bosses;
}

回答by MrTelly

This is a classic issue between databases where many to many doesn't work, hence your helper table, and the object world where many to many works fine. As soon as the relationship has attributes then you should create a new class to hold that information. However, you'll save yourself a lot of head time if you look at Object Relation Mapping - ORM - that whole field grew up to solve this (and many other) problems between DB and Object.

这是多对多不起作用的数据库之间的经典问题，因此您的助手表，以及多对多工作正常的对象世界。一旦关系具有属性，您就应该创建一个新类来保存该信息。但是，如果您查看对象关系映射 - ORM - 整个领域的发展是为了解决 DB 和对象之间的这个（以及许多其他）问题，那么您将节省很多时间。

回答by Jamie Ide

If you have a simple many-to-many linking table with foreign keys from each table in the relationship, then you would model it as you suggest: Boss has a collection of Dogs and Dog has a collection of Bosses.

如果您有一个简单的多对多链接表，其中包含来自关系中每个表的外键，那么您可以按照您的建议对其进行建模：Boss 有一个 Dogs 的集合，Dog 有一个 Bosses 的集合。

If you have a many-to-many relationship with extra data, such as Nickname, then you would model that as two one-to-many relationships. Create an entity, such as DogBoss so that Boss has a collection of DogBoss and Dog has a collection of DogBoss.

如果您有额外数据的多对多关系，例如昵称，那么您可以将其建模为两个一对多关系。创建一个实体，例如 DogBoss，以便 Boss 拥有 DogBoss 的集合，Dog 拥有 DogBoss 的集合。

回答by John Nicholas

the traditional many to many relation would have no extra fields on the matching table.

传统的多对多关系在匹配表上没有额外的字段。

Because you do have fields with unique information I tend to stop thinking of these relations as many to many.

因为您确实有包含独特信息的字段，所以我倾向于不再考虑这些关系是多对多的。

Once you add information to the matching table i think you have then made this table into an entity in its own right and so needs its own object to represent it.

一旦您将信息添加到匹配表中，我认为您已经将该表本身变成了一个实体，因此需要它自己的对象来表示它。

At this point you can begin to have a DogsName class to connect a person and a dog - both of which would contain references to this object as part of a collection.

此时，您可以开始使用 DogsName 类来连接人和狗——这两个类都将包含对此对象的引用作为集合的一部分。

However whether you give the dog a name to be called by or own the dog are independant.

但是，您是否给狗取了名字以供狗称呼或拥有狗是独立的。

As well as modelling the relation of dogs name according to different people you also need to model the ownership relationships. In memory this would mean both objects contain a list of the other objects.

除了根据不同的人对狗名的关系进行建模之外，您还需要对所有权关系进行建模。在内存中，这意味着两个对象都包含其他对象的列表。

回答by Frederik Gheysels

Something like this; It still needs some finetuning though (make the collection private and add a readonly public accessor for it which returns a readonlycollection for instance, but you'll catch the drift.

像这样的东西；尽管如此，它仍然需要一些微调（将集合设为私有并为其添加一个只读公共访问器，例如返回一个只读集合，但您会发现偏差。

public class Dog
{
    public List<Boss> Bosses;

    public void AddBoss( Boss b )  
    {
        if( b != null && Bosses.Contains (b) == false )
        {
            Bosses.Add (b);
            b.AddDog (this);
        }
    }

    public void RemoveBoss( Boss b )
    {
         if( b !=null && Bosses.Contains (b) )
         {
             Bosses.Remove (b);
             b.RemoveDog (this);
         }
    }
}

public class Boss
{
    public List<Dog> Dogs;

    public void AddDog( Dog d )
    {
         if( d != null && Dogs.Contains (d) == false )
         {
              Dogs.Add(d);
              d.AddBoss(this);
         }
    }

    public void RemoveDog( Dog d )
    {
        if( d != null && Dogs.Contains(d) )
        {
            Dogs.Remove (d);
            d.RemoveBoss(this);
        }
    }
}

In this way, you could model a many-to-many in your code where every Dog knows his Bosses, and every Boss knows his Dogs. When you need extra data in the helper table, you'll need to create another class as well.

通过这种方式，您可以在代码中对多对多进行建模，其中每条狗都知道他的老板，每个老板都知道他的狗。当您需要帮助表中的额外数据时，您还需要创建另一个类。

回答by CraigTP

In a relational model, the best way to model a many to many relationship (using your example of Dogs/Bosses) is to have three separate tables.

在关系模型中，建模多对多关系的最佳方法（使用您的狗/老板示例）是拥有三个单独的表。

One table for DOGS, one table for BOSSES (and each of these tables has a unique key), and the third table is usually a "junction table".

DOGS 一张表，BOSSES 一张表（而且每张表都有一个唯一的键），第三张表通常是“连接表”。

This table will usually have at least two fields, one field for the foreign key for a Dog and the other field for the foreign key of a Boss. This way each Dog can have many bosses, and each Boss can have many Dogs.

该表通常至少有两个字段，一个字段用于 Dog 的外键，另一个字段用于 Boss 的外键。这样每只狗可以有很多老板，每个老板可以有很多狗。

Now, when it come to modeling this in code in a more object-oriented manner, this is usually achieved by having a Dog class and a Boss class. As well as having the usual atomic properties for each of these objects, each one would also expose a property that is a collection of the other.

现在，当涉及到以更加面向对象的方式在代码中对此进行建模时，这通常是通过拥有一个 Dog 类和一个 Boss 类来实现的。除了为这些对象中的每一个具有通常的原子属性外，每个对象还会公开一个属性，该属性是另一个对象的集合。

So, for example, a Dog object would have a property called "Bosses". This property would expose a collection of Boss objects that are allocated to the specific Dog object (as defined in the junction table), and on the other side, each Boss object would expose a property called Dogs which would be a collection of Dog objects allocated to that specific Boss object (as defined by the junction table).

因此，例如，Dog 对象将有一个名为“Bosses”的属性。此属性将公开分配给特定 Dog 对象（如联结表中定义）的 Boss 对象集合，另一方面，每个 Boss 对象将公开一个名为 Dogs 的属性，该属性将是分配的 Dog 对象的集合到特定的 Boss 对象（由连接表定义）。

Note that there may well be some "overlap" in these objects (i.e. one "dog" object may have "boss" objects that another "dog" object has), however, this is the traditional mechanism for translating a three-table many-to-many relational model into an object oriented one.

请注意，这些对象中很可能存在一些“重叠”（即一个“狗”对象可能具有另一个“狗”对象具有的“老板”对象），但是，这是转换三表多表的传统机制——对多关系模型转化为面向对象的模型。

回答by James

public class Boss
{
   private string name;
   private List<Hashtable> dogs;
   private int limit;

   public Boss(string name, int dogLimit)
   {
      this.name = name;
      this.dogs = new List<Hashtable>();
      this.limit = dogLimit; 
   }

   public string Name { get { return this.name; } }

   public void AddDog(string nickname, Dog dog)
   {
      if (!this.dogs.Contains(nickname) && !this.dogs.Count == limit)
      {
         this.dogs.Add(nickname, dog);
         dog.AddBoss(this);
      } 
   }

   public void RemoveDog(string nickname)
   {
       this.dogs.Remove(nickname);
       dog.RemoveBoss(this);
   }

   public void Hashtable Dogs { get { return this.dogs; } }
}

public class Dog
{
   private string name;
   private List<Boss> bosses;

   public Dog(string name)
   {
      this.name = name;
      this.bosses = new List<Boss>();
   }

   public string Name { get { return this.name; } }

   public void AddBoss(Boss boss)
   {
      if (!this.bosses.Contains(boss))
      {
          this.bosses.Add(boss);
      }
   }

   public void RemoveBoss(Boss boss)
   {
      this.bosses.Remove(boss);
   }  

   public ReadOnlyCollection<Boss> Bosses { get { return new ReadOnlyCollection<Boss>(this.bosses); } }
}

The above maintains the relationship of Bosses can have multiple dogs (with a limit applied) and dogs having multiple bosses. It also means that when a boss is adding a dog, they can specify a nickname for the dog which is unique to that boss only. Which means other bosses can add the same dog, but with different nicknames.

以上维持了Boss可以有多只狗（有限制）和狗有多个boss的关系。这也意味着当老板添加狗时，他们可以为狗指定一个只有该老板独有的昵称。这意味着其他老板可以添加相同的狗，但具有不同的昵称。

As for the limit, I would probably have this as an App.Config value which you just read in before instantiating the boss object(s). So a small example would be:

至于限制，我可能会将其作为 App.Config 值，您在实例化 boss 对象之前读取该值。所以一个小例子是：

var james = new Boss("James", ConfigurationManager.AppSettings["DogsPerBoss"]);
var joe = new Boss("Joe", ConfigurationManager.AppSettings["DogsPerBoss"]);

var benji = new Dog("Benji");
var pooch = new Dog("Pooch");

james.AddDog("Good boy", benji);
joe.AddDog("Doggy", benji);

james.AddDog("Rover", pooch);
joe.AddDog("Buddy", pooch);  // won't add as the preset limit has been reached.

You can obviously tweak this as you see fit, however, I think the fundamentals of what you are looking for are there.

您显然可以根据需要进行调整，但是，我认为您正在寻找的基础知识就在那里。

• Boss can have multiple dogs with limit
• Dogs can have multiple bosses
• Bosses can have different nicknames for same dog.

• 老板可以有多个狗有限制
• 狗可以有多个老板
• 老板可以为同一只狗取不同的昵称。