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!

Table-per-concrete-class in NHibernate?

Normally when I have a problem I can’t solve right away, I bang away at it until I have a half-assed solution that I can post here so that others can provide a better solution in the comments. It’s how I’ve found about four methods for testing RedirectToAction, got tips for automating my deployments, and figured out the ConventionController. Quite frankly, I post here so that someone else can show me a better way to do things ’cause I’ve learned long ago my way ain’t it.

This time, however, I’ve searched and experimented and have come up empty. So there will be no facade that I know what I’m doing this time. I’ll just plain admit that I can’t get it to work. I suppose I could bitch and complain about how hard this is. That seems to be a common theme I’ve seen in many other places when someone can’t figure something out. Whine about how it should be easier, then have some kid in tenth grade post a simple yet elegant solution in the comments within the first five minutes. Alas, I don’t have the pride to assume that it’s someone else’s fault I can’t get my work done.

‘Spose I should get to the problem at hand lest I lose potential problem-solvers.

I have a domain object called Job. It contains a collection of Location objects. A Location is an abstract class with two implementations: RuralLocation and UrbanLocation. The heart of my problem is: How do I represent this in an NHibernate mapping?

As defined in the documentation, I’m using a "table-per-concrete-class" strategy. I’ll listen to arguments that it’s not appropriate but consider this: Locations are value objects. They have no identity in and of themselves. A sample location would be NE-15-1-29-1W, which is a rural location (Northeast quarter section, section 15, township 1, range 29, 1 west of the prime meridian). There is no benefit to storing this as an individual entity in the database and attaching it to many jobs. Locations are essentially bandied around like money when you listen to the clients talk.

The reason I’m not considering a "table-per-class" or "table-per-subclass" hierarchy is because the two classes have exactly zero fields in common. So in the former, we’d have a table where half the fields would be null in every single row. And in the latter, we’d have a Location table containing two fields, an ID and a LocationType, and nothing else. Maybe having such a table isn’t too bad but it sure feels like I’m altering the schema for the sake of NHibernate.

So for better or for worse, I have a "table-per-concrete-class". This is easy enough to represent in mapping files for each class individually as per the NHibernate documentation. Where I’m stuck is how to link these concrete classes back to the Job mapping file. Back when I had only the one location type, the mapping looked so:

<bag name="Locations" cascade="all-delete-orphan">
      <key column="JobID" />
      <one-to-many class="Trilogy.Gunton.Model.RuralLocation" />
</bag>

Fairly straightforward and pretty much right out of the docs. But now, I need to map to an abstract Location class that has no representation in NHibernate. The docs mention the <any> element and Ayende has something similar but for the life of this hillbilly, the answer still eludes.

So to my insightful and, I pray, generous readers: <ahem>….help!

### UPDATE ###

Sorry folks, forgot to include relevant diagrams. Database diagram and class diagram are included below. The Locations property on Job is an IList<Location>. Click for larger versions:

Gunton_DB Gunton

 

Kyle the Unassisted

