Wednesday, November 05, 2008

NHibernate 2.0 and the join table syntax

I've noticed my post on Mapping a view with NHibernate is always quite popular. Resorting to mapping from a view has worked fine in the past as long as you only required read access, but if you wanted to build a complex object relational mapping that you could update you were in trouble...

Until Now

With NHibernate 2.0 recently being released we now have access to the join table syntax in our mapping documents. This means we can now build more sophisticated domain objects that map on to more than one table. NHibernate takes care of the multi-table inserts and updates behind the scenes making your life easier.

This has to be one of the most underrated features of NHibernate 2.0 as it finally allows you to break away from the one to one mapping between domain objects and your relational model which has ultimately caused compromises in the past.

I urge you to check out this and the other fantastic new features available in the latest NHibernate release.

Labels: , , ,

11 Comments:

Anonymous Anonymous said...

Indeed, this is a great feature, a brilliant workaround for lazy-loading vs. full-loading situations/conflicts.
I'm working with the Mapping.Attributes and am wondering whether you know where would I put the Join attribute and its children attributes. The only example I've seen on the web was this ayende's blog post

Any ideas?

9:33 pm  
Blogger James Fitzsimons said...

Hi, I don't use the attributes for mapping. However if it's similar to the Java implementation (and I'd bet it is) then this example will probably help you out a lot.

Sorry I can't give you more specific advice - hope that helps!

11:27 pm  
Anonymous Anonymous said...

ta!
I'll give it a go later on and will leave a note as soon as I find out how this is done (for future readers :)

9:52 am  
Anonymous Anonymous said...

Hello Again!
It seems that on Mapping.Attributes it works like this:
[Join(0, Table="Publisher")]
[Key(1, Column = "Id")]
[Property(2, Column = "Name")]

Yet there's something here I feel I'm missing. Silly old me, I understood this feature as a possibility to build 'light objects' that would be comprised of multiple tables. Apparently, the default column of the principal table that has been mapped is the ID column, which limits the possibilities.

For example, I thought creating a 'SmallItem' class, consisting a title of an item (Item table) and the name of its publisher (Publisher table). Obviously, the Item table has a PublisherID column, but I just can't find the way to tell NHibernate to take the PublisherID column rather than the ID one... Is this feasible?
Any suggestion would be highly appreciated :)
And merry Christmas, in case you're celebrating!

9:38 pm  
Blogger James Fitzsimons said...

Hi there, apologies for not replying sooner - Christmas has kept me busy!

Unless I misunderstand your example it would seem you are trying to map your composite object the wrong way around. i.e your primary table should be your Publisher table and your join table should be your item table.

In this case the Key of your "small item" class would be the primary key of the primary table e.g. the PublisherID as you desire.

Does that make sense, or did I misunderstand your example?

6:42 pm  
Anonymous Nieve said...

Right,
apparently I've misunderstood the functionality of this feature. A small thread on the google group nhusers threw some light on the whole thing.
It seems that the join attribute serves to join multiple tables sharing the same id. For what I wanted to do there's the join criteria (on hql), use a stored procedure and eventually, what IMO looks like the best solution, is to use and map a view. If we need to a light entity that uses a join for a quick and optimized bulk select, this is the way to do it.

The point I'm trying to make is that I can't think of a case where you'll need to make a bulk select on joined table which will then be followed by a bulk update. (But I'm not that experienced...)
If it's a case of a bulk select on joined table which will be followed by specified update/save- it'll make more sense to use a view and then two additional trips to the db to load and then save.

Hope I was clear enough.

8:48 pm  
Blogger James Fitzsimons said...

Ah, I think I see where the confusion lies now. Your publisher - item relationship will be one to many correct?

In that case you shouldn't be mapping that relationship using either a join table or a view. That is a simple collection mapping which as you say you can load completely either by using the join syntax in hql or ICriteria queries.

In this case you can then modify your collection as required and nhibernate will persist the changes (to the items you've modified) on flush.

The join table mapping allows you to combine multiple tables into a single domain object. It often makes sense in a relation model to break complicated entities out into multiple tables. However, in our code we may want to model those entities as a more complete, richer object. That is when you'd use a join table mapping.

Mapping a view is a hack that worked as an interim solution until the join table support was included. I don't think there would be many valid scenarios for mapping a view now that we have join table support in NHibernate.

HTH!

9:03 pm  
Anonymous Nieve said...

More than just helps... :)

but just another question here to clarify the issue: The join helps with one-to-many and many-to-many relations. But what happens when you have a many to one (many items to one publisher), and say the 'one' (publisher) is a very big table, consisting extremely heavy columns (blobs and texts etc'). Now for certain cases I'll need to load all the many (items) joined with only the publisher name.
A join is impossible and a join hql/criteria for a bulk select would take a lot more time than using a view, no? This is at the heart of the issue I was trying to address.

It's a pleasure discussing this with you, I must add :)

9:28 pm  
Anonymous Nieve said...

By 'A join is impossible' I meant <' join table="" '> is impossible since it's a many(items)-to-one(publisher).

(note to self- use the preview option)

9:34 pm  
Blogger James Fitzsimons said...

This is an interesting problem and one that won't be solvable in an elegant manner until there is lazy loading support for properties in NHibernate. In that case you could map your expensive columns (blobs, text, etc) as lazy loaded properties and to a large extent your problem is solved.

In this case I think the easiest way of solving this would be to add a formula column for the publisher name to you item class. In xml mapping terms it would look something like this:
<property name="PublisherName" formula="(select name from Publisher where PublisherId = this.PublisherId)" />

You would of course need to add a PublisherName property to your item class. This would mean that your bulk loads of your items have everything you need.

You would of probably want to keep the many-to-one relationship with the real publisher class as well, just make sure it's lazy.

One point of clarification with regard to your last comment. You mention that a hql/icriteria join would be more expensive than selecting from a view. Unless you are using materialized views (which is unlikely) then this is not the case. A join will be just as fast as selecting from a view (assuming you are selecting the same columns).

I'm off on holiday for a couple of days from tomorrow. Best of luck, let me know how you get on!

10:55 pm  
Anonymous Nieve said...

Just a quick note to thank you for all the priceless information- the best thing about this post is the fact that it summarises most (if not all) Join strategies in NHibernate. Hope this will serve future readers as it has served me :)

9:18 pm  

Post a Comment

<< Home