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.

Hyddan · Accepted Answer · 2012-12-04 18:34:47Z

5

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.

answered Dec 4, 2012 at 18:34

Hyddan

1,33716 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rafael Mueller · Accepted Answer · 2010-07-30 14:36:54Z

3

+250

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, 2010 at 14:36

Rafael Mueller

6,1133 gold badges26 silver badges28 bronze badges

4 Comments

Nathan W Over a year ago

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 Over a year ago

Thanks for the point in the right direction, I implemented my own quick GetDataBaseSchema for PostgreSQLDialect and it seems to be working alright now.

Egor Pavlikhin Over a year ago

@Nathan, could you share the solution please?

Newbie Over a year ago

FYI, the code in SchemaMetadataUpdater only adds quotes to "reserved" words for your RDBMS. All other words will remain un-quoted.

araqnid · Accepted Answer · 2010-07-26 09:47:56Z

0

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, 2010 at 9:47

araqnid

135k25 gold badges164 silver badges139 bronze badges

1 Comment

Nathan W Over a year ago

The only problem is another program (not made by me) has to have one of the columns in uppercase :(

Collectives™ on Stack Overflow

Quoting column names with NHibernate and PostgreSQL

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related