Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
NHibernate-It
Home
+ new post
About this group
Join this group
Message from discussion query nativa con SetFirstResult e setMaxResult da errore
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
PadovaBoy  
View profile  
 More options Nov 20 2011, 5:58 pm
From: PadovaBoy <padova...@padovaboy.it>
Date: Sun, 20 Nov 2011 14:58:05 -0800 (PST)
Local: Sun, Nov 20 2011 5:58 pm
Subject: Re: query nativa con SetFirstResult e setMaxResult da errore
Print | View thread | Show original | Report this message | Find messages by this author
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.
On 20 nov, 21:17, Giuseppe Guzzi <ag.gu...@gmail.com> wrote:
> 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.

> Il 20/11/2011 20:20, Nicola Tuveri ha scritto:

> > 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

> > On Nov 19, 11:20 pm, Tonio<ag.gu...@gmail.com>  wrote:
> >> 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.
> >>     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): Il nome di colonna
> >> 'aidim' non valido.
> >> Il nome di colonna 'aprog' non valido.
> >>     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 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.
> >>     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(ISessionImp lementor
> >> session, QueryParameters queryParameters, Boolean returnProxies) in d:
> >> \CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 246

> ...

> leer más »


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2013 Google