Ayende @ Rahien

It's a girl

Multi Table Entities in NHibernate

A while ago I posted about the ability to map n tables to a single entity in the Entity Framework. I didn't like it then, and I quoted from the Hibernate documentation that discourage this behavior:

We consider it an essential element of good object model design that the object model be at least as granular as the relational model. If the original data modeller decided that certain columns should be split across two different tables, then there must have been some kind of conceptual justification for that. There is no reason not to also make that distinction in the object model.

I still believe that this statement is true, except... I just run into an issue with my model, I have a case where I am importing data from another database, and I need to add additional data to it. I could add additional columns to the primary table, but that would make the import process much more complex than I would like it to be. I would have liked to make it work by splitting the data by table, rather than by columns.

With that in mind, I headed to NHibernate's JIRA, and found this issue about the problem. Conveniently, a patch was supplied as well.

A big thanks for Karl Chu for making all the work of porting the functionality from Hibernate. I love Open Source.

At any rate, you can now map several tables into a single entity in NHibernate, you can get the full details here (the new tests), but let us walk through a simple one first.

(Image from clipboard).png 

Name and sex are defined in the Person table, but everything else is defined on the Address table, we map it like this:

<class name="Person">

       <id name="Id" column="person_id" unsaved-value="0">

              <generator class="native"/>

       </id>

 

       <property name="Name"/>

       <property name="Sex"/>

 

       <join table="address">

              <key column="address_id"/>

              <property name="Address"/>

              <property name="Zip"/>

              <property name="Country"/>

              <property name="HomePhone"/>

              <property name="BusinessPhone"/>

       </join>

</class>

Obviously address_id is a FK to person_id (not the best names for them, come to think about it). Trying to load a person would cause this SQL query (reformatted):

SELECT

     p.person_id,

     p.Name,

     p.Sex,

     a.Address,

     a.Zip,

     a.Country,

     a.HomePhone,

     a.BusinessPhone

FROM dbo.Person p inner join dbo.Address a

     on p.person_id=a.address_id

There is quite a bit more that it can do (optional joins, etc), and you can check it out at the tests.

Note: this is on NHibernate trunk, so it won't be in the 1.2 release, which is currently in feature-freeze.

Comments

Frans Bouma
04/25/2007 03:12 PM by
Frans Bouma

Still the scope is limited to PK-PK related tables. Nothing wrong with that, but that's also the scope of the current EDM state, as there's currently no way to insert fields in table B if the PK of B isn't in the entity at hand which is for example identified by the PK of table A, not related to B.

Ayende Rahien
04/25/2007 03:21 PM by
Ayende Rahien

If you have a PK-FK assoication, I would say that this is another entity, and not the same one.

that's also the scope of the current EDM state

?? Can you explain? Do you mean the entity framework?

Frans Bouma
05/02/2007 07:50 AM by
Frans Bouma

?? Can you explain? Do you mean the entity framework?

Yes. At the moment they can't update/insert/delete entities on multiple tables if they're not inheritance based or don't have the pk/fk fields inside them. WHich is logical, because that's also the reason why you can't update a view based on multiple tables in sqlserver/oracle etc.: they always update just 1 table.

Ayende Rahien
05/02/2007 09:22 AM by
Ayende Rahien

Hm, the assoication in NHibernate for multi table entities is using a column in the sub-table that matches the PK of the entity.

I am not sure that I follow why this is a limitation, since I can't quite figure how you can have a single entity in multiply tables without assoication between them.

Mark
05/03/2007 04:41 AM by
Mark

Is this something that can be done when the tables are in different databases?

A specific example might be that most of a salepersons information (address etc) is stored in an HR database however other information (general ledger code?) might be stored in a Financial systems database. Can these be brought together into one single entity.

To push the example even further, is there any way to make one of the sources a web service rather than a database table?

Ayende Rahien
05/03/2007 06:06 AM by
Ayende Rahien

Mark, you can use DBLink to make the disparate database works, so yes.

For using Web Services, the answer is that technically, you may be able to do it.

Practically, it is not really a good solution, at least not in my opinion.

Comments have been closed on this topic.