When using any ORM generator, they tend to be quite useful, until…
As you may know, an ORM generator is a software tool used to generate repeatable code patterns used to convert software object into relational structures, primarily pointing to SQL relational data stores. When you write your own ORM you quickly find you are writing the same code over and over, simply replacing the specifics with the properties of your objects, and the tables/columns of your database. You don’t have to get into it for very long before a pattern emerges, and you can see how this code can be automatically generated.
I have worked with a number of ORM tools over the years. The latest I have been using is Entity Framework. It works really nicely for many different problems. One of the more difficult things for an ORM Generator to do is to handle Many to Many relationships.
A Many to Many relationship is when Two different entities has set of each other. For example, consider the relationship between Languages and Speakers. A language may have many Speakers. A Speaker may speak many Languages. The results in a Many to Many relationship. Entity Framework (EF) handles relationships of this kind rather easily. In fact, if you are using Code First generation, where you create the objects first, and Entity Framework generates the data access code, and the database schema, EF will create three tables. It creates the Speakers and Languages table. Because relational databases require a new table to handle Many to Many relationships, EF will also generate a third table LanguagesSpeakers containing the join key values for both Language and Speaker.
A database modeler is used to this implementation. In fact, they are aware that there may be additional data pertaining to that Many to Many relationship. The contoso database that is often used to demonstrate SQL Server solutions, demonstrates this kind of many to many relationships. The database is about a school or training institution, where you have teachers, students, classes, enrollment.
Students enroll in many classes. Classes have many students. That forms a simple Many to Many relationship. In Contoso, this forms a many to many relationship in a table called Enrollment. Notice that the Many to Many table name is not StudentClass, or ClassStudent. It is called Enrollment, requiring additional work on the part of the developer to instruct EF how to proceed.
To make matters worse, the Enrollment table logically contains addition data. Not only does it contain the key for Student and Class, but the student may receive a Grade for participating in the Class. So the Enrollment table would contain a Grade column.
Let’s exacerbate the problem by creating a futher table, related to the Enrollment table, containing attendance records, assignment history or testing history. Now you have a new many to many relationship between your Enrollment (Many to Many relationship) and the Assignment, or Test. Things begin to cascade a little bit more. To me, this is a simple database, and an accurate design of the data problem; but we are already in a quagmire when working with an ORM out of the box. It needs some additiona directives.
Tomorrow, we’ll dig into this final scenario a little further. For now, suffice it to say, Code generators work rather nicely, until you start getting into real world problems. Those are the ones they don’t tend to demonstrate in training courses.
Do you use EF to solve these more difficult Many to Many relationships? How did you learn to solve them? Share your experience with a comment or two.
Cheers,
Ben