NHibernate - Could not execute query

Question

I want to get all tickets using NHibernate.

[Authorize]
[HttpGet]
[Route("getUserTickets")]
public async Task<IHttpActionResult> GetUserTickets()
{
    return Ok(Ticket.GetTicketsByUserName(User.Identity.Name.GetUserName()));
}

and this is GetTicketsByUserNameMethod :

public static List<Ticket> GetTicketsByUserName(string userName)
        {
            using (var session = DatabaseController.SessionFactory.OpenSession())
            {
                using (var transaction = session.BeginTransaction())
                {
                    var tickets = session.Query<Ticket>().
                        Where(x => x.User == userName).
                        ToList();

                    transaction.Commit();

                    return tickets;
                }
            }
        }

But i get this error :

<Error>
<Message>An error has occurred.</Message>
<ExceptionMessage>
could not execute query [ select ticket0_.Id as Id1_, ticket0_.User as User1_, ticket0_.Agent_id as Agent3_1_, ticket0_.TicketStatus_id as TicketSt4_1_ from [Ticket] ticket0_ where ticket0_.User=@p0 ] Name:p1 - Value:ma.sojoudi [SQL: select ticket0_.Id as Id1_, ticket0_.User as User1_, ticket0_.Agent_id as Agent3_1_, ticket0_.TicketStatus_id as TicketSt4_1_ from [Ticket] ticket0_ where ticket0_.User=@p0]
</ExceptionMessage>
<ExceptionType>NHibernate.Exceptions.GenericADOException</ExceptionType>
<StackTrace>
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Loader.Hql.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) at NHibernate.Impl.AbstractQueryImpl2.List() at NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) at Remotion.Linq.QueryableBase`1.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at TicketSystem.Models.Ticket.GetTicketsByUserName(String userName) in D:\Amin\Projects\TicketSystem\TicketSystem\Models\Ticket.cs:line 107 at TicketSystem.Controllers.TicketController.<GetUserTickets>d__3.MoveNext() in D:\Amin\Projects\TicketSystem\TicketSystem\Controllers\TicketController.cs:line 52 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Threading.Tasks.TaskHelpersExtensions.<CastToObject>d__3`1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()
</StackTrace>
<InnerException>
<Message>An error has occurred.</Message>
<ExceptionMessage>Incorrect syntax near the keyword 'User'.</ExceptionMessage>
<ExceptionType>System.Data.SqlClient.SqlException</ExceptionType>
<StackTrace>
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer) at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer) at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer)
</StackTrace>
</InnerException>
</Error>

I have to say that there is no data in database so it's output should be empty xml, is this error related to this ?

Answer 1

It seems that you used column name User which could conflict with some keyword user

Just use your DB related safe name, e.g. on SQL Server

// instead of 
column="User"
// use this
column="[User]"

And even maybe in NHibernate this could be used

column="`User`"

And based on dialect, the proper escape symbol will be used. The same applies to table names etc.