Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Many-to-many relationships with data attached in NHibernate

I’m such a nice brother. For Christmas this year, I got my older brother an MVC application to help him with some of his community work for his land surveying responsibilities. In return, I’ve got a nice little app that’s got at least two more posts in it and probably just as many Dimecasts.

I’ll describe the domain that led to this post so’s I don’t have to change any of the sample code I’ll inevitably paste in here.

We have land surveyors. Each one, over the course of his or her career, will use "monuments". Which as far as I can tell are those big sticks with orange paint on the end you often see stuck into the ground. Why are they called something as grandiose as monuments? My brother likes to say it’s the second oldest profession in the world (and given what some of them charge, they aren’t far removed from the first). So in ye olde dayes, instead of using sticks, they’d erect monuments. See, the Pyramids and the Eiffel Tower aren’t actually the grand constructions you think they are. They’re just the result of diligent land surveyors trying to find the property line.

Anyway, I’m getting off track (though to my credit, I took longer than usual to do so this time). A surveyor will use monuments/sticks and they each one has an official label. These days, the label appears to be related to the company you work for. So all surveyors currently working for Tango & Cash Geomatics will use monuments labelled TC. If a surveyor jumps ship over to Rowan & Martin Land Surveyors, he’ll then use a monument labelled RM. And so on and so forth.

Here are the tables involved:


A pretty standard many-to-many relationship. But where this differs from almost all of the NHibernate examples you see is that there is data attached to the relationship. I.e. it’s not enough that we know Sandy McLean used monument TH, we want to know the period in which he and/or she used it.

Now, I’m using Fluent NHibernate here because, well, it’s my app. I don’t to answer to you people. And the question arose, how do I map this to my domain?

I’ve skipped an obvious precursor question though. Namely: what does the domain look like. This may not be crazy obvious at first glance. Of course, there is a Surveyor object and a Monument object (sparse as it is at the moment). And you might think that a surveyor has a collection of Monuments but that’s not the case.

imageIn fact, as was pointed out to me by a few people (acknowledgements are at the end), the relationship ‘twixt Surveyors and Monuments is a domain concept in its own right. That is, we need another object to represent that relationship. A MonumentAssignment, for example, which contains the properties you might expect: Monument, Surveyor, YearStarted, and YearEnded.

Mapping the MonumentAssignment with Fluent NHibernate was relatively easy ("relatively" being the key word; this *is* NHibernate we’re dealing with):

public class MonumentAssignmentMap : ClassMap
    public MonumentAssignmentMap( )
        WithTable( "SurveyorMonument" );
        Id( x => x.ID, "SurveyorMonumentID" );
        References( x => x.Surveyor, "SurveyorID" )
            .WithForeignKey( "SurveyorID" )
            .FetchType.Join( );
        References( x => x.Monument, "MonumentID" )
            .WithForeignKey( "MonumentID" )
            .FetchType.Join( );
        Map( x => x.YearStarted );
        Map( x => x.YearEnded );

The References calls required some Googling. They basically allow you to map a class to multiple tables.

Now to the Surveyor map. At first, I thought this required the use of HasManyToMany because, y’know, this is a many-to-many relationship. That’s not quite true. Yes, in one sense, a Surveyor has many Monuments and a Monument can have many Surveyors but in fact, a Surveyor isn’t directly related to Monument. It is related to MonumentAssignments. In a many-to-many relationship, this intermediate object is usually left out because it doesn’t have any meaning other than to relate two objects. In our case, it has a temporal meaning outside of just the relationship itself.

So now, our Surveyor has a many-to-one relationship with MonumentAssignment. Monument, in turn, also has a many-to-one relationship with MonumentAssignment. With that in mind, we can map our Surveyor class thusly:

