Phillip Haydon

There seems to be a growing trend now with these Micro ORM’s, at least that is what I see with ServiceStack.OrmLite, which is the ability to persist properties of an object as a JSON, rather than in separate tables.

Usually with a Relational approach you would create a ‘Customer’ table, ‘Address’ table, and most likely shove the Phone Numbers under separate columns of the customer for ‘HomePhone’ and ‘Mobile’.

This means we are limited to two types of phone numbers, and require joining or querying for the addresses.

Do we really need separate columns for phone numbers? Do we really need to persist the addresses in another table?

One problem I see with putting addresses into it’s own table, is the temptation to relate them to an Order (assuming this is some sort of eCommerce system) when really there is no relationship between a customer’s address, and the address on an order.

Really, the order should have it’s own address, otherwise you can never delete or update an address on a customer, and you can’t delete the customer. However I digress and this is a topic for another day.

Example by OrmLite

This post is about how to do it with NHibernate, but I’m going to start by showing the example in OrmLite, then use the same example for NHibernate.

Note: OrmLite by default persists as JSV-format (JSON+CSV) rather than JSON. I’m currently unaware of any way to change it to be JSON.

The customer is the root aggregate, and has his own Addresses and Phone Numbers, and is modelled like so:

Note: The attributes are for OrmLite and are not used by NHibernate, and the properties have been made virtual for NHibernate.

So using OrmLite if we insert some data like so:

We can query for that data and we get the following results:

(Click on the image to see it fully)

As you can see PhoneNumbers are stored like so:

{Mobile:+61 411 122 34,Home:+61 256 3234}

And Addresses are stored like:

{Home:{Line1:Unit 31,Line2:102 Banana Street,ZipCode:2009,State:NSW,City:Sydney,Country:Australia}}

Now if we query for that data back out:

You can see we get all the information back out again, no problem! This stuff is built into OrmLite which is awesome, but how do we do it in NHibernate?

Custom NHibernate UserType

So now we want to do this in NHibernate. This is a UserType I wrote a long time ago, well… I re-wrote it recently but wrote the initial idea a long time ago, and I’ve personally never seen anything similar in NHibernate.

I’ve put this on Gist – https://gist.github.com/1936188

It’s a generic class so that I can return the type of object back, and uses json.net to handle the serialization/deserialization of the object to JSON.

Now when mapping the properties we can specify the CustomType like so:

The example I’m using has two dictionaries of values. But if you were mapping a single type such as a single ‘Address’, you would just specify the type as above, without the Dictionary, .CustomType<Blobbed<Address>>()

The full mapping for the Customer is:

Now we can insert some data:

Again, the data is inserted:

(Click on the image to see it fully)

Only this data is serialized as JSON rather than JSV-format.

PhoneNumbers:

{"Mobile":"+64 27 551 443","Home":"+64 9445 1982"}

And Addresses:

{"Home":{"Line1":"13/187 Jones St","Line2":null,"ZipCode":"0629","State":null,"City":"Auckland","Country":"New Zealand"}}

If we query for the data:

Just like OrmLite we get the object back just the same.

Things to note:

The custom user type in it’s current state does not handle inherited objects. If you want to support it, then you can modify it to serialize and deserialize using the type information.

This can be done like so:

And

What this will do is include the type information on the serialized object:

{"$type":"System.Collections.Generic.Dictionary2[[NHibernateJsonTest.AddressType, NHibernateJsonTest],[NHibernateJsonTest.Address, NHibernateJsonTest]], mscorlib","Home":{"$type":"NHibernateJsonTest.Address, NHibernateJsonTest","Line1":"13/187 Jones St","Line2":null,"ZipCode":"0629","State":null,"City":"Auckland","Country":"New Zealand"}}

Why would you want to do this

To avoid unnecessary tables and mappings. The example above is a perfect example where we can remove the need for a table on data that is never searched against and is never related to anything else.

There’s no need for joins or adding additional columns. We just map the object or collection to a single column and we are done, and our code knows no different.

Also, schema changes in blobs don’t need DDL updates. If your model changes, you add new properties, or remove old properties, the blob will get updated next time you update your data. No more scripting off schema changes.

The guys at MindScape have released their next version of NHibernate Designer, which now supports Fluent NHibernate and a whole heap of new features.

They also dropped the price down to just $99!

I recommend checking it out

http://www.mindscapehq.com/blog/index.php/2011/10/19/nhibernate-designer-2-is-here/

http://www.mindscapehq.com/products/nhdesigner

This is part two, to my post about Table Inheritance using Discriminators, in this post I just want to demonstrate the outcome when the sub-classes have their own properties, or possibly a property that maps to the same column.

First thing however is Mark Perry pointed out in the comments that specifying a value for the baseClassDiscriminator will force it to store the value in the database as an INT rather than a a VARCHAR.

This will create the table with an INT like so:

Maybe INT is too big however, maybe we only want a SMALLINT? That will give us 32k sub-classes…

But even that is too many, so maybe we need TINYINT, that gives us 0-255. I doubt you would ever have 255 sub-classes, so we can specify the discriminator as a byte.

And that gives us:

Nice, much better.

Not to mention when querying now, it uses the INT value rather than the number being used as a string like before:

