query nativa con SetFirstResult e setMaxResult da errore

Tonio

unread,

Nov 19, 2011, 1:35:23 PM11/19/11

to NHibernate-It

questa query nativa con SetFirstResult e setMaxResult solleva un
eccezzione ma senza funziona
la sintassi sqlserver generata da nhibernate mi sembra corretta

const string sqlSelect = @"
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog,
FROM cnsf_005_uiu AS a
ORDER BY aidim, aprog";

var session = NHFactory.Session;

var enumerable = session
.CreateSQLQuery(sqlSelect)
.SetFirstResult(1).SetMaxResults(1)
.SetResultTransformer(Transformers.AliasToEntityMap)
.List<IDictionary>()
.Select(r => new
{
aId = r["aid"],
aIdImmobile = r["aidim"],
aProg = r["aprog"],
});

qualcuno ha un idea?

mi sa tanto di bug...

... Failed[NHibernate.AdoNet.Util.SqlStatementLogger.LogCommand] DEBUG
SELECT TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog,
, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row;@p0 = 1 [Type: Int32 (0)], @p1
= 1 [Type: Int32 (0)]
[NHibernate.AdoNet.Util.SqlStatementLogger.LogCommand] DEBUG SELECT
TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog,
, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row;@p0 = 1 [Type: Int32 (0)], @p1
= 1 [Type: Int32 (0)]
[NHibernate.AdoNet.AbstractBatcher.ExecuteReader] ERROR Could not
execute query: SELECT TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog,
, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row
System.Data.SqlClient.SqlException (0x80131904): Sintassi non corretta
in prossimità di ','.
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
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)
at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
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)
in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet
\AbstractBatcher.cs:line 246
[NHibernate.Util.ADOExceptionReporter.LogExceptions] WARN
System.Data.SqlClient.SqlException (0x80131904): Sintassi non corretta
in prossimità di ','.
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
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)
at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
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)
in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet
\AbstractBatcher.cs:line 270
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean
autoDiscoverTypes, Boolean callable, RowSelection selection,
ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Loader\Loader.cs:line 1221
[NHibernate.Util.ADOExceptionReporter.LogExceptions] ERROR Sintassi
non corretta in prossimità di ','.
[NHibernate.Util.ADOExceptionReporter.LogExceptions] WARN
System.Data.SqlClient.SqlException (0x80131904): Sintassi non corretta
in prossimità di ','.
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
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)
at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
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)
in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet
\AbstractBatcher.cs:line 270
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean
autoDiscoverTypes, Boolean callable, RowSelection selection,
ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Loader\Loader.cs:line 1247
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session,
QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH
\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 416
at
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor
session, QueryParameters queryParameters, Boolean returnProxies) in d:
\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 246
at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Loader\Loader.cs:line 1590
[NHibernate.Util.ADOExceptionReporter.LogExceptions] ERROR Sintassi
non corretta in prossimità di ','.
[NHibernate.Util.ADOExceptionReporter.LogExceptions] WARN
System.Data.SqlClient.SqlException (0x80131904): Sintassi non corretta
in prossimità di ','.
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
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)
at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
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)
in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet
\AbstractBatcher.cs:line 270
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean
autoDiscoverTypes, Boolean callable, RowSelection selection,
ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Loader\Loader.cs:line 1247
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session,
QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH
\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 416
at
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor
session, QueryParameters queryParameters, Boolean returnProxies) in d:
\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 246
at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Loader\Loader.cs:line 1590
[NHibernate.Util.ADOExceptionReporter.LogExceptions] ERROR Sintassi
non corretta in prossimità di ','.

NHibernate.Exceptions.GenericADOException : could not execute query
[ SELECT TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog,
, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row ]
[SQL: SELECT TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog,
, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row]
----> System.Data.SqlClient.SqlException : Sintassi non corretta in
prossimità di ','.
at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Loader\Loader.cs: line 1607
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor
session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate
\src\NHibernate\Loader\Loader.cs: line 1497
at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery
customQuery, QueryParameters queryParameters, IList results) in d:
\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs: line 2089
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec,
QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH
\nhibernate\src\NHibernate\Impl\SessionImpl.cs: line 2071
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec,
QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Impl\SessionImpl.cs: line 2058
at NHibernate.Impl.SqlQueryImpl.List() in d:\CSharp\NH\NH\nhibernate
\src\NHibernate\Impl\SqlQueryImpl.cs: line 163
at EtlCensTests.TestETL.CanLoadCnsStadioUIU.CanBuildGrafoMutazioni1()
in CanLoadCnsStadioUIU.cs: line 443
--SqlException
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
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)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
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) in
d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:
line 270
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean
autoDiscoverTypes, Boolean callable, RowSelection selection,
ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Loader\Loader.cs: line 1247
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session,
QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH
\NH\nhibernate\src\NHibernate\Loader\Loader.cs: line 416
at
NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor
session, QueryParameters queryParameters, Boolean returnProxies) in d:
\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs: line 246
at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Loader\Loader.cs: line 1590

