Using “serial” columns with Fluent NHibernate

So let’s go with PostgreSql and NHibernate again. But now lets look on some problems with adding new items into database.
I have this simple entity:

public class List
{
    public virtual int ListId { get; set; }
    public virtual string Name { get; set; }
}

As you can see id is integer type. On SQL Server we would set this column to use identity for obtaining new values
ListId INT IDENTITY(1,1)
which would indicate that we want to start from Id = 1 and then increment by 1 for each new record in table.
In PostgreSql we need to use serial data type, with does mainly the same – generates bigger value for each new record. So we would expect that inserting new entity with Id not set should work just fine – after all system will generate the value for us.
And that would be true if we generated database using Fluent NHibernate or at least wrote the mappings by hand (we would probably omit the error then). But when we only generate entities mappings we will soon find out that there is an issue with our code:

ERROR: relation “hibernate_sequence” does not exist

That is because by default (at least for PostgreSql) NHibernate uses its own id generator using some table in database (which I of course did not created in my database).
It took me some time to find out how to override this setting easily. First, I looked on all available Conventions I could use in Fluent.NHibernate library. There I found IIdConvention, which seemed like what I need. Then I struggled a bit looking for how to convent NHibernate that ids will be generated automatically. First hint I found on the internet was to tell him, that the type of Id column is not integer, but serial, that was pretty obvious.
After that I knew I need to modify generator used by NHibernate. Somewhere at back of my head Identity value was blinking, so I gave it a try, but to no avail. This would work for SQL Server probably, but not for PostgreSql. Then I tried Native, as it seemed reasonable – after all I want to use PostgreSql native identity mechanism. Yet, this did not work either. My last try was Increment (I found it somewhere on the internet to be honest). And that clicked.
So, below is my final implementation of identity convention.

public class IdConvention : IIdConvention
{
    public void Apply(FluentNHibernate.Conventions.Instances.IIdentityInstance instance)
    {
        if (instance.Type == typeof(int))
        {
            instance.CustomSqlType("Serial");
            instance.GeneratedBy.Increment();
        }
    }
}

PostgreSql and case sensitive columns

I’m currently working on some side project where I decided to use some other technologies than the standard set from work. I’ve chosen to have some fun with NHibernate (to have some comparision to Entity Framework) and PostgreSql (instead of one of SQL Server’s edition). With NH I’m using fluent approach given by Fluent NHibernate with Automappings.

With that kind of environment set up I started by defining some entities and configuring everything almost the standard way (some mapping overrides were required, but nothing special here, also some conventions were created to make everything generate appropriate mappings, nothing fancy). I’ve created sample database, checked that mappings generate correctly and set up some sample MVC3 application to show data to user on screen. And then the exception happened:

Column list0_.listid does not exist

And in fact, it is not present, since on database there is column ListId, with pascal case notation, and the same goes for the property in List entity. So why the query uses different name than the property name? PostgreSql by default creates all columns as case insensitive (by not wrapping them with quotes). Seems that by default NHibernate’s PostgreSql dll assumes the same behaviour and automatically converts all names to lower case. I however like having my columns case sensitive and thus named them “ListId” instead of listid.

Ok, so we know why the error occurs, by how to fix it? Quick googling around let me to this blog post where the solution is explained deeply. In short, I just needed to override naming strategy for NHibernate so that all identifiers are now wrapped with double quotes and made case sensitive this way. Simple solution for simple problem. Then just use newly created naming strategy in session factory configuration and we’re good to go.

public class PostgreSqlNamingStrategy : INamingStrategy
{
    public string ClassToTableName(string className)
    {
        return DoubleQuote(className);
    }
    public string PropertyToColumnName(string propertyName)
    {
        return DoubleQuote(propertyName);
    }
    public string TableName(string tableName)
    {
        return DoubleQuote(tableName);
    }
    public string ColumnName(string columnName)
    {
        return DoubleQuote(columnName);
    }
    public string PropertyToTableName(string className,
                                      string propertyName)
    {
        return DoubleQuote(propertyName);
    }
    public string LogicalColumnName(string columnName,
                                    string propertyName)
    {
        return String.IsNullOrWhiteSpace(columnName) ?
            DoubleQuote(propertyName) :
            DoubleQuote(columnName);
    }
    private static string DoubleQuote(string raw)
    {
        // In some cases the identifier is single-quoted.
        // We simply remove the single quotes:
        raw = raw.Replace("`", "");
        return String.Format("\"{0}\"", raw);
    }
}

And the strategy is used this way:

var rawConfig = new NHibernate.Cfg.Configuration();
rawConfig.SetNamingStrategy(new OrmConfiguration.PostgreSqlNamingStrategy());
var sessionFactory = Fluently.Configure(rawConfig)
...

Simple, isn’t it? Now it queries correctly, getting all the data I requested and using the columns I would like it to use. Great!

Let’s get it started

It’s time to finally start blogging. Hope this will help me organize things in my mind and put some of ideas and/or solutions into the web for future me and possibly for you, dear reader.

First let me introduce myself. I’m Jarek, software developer from Poland. I’m currently coding for last three years (as an employee), but I’ve been having some serious fun with code for past 10 years or so. I’m using mostly Microsoft’s technologies on .NET platform, like C#, VB.NET (unfortunately), Visual Studio and so on. Apart from that I like reading good (not necessarily software-related) book, watching good movie or tv show. And who doesn’t.

Yeah, that’s more than enough, let’s get it started with some more serious stuff soon.