Many to Many O/R Mapping using the Repository Pattern

Topics: General Discussion Forum
Apr 5, 2007 at 6:39 PM
Hello,

Does anyone have any thought how I would map many to many relationship using Business Entities to my Stored Procs.

Amer Gill
Apr 13, 2007 at 8:59 AM
On my project we came up with a solution, may not be the best. Here is an extract on some guidance:

When defining business entities for a service, and in particular one which persists to a relational database, a choice needs to be made as to how to define business entities that correspond to entities in the database.

1-to-many relationships:
In the database these relationships are modelled as a foreign key at the ‘many’ side of the relationship. When converted to business entities this makes more sense if the ‘one’ side of the relationship owns a collection of the ‘many’ entities. However it is useful for the ‘many’ side to know who its parent is, therefore a bi-directional relationship is used.

Many-to-many relationships:
In the database a many-to-many relationship uses the concept of a link table, which has foreign keys to each ‘many’ entity. When this link has no extra details the link table serves only to provide a many-to-many relationship, sometimes the link table can add further information about this link however. It seems valid in a business entity model to also have the concept of this link (here with the concept of ‘Usage’).

Primary keys:
A primary key is often only useful for the database, as it may not make any ‘business sense’ as a different item may be the business ‘primary key’. Primary keys should be replicated in the business entity model, partly as there is no real detriment, but also so that the automatically generated stored procedures can be used.


Bi-directional relationships:
A problem with bi-directional relationships is that you could end up in a continuous loop, as each side of the relationship may refer to the other, with a uni-directional relationship this is simplified, however the child side of the relationship does not have access to its parent, which can be limiting. I propose that bi-directional relationships should be used but the parent (the ‘one’ side of the relationship usually) should own the relationship, the reverse relationship should only be used for access to the parent. In the example these reverse relationships are prefixed with ‘my’ to try and indicate this.


Repository mappings:
The DAL guidance package is fairly simple and only works on a one-to-one basis between business entities and database tables. When foreign keys and many-to-many relationships appear this becomes difficult. To make the repositories work the business entities not only include the reverse relationship but access the primary key of the parent entity so that this can be used to create the foreign key in the database. It is worth noting this parent.primaryKey is not stored in the child entity but accessed via the reverse relationship

e.g.

private Service _MyService;

public Service MyService
{
get { return _MyService; }
set { _MyService = value; }
}

public System.Int64 MyServiceID
{
get { return MyService.ID; }
}

Partial Classes:
The business entities can be auto-generated from the database tables; however this does not model relationships. If these relationships are added afterwards and then the auto-generation repeated (e.g. change to database) the relationships will be lost. To get around this partial classes can be used this means the relationships can be stored in a separate file which is combined with the auto-generated file to compile the class so you could have version.cs and version.relationships.cs This sounds like a good idea, however, due to a current flaw in the DAL guidance package the business entities all appear twice in the wizard, rather than being combined into one. Therefore I recommend for the moment that partial classes are not used.

Constructors: should 'new up' the collections for 1-many relationships. They should not ever add an instance to the collection though.

Issues:
Confusion can arise with the many-to-many relationships. For example when reading these from the database the link entity needs to create the two parent entities and write to their primary key, whereas the original idea prevented entities from writing to each other’s properties, and prevented a child ‘newing up’ its parent.