PadovaBoy

unread,

Nov 19, 2011, 3:22:37 PM11/19/11

to NHibernate-It

Bug nel tuo codice:

leggi l'errore:

NHibernate.Util.ADOExceptionReporter.LogExceptions] ERROR Sintassi
non corretta in prossimità di ','.

> SELECT

> a.tcns001_Id AS aid,
> a.tcns005_idImmobile AS aidim,

> a.tcns005_prog AS aprog, <--------------------------------- l'ultima virgola non ci stà
> FROM cnsf_005_uiu AS a

Tonio

unread,

Nov 19, 2011, 5:20:00 PM11/19/11

to NHibernate-It

scusate avevo snellito la query per semplificare e ho dimenticato un
virgola

quindi con firstresult/maxresult il test non passa senza passa

riposto il tutto

const string sqlSelect = @"
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog

FROM cnsf_005_uiu AS a
ORDER BY aidim, aprog";

var session = NHFactory.Session;

var enumerable = session
.CreateSQLQuery(sqlSelect)
.SetFirstResult(1).SetMaxResults(1)
.SetResultTransformer(Transformers.AliasToEntityMap)
.List<IDictionary>()
.Select(r => new
{
aId = r["aid"],
aIdImmobile = r["aidim"],
aProg = r["aprog"],
});

}

[NHibernate.AdoNet.Util.SqlStatementLogger.LogCommand] DEBUG SELECT

TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog

, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row;@p0 = 1 [Type: Int32 (0)], @p1
= 1 [Type: Int32 (0)]
[NHibernate.AdoNet.Util.SqlStatementLogger.LogCommand] DEBUG SELECT
TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog

, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row;@p0 = 1 [Type: Int32 (0)], @p1
= 1 [Type: Int32 (0)]
[NHibernate.AdoNet.AbstractBatcher.ExecuteReader] ERROR Could not
execute query: SELECT TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog

, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row

System.Data.SqlClient.SqlException (0x80131904): Il nome di colonna
'aidim' non è valido.
Il nome di colonna 'aprog' non è valido.

[NHibernate.Util.ADOExceptionReporter.LogExceptions] ERROR Il nome di
colonna 'aidim' non è valido.
Il nome di colonna 'aprog' non è valido.
[NHibernate.Util.ADOExceptionReporter.LogExceptions] WARN
System.Data.SqlClient.SqlException (0x80131904): Il nome di colonna
'aidim' non è valido.
Il nome di colonna 'aprog' non è valido.

[NHibernate.Util.ADOExceptionReporter.LogExceptions] ERROR Il nome di
colonna 'aidim' non è valido.
Il nome di colonna 'aprog' non è valido.

NHibernate.Exceptions.GenericADOException : could not execute query
[ SELECT TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog

, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row ]
[SQL: SELECT TOP (@p0) aid, aidim, aprog FROM (
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog

, ROW_NUMBER() OVER(ORDER BY aidim, aprog) as __hibernate_sort_row
FROM cnsf_005_uiu AS a) as query WHERE query.__hibernate_sort_row >
@p1 ORDER BY query.__hibernate_sort_row]

----> System.Data.SqlClient.SqlException : Il nome di colonna
'aidim' non è valido.
Il nome di colonna 'aprog' non è valido.

at NHibernate.Loader.Loader.DoList(ISessionImplementor session,
QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Loader\Loader.cs: line 1607
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor
session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate
\src\NHibernate\Loader\Loader.cs: line 1497
at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery
customQuery, QueryParameters queryParameters, IList results) in d:
\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs: line 2089
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec,
QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH
\nhibernate\src\NHibernate\Impl\SessionImpl.cs: line 2071
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec,
QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src
\NHibernate\Impl\SessionImpl.cs: line 2058
at NHibernate.Impl.SqlQueryImpl.List() in d:\CSharp\NH\NH\nhibernate
\src\NHibernate\Impl\SqlQueryImpl.cs: line 163

