NHibernate mapping after a normalisation

Question

Please help me - I'm new to NHibernate and I cannot seem to find what I'm looking for.

I have two tables in a database: Fund and FundBalance. A Fund can have many FundBalances and a FundBalance has only one Fund.

In C#, there is only the FundBalance class. Columns from the Fund table joined with columns from the FundBalance table need to be mapped onto properties of the FundBalance class.

For example, the Fund table contains the FundName property and the FundBalance table contains the AvailableBalance property. These two tables are joined and the result of the join needs to be mapped to the FundName and AvailableBalance properties on the FundBalance class.

The question: how do I do this with NHibernate? Bonus: How do I specify the mapping using FluentNHibernate?

One solution that I thought of was to create a view in the database, but I would prefer it if the mapping can be done purely using NHibernate.

Answer 1

What is the reason that you don't have a Fund class? I'd rethink if this is really wise.

But if you want to stay on that route, the subselect attribute might help (sorry I have this from the Hibernate docs not NHibernate, but I hope they are similiar enough: http://www.hibernate.org/hib_docs/core/reference/en/html_single/#mapping-declaration-class)

I'd think this should work for for select, update and delete. But I have no idea how insert should work (independent from Hibernate) who should be responsible to decide if you need a new Fund record, or update one (which?) when you don't have the Fund table properly represented in your object model.

An alternative would be (as you mentioned yourself) to create a view in the database and use "instead of trigger" if your database supports this.

Answer 2

Why don't you use the façade pattern, if all you need is a common class for both tables? Map Fund and FundBalance normally, create a (unmapped) new class as a interface for both entities. Simple and no "magic" involved whatsoever.

Answer 3

This should be done using Hibernates support for inheritance. Hibernate can model families of related classes in several different ways but one is a table for the base class and another for each of the subclasses to hold their additional properties. On load all the columns are automatically loaded into the designated subclass - so create a fake inheritance structure with just the one class and it should do exactly as you describe.

Answer 4

If you only need to read, but not write in the Fund, you could use a formula. For example:

That way you will be able to work with FundBalances as normal, but not with Funds

Answer 5

As I've asked in the comments; how does this FundBalance class exactly looks like ? What goes in there ? Can you do something with the <join table> element in the NHibernate mapping ?

For example: http://ayende.com/Blog/archive/2007/04/24/Multi-Table-Entities-in-NHibernate.aspx

Answer 6

Hi,

You don't need to use a view to solve your problem. You just need to be specific on the join when you make the mapping on the FundBalance Table. If my understanding is good you want to have your FundBalance class more complete and have some properties from the Fund Table.

Try this:

<class name="FundBalance" table="FundBalance" lazy="true">
     <id name="Id" column="FundBalanceId" unsaved-value="0">
      <generator class="native"/>
     </id>

     <property name="FundBalance" not-null="true" length="80"/>

     <join table="Fund">
      <key column="FundId"/>
      <property name="FundName"/>
     </join>
</class>

For your FundBalance entity class the code will be:

public class FundBalance
{   
     private long _Id;
     public virtual long Id
     {
      get { return _Id; }
      set { _Id = value; }
     }

     private decimal _FundBalance;
     public virtual decimal FundBalance
     {
      get { return _FundBalance; }
      set { _FundBalance= value; }
     }

     private string _FundName;
     public virtual string FundName
     {
      get { return _FundName; }
      set { _FundName= value; }
     }
}

If you need more sample and explanation for the problems of mapping mutiple table for one enties try this links:

https://svn.sourceforge.net/svnroot/nhibernate/trunk/nhibernate/src/NHibernate.Test/Join/ http://ayende.com/Blog/archive/2007/04/24/Multi-Table-Entities-in-NHibernate.aspx

I give you the HBM mapped files, for fluent nhibernate i don't know if you can use the keywords JOIN but bassicly it's the same problem.

Hope this helps.