Ayende @ Rahien

Refunds available at head office

NHibernate Mapping -

I am not going to talk about all the options that NHibernate has for collections, I already did it for <set/>, and most of that are pretty similar. Instead, I am going to show just the unique stuff about NHibernate’s <list//>. While <set/> is an unordered collection, of unique elements, a <list/> is a collection of elements where the order matter, and duplicate elements are allowed (because there is a difference between item A in index 0 and index 4).

Let us look at what it means by looking at the class itself:

public class User
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Phone> EmergencyPhones { get; set; }
}

And the mapping:

<class name="User" table="Users">
    <id name="Id">
        <generator class="hilo"/>
    </id>
    <property name="Name"/>
    <list name="EmergencyPhones" table="UsersToEmergencyPhones" cascade="all">
        <key column="UserId"/>
        <index column="Position"/>
        <many-to-many column="PhoneId" class="Phone"/>
    </list>
</class>

Note that I am using <many-to-many/> mapping, if I wanted to use the <one-to-many/> mapping, the PhoneId would be located on the Phones table. Now, let us see how we are working with it:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    session.Save(new User
    {
        Name = "a",
        EmergencyPhones = new List<Phone>
        {
            new Phone{Name = "Pop", Number = "123-456-789"},
            new Phone{Name = "Mom", Number = "456-789-123"},
            new Phone{Name = "Dog", Number = "789-456-123"},
        }
    });
    tx.Commit();
}

This will produce the following SQL:

image

And now, what happen if we want to read it? Here is the code:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    var user = session.Get<User>(id);
    foreach (var emergencyPhone in user.EmergencyPhones)
    {
        Console.WriteLine(emergencyPhone.Number);
    }
    tx.Commit();
}

And the generated SQL:

image 

What happen if we update the list? Let us see the code (anyone is reminded in CS 101?):

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    var user = session.Get<User>(id);
    user.EmergencyPhones.Add(new Phone{Name = "Cat", Number = "1337-7331"});
    var temp = user.EmergencyPhones[2];
    user.EmergencyPhones[2] = user.EmergencyPhones[0];
    user.EmergencyPhones[0] = temp;
    tx.Commit();
}

Which result in:

image

Beware of holes in the list! One thing that you should be careful about is something removing an entry from the list without modifying all the other indexes of the list would cause… problems.

If you were using Hibernate (Java), you would get an exception and that would be it. NHibernate, however, is much smarter than that, and will give you a list back, with one caveat. Let us say that we have the following data in the database:

image

Note that we don’t have a row in position #2. When we load the list, we will get: [ phone#3, phone#2, null, phone#4 ]

A lot of the time, we are processing things like this:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    var user = session.Get<User>(id);
    foreach (var emergencyPhone in user.EmergencyPhones)
    {
        Console.WriteLine(emergencyPhone.Number);
    }
    tx.Commit();
}

When this code will encounter a hole in the list, it is going to explode in an unpleasant way. You should be aware of this possibility and either protect yourself from it or make sure that whenever you delete from a list, you will rearrange the list so there are no holes in it.

Comments

DaRage
06/02/2009 01:37 AM by
DaRage

I didn't understand why in the second query, UserToEmergencyPhones is left joined to the Phones table. Shouldn't it be an inner join?

Eric Hauser
06/02/2009 03:05 AM by
Eric Hauser

Hibernate returns null for a missing entry as well.

Krzysztof Kozmic
06/02/2009 08:13 AM by
Krzysztof Kozmic

when did you add the --statement #x

My build does not have it yet ;)

Ayende Rahien
06/02/2009 09:51 AM by
Ayende Rahien

Try build 294, it is there, when you select multiple statements.

Tigraine
06/02/2009 10:21 AM by
Tigraine

Thanks for the article. Very interesting.

I just wonder why there is no "figure it out" property in the mappings. Something that instructs NH to also fix the missing indexes (resulting in more work for the DB but saving the developer some trouble).

Also is there a way to persist two collections with the same ordering?

Like OrderLineItems and OrderLineText, both maybe deriving from the same base but being different...

(I had two lists on the Order, which I could mix to their common base but kept seperated for some calculations)

greetings Daniel

Ayende Rahien
06/02/2009 10:28 AM by
Ayende Rahien

Tigraine,

Because in general, having NH doing something like that for you can be bad. There is a meaning to null values.

But broadly, it is because it is not the responsibility of NHibernate to do so. If you want something like that, you don't need a list, you need an ordered set.

As for the second thing, ordering is for a single collection. You can have polymorphic association using list, however.

Frans Bouma
06/02/2009 11:25 AM by
Frans Bouma

This only works if there's just 1 thread of the app modifying the position parameter. I.o.w.: a table lock is required. I don't see that as a valuable feature, if people have to maintain the index anyway, they can also add a position property to the entity and be done with it.

Tigraine
06/02/2009 11:32 AM by
Tigraine

Thanks for the reply Ayende.

I'll look into the ordered set..

Ayende Rahien
06/02/2009 11:35 AM by
Ayende Rahien

Frans,

Nope, it doesn't. If two threads are messing with it, one of them is going to get a concurrency violation error, as expected.

DaRage
06/02/2009 02:39 PM by
DaRage

Hi Guys,

Again regarding the left join. I still don't understand. I was assuming if the foreign key is nullable (ie the phoneId in UserToEmergencyPhones table) than the query will be left join. but if the phoneId column is not nullable it will an inner joint. Or does NHibernate assumes that there will be orphane phoneIds and do a left join all the time. doesn't orphane phone ids mean data corruption and should be protected against by using foreign keys?

Frans Bouma
06/03/2009 09:51 AM by
Frans Bouma

@Ayende: agreed, I overlooked the where clauses in the UPDATE statements.

Umair
06/03/2009 11:18 PM by
Umair

In your post you mentioned,

"if I wanted to use the <one-to-many mapping, the PhoneId would be located on the Phones table."

I feel there is a mistake and it should be "UserId would be located on the Phones table"

Comments have been closed on this topic.