at EtlCensTests.TestETL.CanLoadCnsStadioUIU.Test() in

> ...
>
> leggi tutto

Nicola Tuveri

unread,

Nov 20, 2011, 2:20:12 PM11/20/11

to NHibernate-It

Forse ti posso suggerire un work around, ovvero modificare la query
nativa SQL in:

const string sqlSelect = @"
SELECT
a.tcns001_Id AS aid,
a.tcns005_idImmobile AS aidim,
a.tcns005_prog AS aprog
FROM cnsf_005_uiu AS a

ORDER BY a.tcns005_idImmobile, a.tcns005_prog"; // usando i nomi
delle colonne invece degli alias

> ...
>
> read more »

Giuseppe Guzzi

unread,

Nov 20, 2011, 3:17:55 PM11/20/11

to nh...@googlegroups.com

esatto, dopo un po' di prove stavo proprio adesso per postare di aver
verificato che ado/sqlserver accetta la seguente query (senza alias
nella clausola order by)
SELECT TOP 1 *
FROM (
SELECT tcns001_Id, tcns005_idImmobile, tcns005_prog,
ROW_NUMBER() OVER(ORDER BY tcns005_idImmobile, tcns005_prog) as
__hibernate_sort_row
FROM cnsf_005_uiu
) as query
WHERE query.__hibernate_sort_row > 1
ORDER BY query.__hibernate_sort_row

ma da errore con questa: il nome di colonna xyz non � valido (anche se
non riscontra nessun errore nella sintassi)

SELECT TOP 100 *
FROM (
SELECT tcns001_Id, tcns005_idImmobile as xyz, tcns005_prog,
ROW_NUMBER() OVER(ORDER BY xyz, tcns005_prog) as __hibernate_sort_row
FROM cnsf_005_uiu
) as query
WHERE query.__hibernate_sort_row > 100
ORDER BY query.__hibernate_sort_row

grazie per l'aiuto

t.

>> 'aidim' non � valido.
>> Il nome di colonna 'aprog' non � valido.

>> colonna 'aidim' non � valido.
>> Il nome di colonna 'aprog' non � valido.

>> [NHibernate.Util.ADOExceptionReporter.LogExceptions] WARN
>> System.Data.SqlClient.SqlException (0x80131904): Il nome di colonna

>> 'aidim' non � valido.
>> Il nome di colonna 'aprog' non � valido.

>> colonna 'aidim' non � valido.
>> Il nome di colonna 'aprog' non � valido.

>> [NHibernate.Util.ADOExceptionReporter.LogExceptions] WARN
>> System.Data.SqlClient.SqlException (0x80131904): Il nome di colonna

>> 'aidim' non � valido.
>> Il nome di colonna 'aprog' non � valido.

>> read more �

PadovaBoy

unread,

Nov 20, 2011, 5:58:05 PM11/20/11

to NHibernate-It

Mi spieghi perchè dovresti fare una query del genere in sql con
nhibernate?
Sai vero che puoi fare la projection e recuperarti solo le colonne che
ti servono per poi passarle ad un dto?

public static IList<T> ToDtoList<T>(this IQuery query)
{
return
query.SetResultTransformer(Transformers.AliasToBean(typeof(T))).List<T>();
}

dove T è un dto e IQuery contiene una Hql query fatta tipo:

select c.FirstName as FirstName, c.LastName as LastName from Customer
c

e T è

public class CustomerDto{
public string FirstName {get;set;}
public string LastName {get;set;}
}

così lasci ad nh di preoccuparsi del resto.

> ...
>
> leer más »

Tonio

unread,

Nov 21, 2011, 3:27:44 AM11/21/11

to NHibernate-It

...perche':

1) la list non serve, ho bisogno un dictionary per recuperare le
tuple tramite una chiave ed effettuare delle trasformazioni complesse
e poi istanziare diversi oggetti di dominio

2) nel mio contesto il dto è superfluo: devo solo trasformare dei dati
legacy da una fonte esterna non OO e creare un nuovo dominio object
oriented

3) non ho ancora avuto il tempo di approfondire tutte le classi
trasformer di NH :(

piuttosto a me sarebber servito non usare una sql nativa (molto
grossa) ma usare queryobject o linq to NH (così da evitare le magic
string) ma purtroppo no se puede: sai vero che che nessuno dei due
supporta un left join su campi che non sono associazioni?

cmq grazie del suggerimento

t.

> ...
>
> leggi tutto

Reply all

Reply to author

Forward