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)

Andy White · Accepted Answer · 2009-05-07 06:37:26Z

7

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)

answered May 7, 2009 at 6:37

Andy White

88.8k48 gold badges178 silver badges212 bronze badges

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

4 Comments

Andy White Over a year ago

Just a note: I realize [ and ] are not SQL standards, but those are the default quotes used by SQL server.

Stefan Steinegger Over a year ago

In NH, the backtick are actually used for this, see Anton's answer. This is the database independent NH syntax.

Gaurav Arora Over a year ago

@AndyWhite - agreed with you. What I am supposing here if the scenario where I am using 1000s reserved keywords and here I believe its tidy to go one by one and encode the reserved keywords, hope you agreed at this point with me. What I did [as my requirement to encode SQL reserved keywords] - created a utility class and all is working fine. Here is a snippet from my own utility classes, this is having two flavors one with extension methods and another without extension methods here I supplied both in propelled with single class :

Gaurav Arora Over a year ago

public static class EncodeSqlReservered { public static string EncodeKeywordWithBraces(string keywordToEncode) { return string.Format("[{0}]", keywordToEncode); } public static string EncodeToBraces(this string keywordToEncode) { return string.Format("[{0}]", keywordToEncode); } #endregion }

Anton Gogolev · Accepted Answer · 2009-05-07 06:35:43Z

7

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

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

answered May 7, 2009 at 6:35

Anton Gogolev

116k39 gold badges204 silver badges293 bronze badges

1 Comment

Anton Gogolev Over a year ago

You added wrong character. You need a "backtick" (terminology from NHibernate docs), which is right under Tab key, not the apostrophe.

patrickbadley · Accepted Answer · 2012-01-12 15:49:45Z

5

In Fluent NHibernate, add

.Column("`Order`");

to your mapping to fix this error.

edited Jan 12, 2012 at 15:49

answered Jan 12, 2012 at 15:43

patrickbadley

2,5902 gold badges31 silver badges30 bronze badges

1 Comment

Lech Osiński Over a year ago

That's exactly what I needed to make my property/column "Order" works. I had class with Order property and only after adding this custom mapping it started to work.

Pawel Gorczynski · Accepted Answer · 2013-10-07 12:25:45Z

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 for reserved columns names (e.g. 'From') one 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;
    }
}

Collectives™ on Stack Overflow

NHibernate SQL creation causes error

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related