Using NHibernate to deal with many-to-many relationships with attributes

1. Introduction

Lao Tan in the interview of developers, in order to examine their database development ability, often sacrificed my magic weapon, is the University database tutorial on a model: Students choose classes. This pattern is this:

In this model, students (Student) and courses (Course) are entities. There are primary key IDs, respectively. Exam results (score) are a many-to-many relationship between students and the curriculum.

Based on this model, for the novice, can make some simple query requirements, for skilled, can make some complex query requirements, easy to use.

But the point today is to say. How to implement this pattern with NHibernate. And the general many-to-many relationship slightly different is that the relationship with a property, that is, exam results (score).

Assuming that many-to-many relationships have attributes, we usually extend the relationship to entities. That is to convert a many-to-many relationship into an entity plus two many-to-one relationships.

It is a waste to convert to an entity if there are multiple properties on a many-to-many relationship, whether it is necessary to convert it to an entity, but only a very small number of attributes (just one in this case). As a general case, for an entity, we create an artificial primary key for it, with a manual primary key, and a sequence. At the time of mapping, this entity naturally has a corresponding class. It's a lot of trouble to think about this whole bunch of things.

The crux of the matter is. Conceptually, this is a relationship that is converted to an entity for ease of implementation. This is an extra-thin entity. Make the concept more complex. Very awkward.

Therefore, here to explore, let the relationship back to the relationship, how to use NHibernate to deal with.

2. Mapping

If the attribute field is not score in the relational table score, the student entity can be mapped to this class:

public class student{public    virtual long Id {get; set;}    Public virtual string Name {get; set;}    Public virtual ilist<course> Courses {get; set;}}

Course is similar.

However, with the attribute score, this property is lost by this mapping. In order to take the score attribute, two entities should be mapped like this:

public class student{public    virtual long Id {get; set;}    Public virtual string Name {get; set;}    Public virtual Idictionary<course, int> Courses {get; set;}}

public class course{public    virtual long Id {get; set;}    Public virtual string Name {get; set;}    Public virtual idictionary<student, int> Students {get; set;}}

The original list becomes a dictionary (Dictionary), the primary key of the dictionary is the element in the original list, and the value is the attribute value on the relationship, that is, the test result.

The corresponding mapping file must also be adjusted, as the results are as follows:

... <class name= "Course" table= "Course" >  <id name= "id" unsaved-value= "0" >    <column name= "id"/ > ...  </id>  <property name= "name" >    <column name= "name"/>  </property>  <map Name= "Students" table= "Score" lazy= "true" >    <key column= "CourseID"/>    <index-many-to-many column = "StudentID" class= "Student"/>    <element column= "score"/>  </map></class>

... <class name= "Student" table= "Student" >       <id name= "id" unsaved-value= "0" >      <column name= "id" /> ...  </id>  <property name= "name" >    <column name= "name"/>  </property>  <map Name= "Courses" table= "Score" lazy= "true" >    <key column= "StudentID"/>    <index-many-to-many column = "CourseID" class= "Course"/>    <element column= "score"/>  </map></class>

Through this mapping. The attributes in many-to-many relationships are brought into the class and avoid creating entities for the relationship-there is no score this kind. The operation of such mapping results is basically similar to the common many-to-many relationship mapping approach. But there are several issues to be aware of.

3. Enquiry

For some simple queries, such as:

• The results of all the courses of Xiao Ming;
• The results of all the students in the chemistry course

are easier to handle, as this simply requires filtering on one entity.

Suppose it is necessary to add filtering conditions to two entities, such as inquiring about the achievements of Xiao Ming's chemistry class, it is somewhat complicated. Its query statement hql is this:

Select score from  Course C, Student s  join c.students score where c.name= ' chemistry ' and   index (score) = S.id   and S.name= ' Xiao Ming '

There is a hql function that we seldom use: Index (). This is specifically for map, which is designed to get the index field of the map.

The paradox is. Although we indicate that the key of map is an object. such as Course.students key is the object of student, but the result of index () is still the value of <index-many-to-many column= "..." Class= in the column field. In the query above. Index (score), we expect the result to be a student object, but the result is the ID of the object. So. In the query statement, we had to correlate student s and use S.name to filter.

Even the "simple query", such as the query of xiaoming all the results of the course, there is also a problem to be noted.

This problem is related to lazy loading.

In this query. We already know that we need to get all the lessons. Therefore, it is desirable to pre-load:

From Student s join fetch s.courses coursewhere s.name= ' xiaoming '

Once the results are obtained, assume that the environment is out of the query, such as releasing the session, when visiting the course, such as:

S.courses.select (c = c.key.name)

Exceptions are still thrown. As a result, the above query did not load the course object in.
It is not yet known how to preload the Index object in the map.

If you need to, just rely on lazy loading mechanism.

4. Maintenance

In addition to querying, it is also worth noting that when maintaining a relationship, the cascade of the Save-update type is not valid.

The Cascade property provides a lot of convenience for our relationship maintenance.

In a many-to-many relationship with regular (without attributes). Our maintenance operations can be this:

Xiao Ming selected chemistry class:

using (var tx = session. BeginTransaction ()) {    var student = getstudent (studentname)??
                  New Student {Name = studentname, Courses = new list<course> ()};    var course = GetCourse (coursename)??                  New Course {Name = coursename, Students = new list<student> ()};    if (!course. Students.contains (Student))    {        course. Students.add (student);    }    Session. Saveorupdate (course);    Tx.commit ();}

Among them, Getstudent (Studentname) and GetCourse (Coursename) each refer to the corresponding object from the database based on the student's name or course name.

In the preceding code fragment, assume that there is no database in the new.

After the relationship is maintained, save it.

It is important to note that when saving course, there is no guarantee that student is a persisted object. Assuming that student has not been persisted, NHibernate will save it on its own initiative when it is saved. and maintain and course the relationship. can do so. Depends on the definition of the Cascade attribute on the relationship in course (the end of the third line):

  <class name= "Course" table= "Course" >    ...    <bag name= "Students" table= "Scoure" lazy= "true" cascade= "Save-update" >      <key column= "CourseID"/>      <many-to-many column= "StudentID" class= "Student"/>    </bag>  </class>

But on a many-to-many relationship that includes attributes. Because you want to use map. This configuration cannot be done-configured and not in effect.

Assume that the student has not been saved in the database. We had to first create and persist it (line 7th):

using (var tx = session. BeginTransaction ()) {    var student = getstudent (studentname);    if (student = = null)    {        student = new Student {Name = studentname, Courses = new Dictionary<course, int> ()};< C4/>session. Save (student);    }    var course = GetCourse (coursename)??                 New Course {Name = coursename, Students = new dictionary<student, int> ()};    if (course. Students.containskey (Student))    {        course. Students[student] = score;    }    Else    {        course. Students.add (student, score);    }    Session. Saveorupdate (course);    Tx.commit ();}

Otherwise, just wait for nhibernate to throw an exception.

5. Conclusion

Many-to-many relationships expressed with map/dicitionary are more complex to operate than those expressed by bag/list.

But the price. We are willing to bear.

This is due to. We value model design more and focus more on conceptual integrity. It is the model that determines the detail implementation, not the reverse. Modify the design of the model according to the detailed implementation.

??

Handle many-to-many relationships with attributes with NHibernate