This entry was posted in Featured, NHibernate. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Daniel Fernandes

    I came across the same issue in the past.
    And it was the same kind of entity, here it was Country if I remember right.

    AFAIK your problem is due to a mismatch between object and database models, here the object model forces you down the route of having to potentially use inheritance or object composition so that you can create sets that are reachable using IList collections that are easily configured through mapping.

    I believe I did end up using composition but if I could I wouldn’t anymore because you can end up with something as ugly as :
    class RepublicanCountry [
    private Country {get set;}
    }
    So not only it’s ugly in terms of object model (RepublicanCountry.Country ??) it may make it unnatural when you have to add such a country to a collection and check you don’t have duplicates of RepublicanCountry because here we would delegate to Country for identity check.

  • Henke

    Hmm, I should get my own personal blog so that I also get instant feedback from a multitude of skilled developers. :p

    Heck, since I’m also a one-developer team, I’ll just go by table-per-subclass then. ActiveRecord doesn’t like PKs in subclasses but removing them gives a cryptic NHibernate exception: ‘param named “property” is required for foreign id generation strategy’…

    Forth comrades.

  • Kyle Baley

    @Bil:

    If I’m going to be true to the domain language (I’m calling it that because I can’t spell ubiquitous), Locations has to be a single collection of heterogeneous location objects. That’s how the business thinks of them. Having two collections of locations is not going to make my job easier, nor will it endear me to them if that has to bleed over into the UI anywhere.

    My eventual approach was to switch to a table-per-subclass strategy. One table for Location, and one each for RuralLocation and UrbanLocation. The subclass tables don’t have their own auto-generated IDs, instead it’s a one-to-one mapping to the Location table.

    There were a few reasons for this:
    1) They do actually have something in common: UTM coordinates and Latitude/Longitude. This is pretty much how locations are defined. The Rural/Urban thing is just a different representation of them that has been in place since before the days of computers. (Trivia: Land surveying is one of the oldest professions in the world, some say the second oldest)
    2) There won’t be any duplicate IDs in the locations
    3) I was able to get it work in about a half hour

    Pragmatism trumps all when you’re a one-developer team

  • http://weblogs.asp.net/bsimser Bil Simser

    Did you get an answer or decide on an approach here? I agree with Frans in that the way you’ve split your data out and the entities they map to have nothing in common. If you’re married to this implementation then I guess a discriminator and an abstract type will work but I would consider re-thinking the model first, driving it out with tests around the behavior (are they entities or value objects?) and then deal with the db mappings (and db identity if that’s important).

  • http://www.tobinharris.com/blog Tobin Harris

    Just thinking out loud, but could you chose composition over inheritance here?

    What
    if Location is responsible for containing UTM and Lat/Long coordinates,
    but will also contain a Rural or Urban component part (with a business
    rule to make sure it doesn’t have both).

    Location <>—–> 0..1 UrbanInfo

    Location <>—–> 0..1 RuralInfo

    Location *<——- 1 Job

    class Location

    {

      RuralInfo _ruralInfo;

      UrbanInfo _urbanInfo;

      UtmCoords _utmCoords;

      LatLongCoords _latLongCoords;

    }

    One
    possible benefit of this is that it matches your business domain – it
    sounds like the business sees locations as one entity containing either
    urban or rural specific info. This model reflects that quite well.

    Hoefully,
    you can still find Urban/Rural locations explicitly by searching for
    any location that has a not null urban/rural component. And, you can
    still find all jobs done in an area?

    NHibernate Composition
    mappings would handle this well. You could store the rural or urban
    info all in one table, keeping it simple. You could also move the
    UrbanInfo and RuralInfo out to into separate tables, although I doubt
    this is nessasary because there’s a 0..1 mapping to from location to
    Urban/Rural info.

    On the down side, the UrbanInfo and RuralInfo
    do feel like a good candidate for inheritance, but I think I’d only
    consider this if there were significant *behavioural* differences
    between them.

    Thoughts?

  • http://www.dotnettricks.com Craig Bowes

    Kyle,

    I’m more or less agreeing with Frans here–they seem to be very different entities except for something that may happen in the future. Is there a reason for them to share the same base class? One thing i read in “Object Thinking” (Great book) was that objects should be defined by behavior, not data. So is there some common behavior or functionality for both types of locations? The way your clients talk about them makes me think that there might be in which case your on the right track.

    craig

  • Kyle Baley

    Actually, I left out the discussion on whether they should be stored in separate collections on purpose. The fact is: they are identical to the business. They don’t think in terms of rural locations and urban locations. In fact, the names “rural” and “urban” are my terms for how I’m storing them internally. To them, it’s just a location and the context is gleaned in the way they talk about them.

    Furthermore, a Job can have any number of both rural and urban locations, so it’s not even enough to define a job as having only one type of location.

    Simply put, suggesting to the business that I separate them into two distinct collections would require a radical shift in the way they think about things.

    For all intents and purposes, locations are the key to the application. The major benefit they’ve received from the original (classic ASP) version is that they can search on a location and it will pull up all jobs they’ve done in the area. Then they can determine if they actually need to send out a crew and equipment to actually survey the area so there is a tremendous cost savings in being able to search effectively.

    Another thing I left out (since I just thought of it now) is that there eventually will be some commonality. Every location will have a set of UTM coordinates and a set of latitude/longitude coordinates. Which off the top of my head further suggests that a table-per-subclass model is the way to go.

    So I’ve kind of talked myself out of the problem presented here. I’ll leave it as an academic exercise though mostly so that I can save face. Over at the NHibernate group (http://groups.google.com/group/nhusers), Ayende expands on his comment here. He doesn’t come out and say it, but I translate his answer as: it *can* be done, but *shouldn’t*.

  • http://www.tobinharris.com/blog Tobin Harris

    As Frans pointed out, there *appears* to be very little commonality between the two types of location. I suspect there’s a good reason why you have chosen this model, but it’s just not obvious. What are the behaviours of locations – what do they actually do? Are they merely reference data, or are they passed to external systems or subsystems?

  • http://blog.7-clan.org Martin Linkhorst

    I would have to agree with Frans here. What is the benfits of this design? You are going to have to cast to the specific Location implementation each time you need to access it. I wrote a blog-post about this issue http://blog.7-clan.org/, feel free to comment on it :)

  • http://blog.symbiotic-development.com Symon Rottem

    Actually, I’d probably recommend implementing an ICompositeUserType and doing this as a component mapping since, as you said, the Location is not really an entity but is instead a value type.

    You could have your location table have a discriminator column and have the user type class handle instantiating the correct class based on the discriminator.

    See section 5.2.3. – Custom value types in the NHibernate Docs.

    The method you would focus on to populate your custom value type would bet NullSafeGet in the interface mentioned above – there’s an example you can look at on Mike Nichol’s blog here: http://geekswithblogs.net/opiesblog/archive/2006/08/05/87218.aspx

    Of course, it’s possible that these are not the droids you’re looking for… :)

    Symon.

  • http://weblogs.asp.net/fbouma FransBouma

    I fail to see why both locations have to be of the same type, they’re simply different entities. The problem is: if you see them both as ‘locations’ you have a problem consuming: job.Locations, which can contain instances of two distinct types, which have nothing in common.

  • http://www.ayende.com/Blog/ Ayende Rahien

    Kyle,
    This is handled using NHibernate’s

  • Kyle Baley

    @James:

    Sorry, the downside of a wandering mind. I started creating a diagram and plum forgot. Hope I can be descriptive enough.

    There are three tables: Job, RuralLocation, and UrbanLocation. Here are the relevant columns:

    Job: JobID (int – primary key)
    RuralLocation: RuralLocationID (int – primary key), JobID (Foreign key)
    UrbanLocation: UrbanLocationID (int – primary key), JobID (Foreign key)

    After thinking about it some more, I believe a table per subclass strategy with a discriminator might actually make more sense. Otherwise, the SQL to get a list of locations for a job is: SELECT * FROM RuralLocation WHERE JobID = x UNION SELECT * FROM UrbanLocation WHERE JobID = x

    Which is kinda freaky-lookin’. Plus there is the issue with IDs not being unique within any given collection of locations.

    Still if someone has an answer….

  • http://www.tobinharris.com/blog Tobin Harris

    Can you post up the class code for both types of location, showing behavioural and data differences?

  • http://blog.jagregory.com James Gregory

    I’m leaning towards some kind of discriminator, but I think if you could show us the db schema, that’d make things a bit clearer.