I recently discovered a nice pattern for dealing with self joins, and many-to-many joins in a Data Access Layer. Here's the problem. In your domain design you have an class with a collection of itself. This is a common pattern for tree structures.
class MyEntity
{
MyEntityCollection _myEntities;
MyEntityCollection MyEntities
{
get{ retrun _myEntities; }
}
}
To do a similar thing in a relational database one would normally have two tables, the entity table and a table to map the relationships between the entities.
create table MyEntity(
id int
)
create table MyEntityMap(
from_id int,
to_id int
)
Now, how do we recreate object map in memory from the relational data. My solution is to use a temporary domain class to represent the map table. It has a reference to the entities on both ends of the relationship and a method to wire them up.
class MyEntityMap
{
MyEntity _fromEntity;
MyEntity _toEntity;
public void WireUp()
{
_fromEntity.MyEntities.Add(_toEntity);
}
}
The MyEntityMapCollection class has a method WireUp() that calls WireUp() on every MyEntityMap object.
class MyEntityMapCollection
{
ArrayList _myEntityList = new ArrayList();
...
public void WireUp()
{
foreach(MyEntity myEntity in _myEntityList)
{
myEntity.WireUp();
}
}
}
Then in our data access layer we can simply get all the MyEntity objects from the database, storing pointers to them in an object store, then get all the MyEntityMap objects. Each MyEntityMap object gets the pointers for the correct objects from the object store. The last task is to call WireUp() on the MyEntityMapCollection. Here's the DAL code.
public MyEntity GetEntityMap()
{
MyEntityCollection myEntities = SelectAllEntitiesStoredProcedureWrapper();
MyEntityMapCollection myMap = SelectAllEntityMapStoredProcedureWrapper();
myMap.WireUp();
// some method to get the root MyEntity
return GetRootEntity(myEntities);
}
Posts
