Fluent NHibernate without Automapping SQL Exception, Incorrect syntax near the keyword 'User'

Question

Just starting in NHibernate and to my eye Everything seems correct but obviously is not. When I ren unit tests shown below I receive back that there is a syntax error near the keyword "User" here is my user class:

namespace Users
    {
        public class User
        {
            public virtual string firstName { get; set; }
            public virtual string lastName { get; set; }
            public virtual int Id { get; set; }
        }
    }

and the User mapping(Also ran without square brackets around column names with identical results:

namespace Users
{
    class UserMap: ClassMap<User>
    {
        UserMap()
        {
            Table("User");
            Id(x => x.Id).GeneratedBy.Native().Column("[Id]").Not.Nullable();
            Map(x => x.firstName).Not.Nullable().Column("[firstName]");
            Map(x => x.lastName).Not.Nullable().Column("[lastName]");
        }
    }
}

The Config file named Framework.cs

namespace Users
{
    public class Framework
    {
        private const string ConnectionStr = "Data Source=ERALCH-ESTEPHEN;Initial     
                                Catalog=NHDev;Integrated Security=True;Pooling=False";
        public static ISessionFactory CreateFactory()
        {
            return Fluently.Configure()
                .Database(FluentNHibernate.Cfg.Db.MsSqlConfiguration
                .MsSql2008
                .ConnectionString(ConnectionStr))
                .Mappings(x=>x.FluentMappings.AddFromAssemblyOf<User>())
                .BuildSessionFactory();
        }
    }
}

The Data Access Layer-- simply retrieves a user by Id

namespace Users
{
    public class Accesslayer
    {
        public static IList<User> GetUserById(int Id)
        {
            ISessionFactory provider = Framework.CreateFactory();
            using (ISession session = provider.OpenSession())
            {
                return session.CreateSQLQuery(String
                    .Format("SELECT * FROM User WHERE Id={0}", Id)).List<User>();
            }

        }
    }
}

and finally the unit test layer

namespace Users
{
    [TestFixture]
    class AccessLayerTest
    {
        [Test]
        public void CanGetUserById()
        {
            Assert.AreEqual(1, Accesslayer.GetUserById(1).Count());
        }
    }
}

The Database is MSsql with one table "User" with columns matching the user properties. Any help would be appreciated thanks

score 4 · Answer 1 · answered Feb 03 '15 at 14:46

You should be able to do this in your configuration:

var factory = Fluently.Configure()
    // ...
    .ExposeConfiguration(c => SchemaMetadataUpdater.QuoteTableAndColumns(c))
    .BuildSessionFactory();

Which should escape your table and column names for you automagically.

score 0 · Answer 2 · edited May 23 '17 at 11:59

Did you try putting backticks around your User table name ?

namespace Users
{
    class UserMap: ClassMap<User>
    {
        UserMap()
        {
            Table("`User`");
            Id(x => x.Id).GeneratedBy.Native().Column("`Id`").Not.Nullable();
            Map(x => x.firstName).Not.Nullable().Column("`firstName`");
            Map(x => x.lastName).Not.Nullable().Column("`lastName`");
        }
    }
}

See this answer for more details : NHibernate - Force escaping on Table Names

Also, you should use NHibernate querying facilities instead of SQL :

namespace Users
{
    public class Accesslayer
    {
        public static IList<User> GetUserById(int Id)
        {
            ISessionFactory provider = Framework.CreateFactory();
            using (ISession session = provider.OpenSession())
            {
                return session.Query<User>().Where(x => x.Id == Id ).List<User>();
            }
        }
    }
}

Have a look at this tutorial : http://www.d80.co.uk/post/2011/02/20/Linq-to-NHibernate-Tutorial.aspx

score 0 · Answer 3 · answered May 06 '12 at 15:03

You should need the [Brackets] while mapping. Heck, if they have the same name, both of these would work the same:

public class UserMap: ClassMap<User>
{
    UserMap()
    {
        Table("User");
        Id(x => x.Id).GeneratedBy.Native().Not.Nullable();
        Map(x => x.firstName).Not.Nullable();
        Map(x => x.lastName).Not.Nullable();
    }
}

public class UserMap: ClassMap<User>
{
    UserMap()
    {
        Table("User");
        Id(x => x.Id, "Id").GeneratedBy.Native().Not.Nullable();
        Map(x => x.firstName, "firstName").Not.Nullable();
        Map(x => x.lastName, "lastName").Not.Nullable();
    }
}

Where would i need the brackets and yes the column names are identical to the property names — Ethan Stephens, May 10 '12 at 14:26

score 0 · Answer 4 · edited May 23 '17 at 12:15

0

Avoid naming your tables or columns using Reserved Keywords. Hibernate forms a SQL-Statement which won't be accepted by MS SQL. Had the same problem with NHibernate + Fluent + Automapper and solved it by renaming the "user"-column to "username". Don't know how other DBs handle it.

Further comments about this here.

edited May 23 '17 at 12:15

Community

1
1

answered Apr 11 '13 at 14:31

Ronny D

1

Fluent NHibernate without Automapping SQL Exception, Incorrect syntax near the keyword 'User'

4 Answers4