Nhibernate, need help to fix my select query

Question

I am new to nhibernate. the one I am using is nhibernate 3. here's what I need to do, select the User its Id is 1. if that User doesn't exist return back null.

var selectedUser = session.Query().Where(u=>u.Id == 1).FirstOrDefault();

                if (selectedUser == null)
                {
                    userID = (int)session.Save(user);
                }

my select query doesn't.

here's the error message I got.

could not execute query\r\n[ select TOP (@p0) user0_.Id as Id0_, user0_.Username as Username0_, user0_.Password as Password0_, user0_.CreateDate as CreateDate0_, user0_.Avatar as Avatar0_, user0_.AccountTypeId as AccountT6_0_, user0_.FirstName as FirstName0_, user0_.LastName as LastName0_, user0_.MiddleName as MiddleName0_, user0_.Email as Email0_, user0_.OtherId as OtherId0_ from [User] user0_ where user0_.Id=@p1 ]\r\n  Name:p1 - Value:1\r\n[SQL: select TOP (@p0) user0_.Id as Id0_, user0_.Username as Username0_, user0_.Password as Password0_, user0_.CreateDate as CreateDate0_, user0_.Avatar as Avatar0_, user0_.AccountTypeId as AccountT6_0_, user0_.FirstName as FirstName0_, user0_.LastName as LastName0_, user0_.MiddleName as MiddleName0_, user0_.Email as Email0_, user0_.OtherId as OtherId0_ from [User] user0_ where user0_.Id=@p1]"}
 [NHibernate.Exceptions.GenericADOException]: {"could not execute query\r\n[ select TOP (@p0) user0_.Id as Id0_, user0_.Username as Username0_, user0_.Password as Password0_, user0_.CreateDate as CreateDate0_, user0_.Avatar as Avatar0_, user0_.AccountTypeId as AccountT6_0_, user0_.FirstName as FirstName0_, user0_.LastName as LastName0_, user0_.MiddleName as MiddleName0_, user0_.Email as Email0_, user0_.OtherId as OtherId0_ from [User] user0_ where user0_.Id=@p1 ]\r\n  Name:p1 - Value:1\r\n[SQL: select TOP (@p0) user0_.Id as Id0_, user0_.Username as Username0_, user0_.Password as Password0_, user0_.CreateDate as CreateDate0_, user0_.Avatar as Avatar0_, user0_.AccountTypeId as AccountT6_0_, user0_.FirstName as FirstName0_, user0_.LastName as LastName0_, user0_.MiddleName as MiddleName0_, user0_.Email as Email0_, user0_.OtherId as OtherId0_ from [User] user0_ where user0_.Id=@p1]"}
Data: {System.Collections.ListDictionaryInternal}
HelpLink: null
InnerException: {"Invalid object name 'User'."}
Message: "could not execute query\r\n[ select TOP (@p0) user0_.Id as Id0_, user0_.Username as Username0_, user0_.Password as Password0_, user0_.CreateDate as CreateDate0_, user0_.Avatar as Avatar0_, user0_.AccountTypeId as AccountT6_0_, user0_.FirstName as FirstName0_, user0_.LastName as LastName0_, user0_.MiddleName as MiddleName0_, user0_.Email as Email0_, user0_.OtherId as OtherId0_ from [User] user0_ where user0_.Id=@p1 ]\r\n  Name:p1 - Value:1\r\n[SQL: select TOP (@p0) user0_.Id as Id0_, user0_.Username as Username0_, user0_.Password as Password0_, user0_.CreateDate as CreateDate0_, user0_.Avatar as Avatar0_, user0_.AccountTypeId as AccountT6_0_, user0_.FirstName as FirstName0_, user0_.LastName as LastName0_, user0_.MiddleName as MiddleName0_, user0_.Email as Email0_, user0_.OtherId as OtherId0_ from [User] user0_ where user0_.Id=@p1]"
Source: "NHibernate"
StackTrace: "   at NHibernate.Loader.Loader.DoList(ISessionImplementor session,    QueryParameters queryParameters)\r\n   at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)\r\n   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)\r\n   at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters)\r\n   at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters)\r\n   at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)\r\n   at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results)\r\n   at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters)\r\n   at NHibernate.Impl.ExpressionQu

eryImpl.List()\r\n at NHibernate.Linq.NhQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery)\r\n at NHibernate.Linq.NhQueryProvider.Execute(Expression expression)\r\n at NHibernate.Linq.NhQueryProvider.Execute[TResult](Expression expression)\r\n at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)\r\n at DevelopmentStack.Domain.Repositories.Concrete.NUserRepository.AddUser(User user) in C:\code\net\DevelopmentStack\DevelopmentStack.Domain\Repositories\Concrete\NUserRepository.cs:line 30" TargetSite: {System.Collections.IList DoList(NHibernate.Engine.ISessionImplementor, NHibernate.Engine.QueryParameters)} >

Felice Pollano Felice Pollano · Accepted Answer · 2011-12-23T15:58:48

This is probably due to the fact that User is a reserved keyword. If you mapped it in hbm ensure you have provided the escapes in the table name: `User ( user should be surrounded by `` I can't write properly due to markdown ) or [User]. The difference between the two is that the second one does not care about the dialect.

Nhibernate, need help to fix my select query

2 Answers