Geek Answers Handbook

Creating NHibernate SQL fails

I have 2 classes called Order and Orderrow. I use NHibernate to join it.

When I started NUnit to verify the request, I received an ADO exception:

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 parsing the SQL created by NHibernate, I noticed that the Order class distorts the SQL statement because ORDER BY is an internal keyword in SQL.

This is the generated 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 changed it in SQL Server 2008 Management Studio as follows:

 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 name of the Order table (for example: [Order]), and it is fixed.

But how do I install this on NHibernate? Is there an XML file for display for this?

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

+4
source share
4 answers

I think if you put quotation marks ("[" and "]" in SQL Server or any quotation marks that your database supports) in your mapping file, hibernate will indicate the names of objects when generating queries.

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

+6
source

See this , section "5.3. Identifiers with SQL Codes". Yoo basically needs this:

 <class name="Order" table="`Order`">
+6
source

In Fluent NHibernate add

 .Column("`Order`");

to fix this error.

+5
source

When using conventions, you can add the following:

 public class TableNameConvention : IClassConvention, IClassConventionAcceptance { public void Accept(IAcceptanceCriteria<IClassInspector> criteria) { criteria.Expect(x => Check(x)); } private bool Check(IClassInspector x) { return String.IsNullOrWhiteSpace(x.TableName) || x.TableName.Equals("`{0}`".Args(x.EntityType.Name)); } public void Apply(IClassInstance instance) { instance.Table("`" + instance.EntityType.Name + "`"); } }

To fix the problem with reserved table names and reserved column names (for example, From), you can use the following:

 public class ColumnNameConvention : IPropertyConvention, IPropertyConventionAcceptance { public void Apply(IPropertyInstance instance) { instance.Column("`" + instance.Property.Name + "`"); } public void Accept(IAcceptanceCriteria<IPropertyInspector> criteria) { criteria.Expect(c => Check(c)); } private bool Check(IPropertyInspector inspector) { //walkaround: //this convention causes problems with Components - creates columns like Issue`Id` so we apply it only to entities var type = inspector.EntityType; if (!(type.IsSubclassOf(typeof (Entity)) || type.IsSubclassOf(typeof (GlossaryEntity)))) { return false; } return true; } }
0
source

More articles:


All Articles