sqlkata / querybuilder

SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql, Oracle, Sqlite and Firebird
https://sqlkata.com
MIT License
3.08k stars 498 forks source link

Different behavior Get(query) and Get() #620

Closed emaengine closed 1 year ago

emaengine commented 1 year ago

I'm trying to get data from MS SQL server with a queryObject created like this:

var queryObj = new Query().From("PersonalInformation")
                          .Select("FirstName")
                          .Select("LastName")
                          .Select("Id")
                          .Select("BirthDate")
                          .Select("AppUserId")
                          ;

and execute the query:

var @object = dbSQLServer.Get(query);

but I get this exception:

System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Incorrect syntax near '@p0'.
  Source=Core .Net SqlClient Data Provider
  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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior) in /_/Dapper/SqlMapper.cs:line 1066
   at Dapper.SqlMapper.<QueryImpl>d__140`1.MoveNext() in /_/Dapper/SqlMapper.cs:line 1094
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 734
   at SqlKata.Execution.QueryFactory.Get[T](Query query, IDbTransaction transaction, Nullable`1 timeout)
   at SqlKata.Execution.QueryFactory.Get(Query query, IDbTransaction transaction, Nullable`1 timeout)
   at LibraryNugetTest.DapperAndSqlKata.TestSelect() in C:\Users\emanu\Desktop\WORK\.NET\MyProjects\ERPWebApp\LibraryNugetTest\DapperAndSqlKata.cs:line 59

  This exception was originally thrown at this call stack:
    [External Code]
    LibraryNugetTest.DapperAndSqlKata.TestSelect() in DapperAndSqlKata.cs

I have no exception if I create query directly from QueryFactory:

var userSQLServer = dbSQLServer.Query().From("PersonalInformation")
                          .Select("FirstName")
                          .Select("LastName")
                          .Select("Id")
                          .Select("BirthDate")
                          .Select("AppUserId")
                          ;
var @object = userSQLServer.Get();

Database: Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64) SqlClient: System.Data.SqlClient Version=4.8.3

ahmad-moussawi commented 1 year ago

I am pretty sure there is something that altering the query in your code, could you double check, if no could you please post a project link that can reproduce the issue.