Ayende @ Rahien

Refunds available at head office

NHibernate Mapping -

<many-to-any/> is the logical extension of the <any/> feature that NHibernate has. At the time of this writing, if you do a Google search on <many-to-any/>, the first result is this post. It was written by me, in 2005, and contains absolutely zero useful information. Time to fix that.

Following up on the <any/> post, let us say that we need to map not a single heterogeneous association, but a multiple heterogeneous one, such as this:

image

In the database, it would appear as:

image

How can we map such a thing?

Well, that turn out to be pretty easy to do:

<set name="Payments" table="OrderPayments" cascade="all">
	<key column="OrderId"/>
	<many-to-any id-type="System.Int64"
			meta-type="System.String">
		<meta-value value="CreditCard"
			class="CreditCardPayment"/>
		<meta-value value="Wire"
			class="WirePayment"/>
		<column name="PaymentType" 
			not-null="true"/>
		<column name="PaymentId"
			not-null="true"/>
	</many-to-any>
</set>

Now, let us look at how we use this when we insert values:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var order = new Order
	{
		Payments = new HashSet<IPayment>
        {
        	new CreditCardPayment
        	{
        		Amount = 6,
                CardNumber = "35434",
                IsSuccessful = true
        	},
            new WirePayment
            {
            	Amount = 3,
                BankAccountNumber = "25325",
                IsSuccessful = false
            }
        }
	};
	session.Save(order);
	tx.Commit();
}

This will produce some very interesting SQL:

image

image

image

image

image

I think that the SQL make it pretty clear what is going on here, so let us move to a more fascinating topic, what does NHibernate do when we read them?

Here is the code:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	var order = session.Get<Order>(1L);
	foreach (var payment in order.Payments)
	{
		Console.WriteLine(payment.Amount);
	}
	tx.Commit();
}

And the SQL:

image

image

image

image

As you can see, this is about as efficient as you can get. We load the order, we check what tables we need to check, and the we select from each of the tables that we found to get the actual values in the association.

True heterogeneous association, not used very often, but when you need it, you really love it when you do.

Comments

Nathan
04/22/2009 04:53 AM by
Nathan

I must be missing something: why does the Order table have PaymentType and PaymentId columns?

Ayende Rahien
04/22/2009 05:25 AM by
Ayende Rahien

Nathan,

How else could it have a reference for payment?

Ayende Rahien
04/22/2009 05:26 AM by
Ayende Rahien

Oh, no, sorry.

Order have those columns because (not seen in this post), Order also have an any association to payment.

Kenny Eliasson
04/22/2009 11:06 AM by
Kenny Eliasson

Really appreciate the work with these series, gonna print them out and make a little book of it for my colleagues. Thanks!

John
04/22/2009 03:08 PM by
John

I had no idea this mapping even existed! I always read your posts because I'm constantly either learning something new or they force me to think through issues that I've never considered or from a viewpoint that I hadn't previously taken. Thanks for the education!!!

Yazid
04/23/2009 09:52 AM by
Yazid

Hello,

I would like to take to this opportunity to ask a question about one-to-many. I have two classes:

GeneralInformation and famousPlacesInLondon

public class GeneralInformation

{

    private int id;


    public virtual int Id

    {

        get { return id; }

        set { id = value; }

    }


    private IList

<famousplacesinlondon famousPlacesOutLondon;

    public virtual IList

<famousplacesinlondon FamousPlacesOutLondon

    {

        get { return famousPlacesOutLondon; }

        set { famousPlacesOutLondon = value; }

    }

}

public class FamousPlacesInLondon

{

    private int id;


    public virtual int Id

    {

        get { return id; }

        set { id = value; }

    }

    private string link;


    public virtual string Link

    {

        get { return link; }

        set { link = value; }

    }

    private string title;


    public virtual string Title

    {

        get { return title; }

        set { title = value; }

    }


}

}

My mappings are as follows:

<hibernate-mapping
<class
<id
<generator

<bag
<key
<one-to-many

<hibernate-mapping
<class
<id
<generator

<property
<property

If I fill the GeneralInformation class with data and do a save using NHibernate, everything gets inserted into the appropriate tables, except the ForeignKey GeneralInformationId. What is wrong?

TIA

Yaz

Ayende Rahien
04/23/2009 10:24 AM by
Ayende Rahien

Yazid,

If this is not a question about this post, please use the nhusers mailing list for that

Jeroen
04/23/2009 11:52 AM by
Jeroen

Can you explain the first SQL statement (i.e. insert into orders(PaymentType, PaymentId) values (NULL, NULL))?

Jeroen
04/23/2009 12:08 PM by
Jeroen

It already makes sense. Had to check your previous post to understand the table structures.

Ayende Rahien
04/23/2009 05:23 PM by
Ayende Rahien

Jeroen,

The problem is that it is using the same model from the previous post, so Payment property is empty, so it is putting null there.

NAC
04/26/2009 02:38 AM by
NAC

Is there any way you can write HQL queries that cross this to-any relationship --- even if at query time you know the type of the entity on the other end?

I.e. the casting equivalent of select ... from foo where (KnownType)foo.anyRelation.number=12 ?

Or no need to do that?

N
Ayende Rahien
04/26/2009 07:40 AM by
Ayende Rahien

NAC,

You have to traverse the association from the other side

Comments have been closed on this topic.