public class SurveyorMap : ClassMap
    public SurveyorMap() {
        Id(x => x.ID, "SurveyorID");
        Map(x => x.CommissionNumber);
        Map(x => x.Surname);
        Map(x => x.GivenNames);
        Map(x => x.YearCommissionGranted);

        HasMany( x => x.Monuments )
            .Cascade.AllDeleteOrphan( )
            .WithKeyColumn( "SurveyorID" );

That’s it. In the end, it wasn’t nearly as complicated as it first seemed. Of course, two key ideas led to this (again, relative) simplicity:

  • MonumentAssignment is a domain concept in its own right
  • Surveyor has a many-to-one relationship with MonumentAssignment, *not* a many-to-many relationship with Monument

This post was brought to you by Colin Jack, Chad Myers, Shane Courtrille, James Gregory and the number 12.

Merry Christmas!

Kyle the Monumental

This entry was posted in NHibernate. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://kyle.baley.org Kyle Baley

    Yes, that’s how I would do it.

    This mapping does require a slight challenge to your intuition. At first glance, it seems obvious: A Surveyor has a collection of Monuments. But as soon as you attach the StartDate and EndDate to the table, it’s no longer true. You’re *not* interested in the collection of Monuments a Surveyor has. You’re interested in the specific assignment of the monument. That is, which particular monument *and* the period he or she used it. That’s a domain concept, even though it’s not a physical thing.

  • http://profiles.google.com/bgsosh Sosh Sosh

    Yes, I understood, but looking at the mapping definition it’s not clear at first glance.

    A question for you: How do you deal with situations where you want to check whether a particular monument is associated with a surveyor. Without the MonumentAsssignment middle man, it’s as easy as checking Monuments.Contains(someMonument) on the Surveyor object, but I can’t see how you would do it this simply in this case.

    Two possibilities strike me, but neither are very nice:
    a) mapping an additional read only direct collection of Monuments (without the intermediary MonumentAssigment), using the same existing table data as source, but this seems pretty nasty.
    b) Looping through all the MonumentAssignments and checking them manually. This seems a little old school.
    Am I overlooking a more elegant LINQ way of doing it?



    Ah, I think this works

    Monuments.Any(x => x.Monument == someMonument);

    I seriously need to brush up on my LINQ!

  • http://kyle.baley.org Kyle Baley

    After re-reading it and making some assumptions about what the images originally looked like, yes, I would. FluentNHibernate handles many-to-many relationships easily but in this case, it’s actually two many-to-one relationships because of the extra data attached to the relationship.

    The Monuments property is a collection of MonumentAssignments. That is, for each Surveyor, we want to know the collection of Monuments as well as the dates that Surveyor used the Monument.

  • http://profiles.google.com/bgsosh Sosh Sosh

    Thanks – have the same issues myself (not monuments mind you). One thing I’d say is that it’s not entirely clear on first glance what type your Monuments collection is (one initially assumes type ‘monument’) – the code for Surveyor class would be useful along with SurveyorMap. Also, I notice this post is quite old now – would you do it differently if you had to tomorrow?

  • http://codebetter.com/members/kylebaley/default.aspx Kyle Baley

    No can do. Afraid I don’t have them anymore.

  • Matt

    images broken, plz fix

  • http://blog.timtyrrell.net Tim

    Thank you, you are a freaking life saver.

  • Levin

    Hi,I followed your blog on my demo app,but got some questions:
    1,In the practical use,when i wanna assign a monument to a surveyor,should i use surveyor.Monuments.Add(mAssignment),then invoke session.Save(surveyor)?Or directly do session.Save(mAssignment)?
    2,If i take the session.Save(surveyor) choice,my unit test always complains “Don’t dereference a collection with cascade=”all-delete-orphan””

    Am i missing sth?Please help.

  • http://creedcultcode.blogspot.com Dale Smith

    “My brother likes to say it’s the second oldest profession in the world”

    Your brother’s a farmer?

  • Kyle Baley

    @Mike: That sounds like something I encountered with another surveyor-related application. Details are here: http://codebetter.com/blogs/kyle.baley/archive/2008/03/20/table-per-concrete-class-in-nhibernate.aspx

    Apologies for the formatting. What I’m about to paste was generated by Fluent NHibernate.

    Here is the XML for Surveyors:
    < ?xml version="1.0" encoding="utf-8"?>

    And here it is for MonumentAssignment:

    < ?xml version="1.0" encoding="utf-8"?>

  • David Mc.

    Kyle – for those of us who are Fluent NHibernate challenged (aka “handicapped”), could you post the XML mapping that this generates?

  • http://devlicio.us/blogs/mike_nichols Mike

    Kyle, Thanks for sharing this.
    How would you do this if you had multiple implementations of Monument and were not able to use the table-per-hierarchy or joined-subclass strategy?

  • http://weblogs.asp.net/fbouma Frans Bouma

    “A pretty standard many-to-many relationship. But where this differs from almost all of the NHibernate examples you see is that there is data attached to the relationship. I.e. it’s not enough that we know Sandy McLean used monument TH, we want to know the period in which he and/or she used it.”
    It’s called an ‘objectified relationship’ (ref: http://www.orm.net) and in NIAM/ORM it’s typically defined as a relationship which is on itself an entity with attributes. An objectified relationship is always forming at least one m:n relationship.

    Sometimes it can be a bit hard to recognize them. For example ‘Order’ in Northwind could be seen as an objectified relationship for the Customer m:n Employee relationship. :)