Fluent-NHibernate, PostgreSQL and Identifiers

In PostgreSQL identifiers for tables, columns, etc. are case sensitive. The problem is, though, that when you access PostgreSQL through the .NET data provider (e.g. Npgsql) and don’t double-quote the identifiers, they will be interpreted as lower case. As a result PostgreSQL may tell you that it doesn’t know table ‘MyTable’ as the query is sent to PostgreSQL as ‘mytable’, which is a different identifier than ‘MyTable’.

When you build your SQL queries yourself this is not a major issue. Just add the double quotes. It becomes more of a challenge when you want to use Fluent-NHibernate.

I searched the internet but couldn’t find a solution that worked for me. For example one answer at Stack Overflow suggested to make all identifiers lower case, e.g. have table, column names, etc lower case. While this may work in some cases it doesn’t work in others. Changing the database schema was not an option in my case.

Others (e.g. here) recommend the use of FluentConfiguration.ExposeConfiguration(cfg => cfg.SetProperty("hbm2ddl.keywords","auto-quote") but according to several sources it doesn’t seem to work properly or at all. This solution didn’t work for me either.

Fabio Maulo describes the official programmatic way for NHibernate to enable quoting tables and columns as follows:

SchemaMetadataUpdater.QuoteTableAndColumns(configuration);

I couldn’t get this to work in combination with Fluent-NHibernate either. NHibernate.Dialect reported a System.NotSupportedException:

A first workable option is providing the identifiers via the domain mappings. Let’s look at an example for this approach:

public class User {   
   public virtual int Id { get; private set; }
   public virtual string Name { get; set; }   
   public virtual string Password { get; set; }
}

A simple mapping for this class including specifying the names using double-quotes looks like this:

public class UserMapping : ClassMap<User> {
   public UserMapping() {
      Table("\"User\"");
      Id(x => x.Id).Column("\"Id\"");
      Map(x => x.Name).Column("\"Name\"");
      Map(x => x.Password).Column("\"Password\"");
   }
}

This works but has the draw back that you have to specify the names in every single case. Although a one-off, this could still be quite some work if you have a large database schema with over a thousand tables. I wanted to have something simpler, something that would have the logic in a single place.

And I didn’t want to modify Fluent-NHibernate or NHibernate sources either. Instead I wanted to use the official interfaces.

The solution that worked for me was implementing the INamingStrategy interface from the NHibernate.Cfg namespace. Before I show you the implementation, here is how you can use it:

public static ISessionFactory CreateSessionFactory() {
   var rawConfig = new Configuration();
   rawConfig.SetNamingStrategy(new PostgresNamingStrategy());
   var fluentConfiguration = Fluently.Configure(rawConfig)
      .Database(PostgreSQLConfiguration.PostgreSQL82
                  .ConnectionString(ConnectionString))
      .Mappings(m => m.FluentMappings.AddFromAssemblyOf<User>())
      .BuildConfiguration();
   return fluentConfiguration.BuildSessionFactory();
}

This adds only a small amount of additional code to the creation of the session factory. First we create a raw NHibernate.Configuration() object (line 2) and set the naming strategy (line 3). From thereon I can use the fluent interface by passing the raw Configuration object as the parameter to Fluently.Configure() (see line 4). Note that to come into effect the naming strategy must be set before any mappings are added to the configuration.

As a result of setting the naming strategy you can simplify the mapping to:

public class UserMapping : ClassMap<User> {
   public UserMapping() {
      Id(x => x.Id);
      Map(x => x.Name);
      Map(x => x.Password);
   }
}

The need to specify quoted column names is gone. Equally we don’t need to provide the quoted table name anymore.

And here is the implementation of the INamingStrategy interface:

internal class PostgresNamingStrategy : 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);
   }
}

Note that in some cases you may have to remove single quotes first before you add double quotes, e.g. when an identifier is a reserved name. See implementation of the private method DoubleQuote().

27 May 2011: Update this article with actually working code. Thanks for the feedback from various people.

Disclaimer: Source code is provided “as-is”. Use at your own risk. In your environment this solution may need to be adapted or may work at all. The configuration I used for my experiments was PostgreSQL 9.0 running on 64 bit Windows 7, Npgsql 2.0.11.0, Fluent-NHibernate 1.2, and Visual Studio 2010.