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.

score 4 · Answer 1 · answered Dec 04 '12 at 18:34

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.

score 3 · Accepted Answer · answered Jul 30 '10 at 14:36

3

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.

answered Jul 30 '10 at 14:36

Rafael Mueller

6,028
3
24
28

1

hmm it throws a error on `public override IDataBaseSchema GetDataBaseSchema(DbConnection connection)` because the `PostgreSQLDialect` doesn't override it to provide one, so the code at `if (settings.IsAutoQuoteEnabled)` throws an exception and they just ignore it. I wonder why nobody has implemented that method for PostgreSQL. – Nathan W Aug 01 '10 at 23:24
1

Thanks for the point in the right direction, I implemented my own quick GetDataBaseSchema for PostgreSQLDialect and it seems to be working alright now. – Nathan W Aug 02 '10 at 00:03
2

@Nathan, could you share the solution please? – Egor Pavlikhin Apr 04 '11 at 13:36
FYI, the code in SchemaMetadataUpdater only adds quotes to "reserved" words for your RDBMS. All other words will remain un-quoted. – Newbie Jun 08 '11 at 01:39

score 2 · Answer 3 · answered Jul 26 '10 at 09:47

2

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.

answered Jul 26 '10 at 09:47

araqnid

127,052
24
157
134

1

The only problem is another program (not made by me) has to have one of the columns in uppercase :( – Nathan W Jul 26 '10 at 22:03

Quoting column names with NHibernate and PostgreSQL

3 Answers3

Linked