NHibernate SQL creation causes error

Question

I have 2 classes named Order and Orderrow. I use NHibernate to get a join on it.

When running NUnit to test the query, I got an ADOException:

Logica.NHibernate.Tests.NHibernateTest.SelectAllOrdersFromSupplierNamedKnorrTest:
NHibernate.ADOException : could not execute query
[ SELECT this_.OrderId as OrderId1_1_, this_.CreatedAt as CreatedAt1_1_, this_.ShippedAt as ShippedAt1_1_, this_.ContactId as ContactId1_1_, customer2_.ContactId as ContactId0_0_, customer2_.LastName as LastName0_0_, customer2_.Initials as Initials0_0_, customer2_.Address as Address0_0_, customer2_.City as City0_0_, customer2_.Country as Country0_0_ FROM Order this_ inner join Contact customer2_ on this_.ContactId=customer2_.ContactId ]
[SQL: SELECT this_.OrderId as OrderId1_1_, this_.CreatedAt as CreatedAt1_1_, this_.ShippedAt as ShippedAt1_1_, this_.ContactId as ContactId1_1_, customer2_.ContactId as ContactId0_0_, customer2_.LastName as LastName0_0_, customer2_.Initials as Initials0_0_, customer2_.Address as Address0_0_, customer2_.City as City0_0_, customer2_.Country as Country0_0_ FROM Order this_ inner join Contact customer2_ on this_.ContactId=customer2_.ContactId]
  ----> System.Data.SqlClient.SqlException : Incorrect syntax near the keyword 'Order'.

When analyzing the SQL that has been created by NHibernate, I notice that the Order class is corrupting the SQL statement, because ORDER BY is an internal keyword in SQL.

This is the created SQL in NHibernate:

SELECT this_.OrderId as OrderId1_1_, this_.CreatedAt as CreatedAt1_1_, this_.ShippedAt as ShippedAt1_1_, this_.ContactId as ContactId1_1_, customer2_.ContactId as ContactId0_0_, customer2_.LastName as LastName0_0_, customer2_.Initials as Initials0_0_, customer2_.Address as Address0_0_, customer2_.City as City0_0_, customer2_.Country as Country0_0_ FROM Order this_ inner join Contact customer2_ on this_.ContactId=customer2_.ContactId

I altered it in SQL Server 2008 Management studio like this:

SELECT this_.OrderId as OrderId1_1_, this_.CreatedAt as CreatedAt1_1_, this_.ShippedAt as ShippedAt1_1_, this_.ContactId as ContactId1_1_, customer2_.ContactId as ContactId0_0_, customer2_.LastName as LastName0_0_, customer2_.Initials as Initials0_0_, customer2_.Address as Address0_0_, customer2_.City as City0_0_, customer2_.Country as Country0_0_ FROM [Order] this_ inner join Contact customer2_ on this_.ContactId=customer2_.ContactId`

I added brackets to the table name Order (like this: [Order]) and it is fixed.

But how do I get this fixed in NHibernate ? Is there a mapping XML file instruction for it to get this done ?

(using VS2008 SP1, SQL Server 2008 SP1, NHibernate 2.0.1 GA)

Answer 1

I think if you put the quotes ("[" and "]" in SQL Server, or whatever quotes your DB supports) in your mapping file, hibernate will quote the object names when it generates queries.

(Maybe post your mapping file, so we can take a look)

Answer 2

See this, section "5.3. SQL quoted identifiers". Yoo basically need this:

<class name="Order" table="`Order`">