Ayende @ Rahien

Refunds available at head office

NHibernate Mapping -

Sometimes, well known associations just don’t cut it. We sometimes need to be able to go not to a single table, but to a collection of table. For example, let us say that an order can be paid using a credit card or a wire transfer. The data about those are stored in different tables, and even in the object model, there is no inheritance association them.

From the database perspective, it looks like this:

image

As you can see, based on the payment type, we need to get the data from a different table. That is somewhat of a problem for the standard NHibernate mapping, which is why we have <any/> around.

Just to close the circle before we get down into the mapping, from the object model perspective, it looks like this:

image

In other words, this is a non polymorphic association, because there is no mapped base class for the association. In fact, we could have used System.Object instead, but even for a sample, I don’t like it.

The mapping that we use are:

<class name="Order"
			 table="Orders">

	<id name="Id">
		<generator class="native"/>
	</id>

	<any name="Payment" id-type="System.Int64" meta-type="System.String" cascade="all">
		<meta-value value="CreditCard" class="CreditCardPayment"/>
		<meta-value value="Wire" class="WirePayment"/>
		<column name="PaymentType"/>
		<column name="PaymentId"/>
	</any>

</class>

<class name="CreditCardPayment"
			 table="CreditCardPayments">
	<id name="Id">
		<generator class="native"/>
	</id>
	<property name="IsSuccessful"/>
	<property name="Amount"/>
	<property name="CardNumber"/>
</class>

<class name="WirePayment"
			 table="WirePayments">
	<id name="Id">
		<generator class="native"/>
	</id>
	<property name="IsSuccessful"/>
	<property name="Amount"/>
	<property name="BankAccountNumber"/>
</class>

Pay special attention to the <any/> element. Any <meta-value/> declaration is setting up the association between the type as specified in the PaymentType column and the actual class name that it maps to. The only limitation is that all the mapped class must have the same data type for the primary key column.

Let us look at what this will give us:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var order = new Order
	{
		Payment = new CreditCardPayment
		{
			Amount = 5,
			CardNumber = "1234",
			IsSuccessful = true
		}
	};
	session.Save(order);
	tx.Commit();
}

Which produces:

image

image

And for selecting, it works just the way we would expect it to:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var person = session.Get<Order>(1L).Payment;
	Console.WriteLine(person.Amount);
	tx.Commit();
}

The generated SQL is:

image

image

An interesting limitation is that you cannot do an eager load on <any/>, considering the flexibility of the feature, I am most certainly willing to accept that limitation.

Comments

josh
04/21/2009 04:28 AM by
josh

i don't like those scenarios, but I do like how nhib can handle it.

CaliCoder
04/21/2009 05:34 AM by
CaliCoder

nice post!

mark
04/21/2009 07:11 AM by
mark

one question about <any, if you map also the IPayment interface with for example the property Amount, there's a way to do queries using this property?

i mean that i have in the db 3 order

1 with CreditCardPayment an Amount = 100,

1 with CreditCardPayment an Amount = 50,

1 with WirePayment an Amount = 100,

and i want to do a query as

"select order o where o.Payment.Amount = 100"

it's possible using <any?

Ayende Rahien
04/21/2009 07:17 AM by
Ayende Rahien

Mark,

No, we cannot do that.

There is no way to actually create a SQL query that would do that.

Daniel Fernandes
04/21/2009 07:25 AM by
Daniel Fernandes

@Ayende

Isn't it a big limitation that from NH point of view a <any relationship is akin of having System.Object on one side ? This is pretty restrictive in term of querying logic.

Woudln't it be "fairly" easy to extend the <any mapping to have implicit typing instead of System.Object therefore enabling flexible polymorphic associations ?

Daniel

Ayende Rahien
04/21/2009 07:43 AM by
Ayende Rahien

Daniel,

Fairly easy? Not really.

Try extremely hard.

Just for kicks, try to write a SQL statement that can do that, they try to generalize this for the whole concept.

As for being able to query that, any is supposed to be used for special purposes, not for anything, and if you want to query that, just query it from the other side.

Daniel Fernandes
04/21/2009 08:03 AM by
Daniel Fernandes
@Ayende
  
  
The second I posted my comment I thought I was way off as I've got only small understanding of the NHibernate codebase :)
  
The reason why I think 
<any association as great is that it allows true polymorphic associations between entities instead of having to be limited by the fact that associations have to be between a table to another table.
  
  
Daniel
>
Craig Quillen
04/21/2009 02:04 PM by
Craig Quillen
How does it know which of the 
<column is the FKey and which is the descriminator?  By their data type?
>
Kyle Baley
04/21/2009 02:10 PM by
Kyle Baley

What if you wanted to have a collection of payments on the order?

Ayende Rahien
04/21/2009 02:12 PM by
Ayende Rahien

Craig,

By their positions

The first is the type, the second is the id.

Ayende Rahien
04/21/2009 02:13 PM by
Ayende Rahien

Kyle,

Wait for tomorrow :-)

jdn
04/21/2009 04:09 PM by
jdn

"select order o where o.Payment.Amount = 100"

"There is no way to actually create a SQL query that would do that."

There isn't? What am I missing? The query is pretty trivial. Or do you mean there isn't a way to tell NHibernate how to do it?

Ayende Rahien
04/21/2009 04:26 PM by
Ayende Rahien

Jdn,

Please try to formulate that query.

jdn
04/21/2009 04:29 PM by
jdn

select o.* from [Order] o

left join CCPayments on

CCPayments.ID = o.PaymentID

and o.PaymentType = 'CC'

left join WirePayments on

WirePayments.ID = o.PaymentID

and o.PaymentType = 'Wire'

where

CCPayments.Amount = 100

or

WirePayments.Amount = 100

Given the data Mark listed (3 orders, 2 in CC, 1 in Wire), this returns the correct two rows.

What am I missing?

Ayende Rahien
04/21/2009 04:33 PM by
Ayende Rahien

Jdn,

a) you forgot that you may have different properties for different entitites. that is, not all entities would have Amount, how do you handle that?

b) try to generalize this scenario for a 10 entities scenario, see what happens

jdn
04/21/2009 04:38 PM by
jdn

I didn't 'forget' anything. I was responding to

"There is no way to actually create a SQL query that would do that."

to Mark. It is possible to write the query for what he asked about.

To 'generalize' it, you would dynamically generate the SQL (which would require other work, of course) that needed to be executed.

Note, I'm not saying this is a good thing, but it is possible.

Ayende Rahien
04/22/2009 06:20 AM by
Ayende Rahien

jdn,

I meant, there is no generic way to do it in SQL that has acceptable performance margins

jdn
04/22/2009 11:30 PM by
jdn

Well, okay. I did not get that from the comment(s) that you made, you are usually very clear and direct in what you say. I don't see how one gets from 'there is no way to actually create a SQL query' to (paraphrasing) 'you can't get good performance from a SQL query in a generic way.' Apologies if I misconstrued anything you said, never my intention.

I believe that you can do it in SQL with acceptable performance margins, but that is highly dependent on context and how one defines 'acceptable', so I won't harp on that here.

Thanks.

Comments have been closed on this topic.