Quoting column names with NHibernate and PostgreSQL

Question

I have started to use NHibernate 3.0 and PostgreSQL for a small project, so far the ride has been a little rough due to the NHibernate site being down and I'm sure this answer is on their website somewhere.

I have a database that has these two columns(of course there is more in the real table):

int ID
String Feature

now I'm using a FluentNHibernate to do the mapping so something like this:

public class MyEntityMap: ClassMap<MyEntity>
{
    public MyEntityMap()
    {
        Id(x => x.ID);
        Map(x => x.Feature);
    }
}

and a LINQ query to get out the data

var strucs = from str in session.Query<MyEntity>()
             where str.ID < 5
             select str;

The query will generate the proper SQL statement, well sort of. The problem is, because I have uppercase letters in my column names you have to wrap them in quotes but the generated SQL code looks something like this:

SELECT this_.ID as ID0_0_, this_.feature as feature0_0_, 
       FROM "MyEntity" this_ WHERE this_.ID < 5

Where the columns don't have quotes around them. If I run this I get a "column this_.id" is not found etc.

Does anyone know how I can get NHibernate to wrap the column names in quotes?

EDIT: I can't lowercase the column names as there are some columns that a third party program needs to be all in uppercase.

I tried adding .ExposeConfiguration(cfg => cfg.SetProperty("hbm2ddl.keywords","auto-quote") but it doesn't seem to do anything.

Answer 1

I realize this is a rather old question but for others that may end up here you could also implement a custom naming strategy, when setting up your session factory you would add:

Fluently.Configure(new Configuration()
    .SetNamingStrategy(new CustomNamingStrategy()))...

In CustomNamingStrategy, implement the interface NHibernate.Cfg.INamingStrategy and for all the methods basically just return the parameters with quotes around them.

Answer 2

Since hbm2ddl.keywords is supposed to do this for you and it not working, I would suggest you to grab nhibernate source, and debug it with your project.

Problably you can start with a breakpoint on SessionFactoryImpl.cs line 171

if (settings.IsAutoQuoteEnabled)

Take a look at method public static void QuoteTableAndColumns(Configuration configuration) on SchemaMetadataUpdater.cs

Hope that helps.

Answer 3

Don't use uppercase characters in table/column names. That will solve this problem and make ad-hoc querying less painful as you won't have to keep quoting them.