+972 52-548-6969
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:
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.
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
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.
If you have a PK-FK assoication, I would say that this is another entity, and not the same one.
?? 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.
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.
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?
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.
Comment preview