So the next thing I want to show is what happens when we add a property to 1 sub-class and not the other. (or properties than exist in 1, and properties that exist in the other)

Give the same example as my previous post, I’ve added one property to the class, a ‘Url’ property.

This will get created as a normal column in the database.

When we insert now, a LinkShare and a Text wallpost:

The link share one will include the Url.

The ‘Url’ column must be null, or have a default value assigned to it so that when inserting a Text wallpost, the column doesn’t need to be specified.

If we query for the base class:

This will query for all columns:

Just like it did before. No changes, likewise if we query for just the Text wallpost, it will not include the ‘Url’ column:

Results in:

If we query for the LinkShare wall post:

This results in the ‘Url’ column being selected:

So NHibernate is efficient in that it only queries for what it actually needs. If you extend your sub-classes out to have a couple of properties each then they will only query for the required fields for that sub-class.

It is possible for sub-classes to share properties. For example if introduced a new sub-class, MovieShare, which has a VideoUrl, as well as a SiteUrl property:

We can map the classes like so:

When the table is created, ‘Url’ column is only created once:

Now when we insert:

The insert will share the same ‘Url’ column for both the LinkShare, and the MovieShare:

And querying is still as it was before. No changes.

One really important thing to remember is. You cannot change a type from 1 type to another, meaning you cannot change a LinkShare to a MovieShare. Any sub-class you create should never have any reason to change, if for some reason it DOES change, you should delete it, and create a new one.

By that I mean delete the object, and insert a new one of the specified sub-class. While it is possible to use native SQL to change the discriminator value, there’s no way to do it in HQL, Criteria, LINQ, or QueryOver, because it’s just wrong. If it needs to change, you probably need to re-think your domain and persistence.

Next post will be about Table per Sub-Class mapping.

So a long time ago James Kovacs posted a article about get/load polymorphism with NHibernate, which was cool and all but I always wanted to know how to map it all in Fluent NHibernate. I worked it out at the time but I guess it’s taken me 7 months to write it down.

First up is using a single table, mapping them to multiple classes, this is done using a discriminator. Fluent NHibernate calls this “table-per-class-hierarchy strategy”, which doesn’t make sense to me. But meh.

So I’m going to begin with the following classes to demonstrate this:

So if I was to select all WallPost’s it would give me instances of LinkShare, and Text wall posts.

These classes are really basic at the moment.

First we map the Wall Post:

As you see, there is a ‘DescriminateSubClassesOnColumn’ method which specifies a column. This is what NHibernate uses to figure out which type of Sub Class to create.

Next we need to map the Sub Classes.

Here I have specified the Discriminator Value for each sub class. If I save a TextWallPost, it will save the value ‘1’ to the column ‘PostType’. Then when it pulls it from the database, it uses this value to decide the SubClass to create.

The generated table looks like:

Now if I insert a couple of posts:

I’ve done nothing more than create instances of the classes I want, and commit them to the database, the SQL that is generated looks like:

So when it generates the SQL it puts the discriminator value in for us.

If I select all ‘WallPost’ from the database:

It just does a normal select:

Nice right? What’s cool is if we look at the list of results we get, we get instances of WallPost, and instances of LinkShareWallPost back:

Now suppose we wanted to select JUST Link Shares.

NHibernate will actually write the query to select only WallPosts that specify the Discriminator type defined in our Sub Class mapping.

So if we look at the results in VS:

Very nice stuff indeed.

There’s future posts on this stuff to come

So happy. NHibernate 3.0 just got released.

http://nhforge.org/blogs/nhibernate/archive/2010/12/05/nhibernate-3-0-released.aspx

Now to wait for Fluent to be updated and a repository to be shoved on NuGet.

I spend ages trying to find some documentation or tutorial on how to use HiLo with FluentNHibernate after reading:

NHibernate: Avoid identity generator when possible

SCOPE_IDENTITY() sometimes returns incorrect value

NHibernate POID Generators revealed

Identity: The never ending story – BTW they are remaking The NeverEnding Story. Bastards!!!

As it turns out, it’s really easy to setup. I figured out two ways of doing it, the HiLo table having a single row, with each column for each table ‘Hi’. Or a row for each table.

I setup a really simple example with the following table.

‘ProductId’ is set to be int/primary key, but identity is false, since we don’t want the database generating the id.

The two HiLo tables are like so:

Row Per Table

Column Per Table
(shows only a single column, but for each table you would add a new column)

Then all you need to do is setup the Fluent Mappings.

Row Per Table

So for Row Per Table, the mapping for Product would look like this:

HiLo takes the parameters (TableName,ColumnName, MaxLo, Where). Where is an override that we will use since we need to specify the row to return. So if we insert a single row into our table:

We pass in our where clause as "TableKey = ””Product””", so that it knows which row to get the NextHi from.

For each new table we would just add a new row and a default NextHi value. Every time the SessionFactory is created it would grab the NextHi value and increment it by 1.

Column Per Table

Column Per Table is very similar, except we don’t need the where parameter, we only need to specify the column to look at. Assuming we were using the Column Per Table with the HiLo2 table, our mapping would look like this:

We would need to add a single row to our table like so: