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 off the unique features of NHibernate’s <map/>, and then some how crazy you can get with it.

Let us start with the simplest possible scenario, a key/value strings collection, which looks like this:

public class User
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IDictionary<string, string> Attributes { get; set; }
}

And the mapping is pretty simple as well:

<class name="User" table="Users">
    <id name="Id">
        <generator class="hilo"/>
    </id>
    <property name="Name"/>

    <map name="Attributes" table="UserAttributesAreBoring">
        <key column="UserId"/>
        <index column="AttributeName" type="System.String"/>
        <element column="Attributevalue" type="System.String"/>
    </map>
</class>

How are we using this? Let us start by writing it to the database:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    id = session.Save(new User
    {
        Name = "a",
        Attributes = new Dictionary<string, string>
        {
            {"height", "1.9cm"},
            {"x","y"},
            {"is_boring","true, very much so"}
        },
    });
    tx.Commit();
}

Which give us:

image

And when we want to read it, we just use:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    var user = session.Get<User>(id);
    Console.WriteLine(user.Name);

    foreach (var kvp in user.Attributes)
    {
        Console.WriteLine("\t{0} - {1}", kvp.Key,
            kvp.Value);
    }

    tx.Commit();
}

And the SQL:

image

This simple mapping is quite boring, so let us try to do something a bit more interesting, let us map a complex value type:

public virtual IDictionary<string, Position> FavoritePlaces { get; set; }

// Position is a value type, defined as:
public class Position
{
    public decimal Lang { get; set; }
    public decimal Lat { get; set; }

    public override string ToString()
    {
        return string.Format("Lang: {0}, Lat: {1}", Lang, Lat);
    }
}

Which we can map using:

<map name="FavoritePlaces" table="UsersFavoritePlaces">
    <key column="UserId"/>
    <index column="PlaceName" type="System.String"/>
    <composite-element class="Position">
        <property name="Lang"/>
        <property name="Lat"/>
    </composite-element>
</map>

Using composite-element, we can map value types (types that have no identifiers, and only exists as part of their parent). We can use it using the following code:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    id = session.Save(new User
    {
        Name = "a",
        FavoritePlaces = new Dictionary<string, Position>
        {
            {"home", new Position{Lang = 10,Lat = 94.4m}},
            {"vacation house", new Position{Lang = 130,Lat = 194.4m}}
        },
    });
    tx.Commit();
}

And that give us:

image

By now you are probably already are familiar with what reading the FavoritePlaces collection would look like, so we won’t bother. Instead, let us look at a more complex example, what happen if we want the key of the map to be a complex value type as well? Let us look at this:

public virtual IDictionary<FavPlaceKey, Position> ComplexFavoritePlaces { get; set; }

// FavPlaceKey is another value type
public class FavPlaceKey
{
    public virtual string Name { get; set; }
    public virtual string Why { get; set; }

    public override string ToString()
    {
        return string.Format("Name: {0}, Why: {1}", Name, Why);
    }
}

We can map this collection as:

<map name="ComplexFavoritePlaces" table="UsersComplexFavoritePlaces" >
    <key column="UserId"/>
    <composite-index class="FavPlaceKey">
        <key-property  name="Name"/>
        <key-property name="Why"/>
    </composite-index>
    <composite-element class="Position">
        <property name="Lang"/>
        <property name="Lat"/>
    </composite-element>
</map>

And using this is pretty simple as well:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    id = session.Save(new User
    {
        Name = "a",
        ComplexFavoritePlaces = new Dictionary<FavPlaceKey, Position>
        {
            {
                new FavPlaceKey
                {
                    Name = "home",
                    Why = "what do you mean, why?"
                },
                new Position
                {
                    Lang = 123,
                    Lat = 29.3m
                }
            }
        },
    });
    tx.Commit();
}

Which results in:

image

But that still isn’t over, I am happy to say. So far we have dealt only with value types, what about using entities? You can probably guess:

id = session.Save(new User
{
    Name = "a",
    CommentorsOnMyPosts = new Dictionary<User, Post>
    {
        {anotherUser, post1}
    }
});

With the mapping:

<map name="CommentorsOnMyPosts" table="UserCommentorsOnPosts">
    <key column="UserId"/>
    <index-many-to-many column="CommentorUserId" class="User"/>
    <many-to-many class="Post" column="PostId"/>
</map>

Giving us:

image

But how are we going to read it?

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    var user = session.Get<User>(id);
    Console.WriteLine(user.Name);

    foreach (var kvp in user.CommentorsOnMyPosts)
    {
        Console.WriteLine("\t{0} - {1}", kvp.Key.Name,
            kvp.Value.Title);
    }

    tx.Commit();
}

The resulting SQL is quite interesting:

image

When we load the commentors, we join directly to the posts (value), but we have to use a different query to load the user. This is something that you should be aware of, when using a <map/> with entity key.

I would like to point out that I have still not covered all the options for <map/>, there are even more options (Although, IDictionary<K, IList<V >> is not something that is possible to map, for the problematic SQL statements that would be required to do so).

And, of course, you are free to mix all types as you would like, entity key and value type, or a string to an entity, or a complex value type to an entity.

Happy (n)hibernating… :-)

a

Comments

Alex Yakunin
06/03/2009 07:24 AM by
Alex Yakunin

I'm curious, what kind of object will represent the original Dictionary after the materialization in NHibernate?

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

Aren't these objectified relationships where the relationship with user with post is actually a new entity (NIAM/ORM)? Isn't it then better to map this as a true entity and use 1:n relationships between them? (as objectified relationships are always m:n so the two entities involved in m:n have a 1:n with the objectified relationship (is intermediate entity).

Rafal
06/03/2009 09:54 AM by
Rafal

Yes, finally! The relational nirvana! Let's create one BIG table:

create table AllTheData

(

table_name varchar,

key varchar,

column_name varchar,

column_value varchar

)

and map the whole object model to this table. NHibernate certainly is able to handle that.

Ayende Rahien
06/03/2009 10:49 AM by
Ayende Rahien

Alex,

I don't understand the question

Ayende Rahien
06/03/2009 10:55 AM by
Ayende Rahien

Frans,

Probably, I am using these posts to show NH features, not to discuss good OO design.

To be frank, I couldn't think of any real world scenario where this would be needed :-)

Rafal,

Um, you can do that, sure, but then I am going to come in a year or two and have to fix that, so let avoid this...

DaRage
06/03/2009 02:12 PM by
DaRage

Is the various statement execution batched?

Ayende Rahien
06/03/2009 02:19 PM by
Ayende Rahien

DaRage,

No, they are not the same statement, so cannot be batched

Alex Yakunin
06/03/2009 02:27 PM by
Alex Yakunin

I don't understand the question

Well, it's easy: I'm talking about the actual type of the provided object. If it is a regular Dictionary, there are lots of obvious issues.

Ayende Rahien
06/03/2009 02:37 PM by
Ayende Rahien

It is not an Dictionay, is is an impl of IDictionary

Alex Yakunin
06/03/2009 03:02 PM by
Alex Yakunin

That's good then ;)

So I can expect that user.CommentorsOnMyPosts.Count will run a "select count(*) query", and user.CommentorsOnMyPosts["x"] will lead to a query returning a single record?

Ayende Rahien
06/03/2009 03:08 PM by
Ayende Rahien

Not always, that depend on the lazy strategy that you use.

if you use lazy="extra", count(*) should work, but the indexer probably wouldn't

Alex Yakunin
06/03/2009 04:15 PM by
Alex Yakunin

What about the same for other collections? Just curious - because I'm comparing this to DO. We have just EntitySet <t, but it closes most of possible scenarios:

  • If it's "paired" (paired = reflects the association (pair) from another side) to a reference property in another Entity, it acts like dictionary allowing to resolve it by its key.

  • If it's paired to another EntitySet, it "reflects" it from another side of the association.

  • If it is unpaired, in fact it is anyway paired to Left property of an instance of special generic entity like ~ Pair <tleft,> describing its items. So in general, it is always paired to something.

Above cases allow to implement dictionary and set with complete lazy loading (that's what I've asked). I'm not sure about necessity to implement IList - i.e. it's good to have this opportunity, but getting 1M updates on removal of item with index 0 from a list with 1M items is actually quite impractical (am I right, or NHibernate uses some kind of trick here?).

Alex Yakunin
06/03/2009 04:17 PM by
Alex Yakunin

Err... The blog "ate" by square brackets ;) There was Pair(of TLeft, TRight, TVariator).

Ayende Rahien
06/04/2009 11:24 AM by
Ayende Rahien

Most of the time, people use a set or a bag to represent collections.

Lazy="extra" will handle the most common cases, which is Count and Contains, but not anything else.

If you are using a list to store 1M items, you are doing something wrong.

NH has options to deal with that, but they are very manual, and even from straight SQL perspective, they are bad (an update statement that touch 1M rows is BAD).

Alex Yakunin
06/04/2009 07:12 PM by
Alex Yakunin

From my point of view even the update of 10-100 rows just to raise up an item is bad. 1M was obviously an example of the worst case. Personally I won't use List at all ;)

Concerning "but not anything else" - well, but there is really nothing else to cache ;)

Thanks for the answers!

Simon
06/05/2009 03:10 PM by
Simon

Is it possible to query the keys or values in the mapped dictionary?

Say we have a userprofile where we want to be able to add more and more properties, like your attributes example.

profile.Entries.Add("HasCats","true");

Is it possible for me to do a hql or criteria query which filters based on HasCats="true" ?

perhaps a bad example, but you get the idea?

Ayende Rahien
06/05/2009 03:17 PM by
Ayende Rahien

simon,

It is something like:

select 1 from Profile p join p.Entries e

where index(e) = 'HasCats' and e = 'true'

Simon
06/05/2009 05:11 PM by
Simon

Aah, thanks a bunch! searched quite alot in the docs, but did´t see that index(e) stuff anywhere! perhaps it´s a rarely used feature :)

Thomas
06/22/2009 05:55 AM by
Thomas

Is there a way to map to a text column in SQLServer instead of nvarchar(length) ?

I want the value component to be unresitricted

Thomas
06/22/2009 06:18 AM by
Thomas

In case others have the same issue as me and got here googling:

index type = System.String, and a column node which specifies sql-type(nvarchar(4000)

element type = System.String, and a column node which specifies sql-type nvarchar(MAX)

Note that nvarchar(max) cannot be used as a DB key, and thus not not in the index column sql type.

Senthil
07/20/2009 04:22 PM by
Senthil

Is there a problem with the new build. I implemented the Map after read your blog . Everthing worked as a expected. Recently i updated NHiberrnate Code base to Build 2.1.0.CR1 (rev4578) Mapping is not working properly.

I reterivied an entity without any update i called save. Save operation deletes all records in the Dictonary. I tried so many ways i coulde not able to solve. why is Index function is not supported for Many to Many.

Ayende Rahien
07/21/2009 06:52 AM by
Ayende Rahien

Please ask this question (including mapping) in the NHUsers mailing list

Comments have been closed on this topic.