Really? I guess the Oracle query optimizer doesn't do its job well enough
then.
var list=sess.CreateSQLQuery(sql)
.SetResultTransformer(Transformers.AliasToBean(typeof(YourDto)))
.List<YourDto>()
2015-01-21 23:26 GMT+01:00 Bryan Valencia <[email protected]>:
> Yes, that's the type of query that takes 70 to 120 seconds to complete.
> That's why I wanted to execute it as native SQL.
>
>
>
>
> On 1/21/2015 2:20 PM, Gunnar Liljas wrote:
>
> In LINQ, that could be
>
> session.Query<Message>()
> .Select(x=>new {
> Message=x,
> LastNote=session.Query<MessageHistory>()
> .Where(mh=>mh.MessageId==x.Id && mh.Notes!=null)
> .OrderByDescending(mh=>mh.ID)
> .Select(mh=>mh.Notes)
> .FirstOrDefault()
> }
> )
> .Select(x=>new TheClass{ID=x.Message.Id,....etc,
> FirstLine=MethodWhichExtractsTheFirstLine(x.LastNote)
>
> 2015-01-21 21:56 GMT+01:00 Bryan Valencia <[email protected]>:
>
>> Here is my query and Object Properties
>> select
>> T.ID,
>> T.ENTITY_TYPE as "EntityType",
>> T.ENTITY_ID as "EntityID",
>> T.ENTITY_NAME as "EntityName",
>> T.USER_ID as "UserID",
>> T.STATUS as "Status",
>> T.REASON_CODE as "Reason",
>> T.CREATE_DATE as "CreateDate",
>> T.UPDATE_DATE as "UpdateDate",
>> T.LASTNOTE as "LastNote",
>> -- next line pulls in the first part of the LastNote up to the first
>> Carriage
>> -- Return.
>> replace(regexp_substr(T.LastNote, '.*$', 1, 1, 'm'),CHR(13),'') as
>> "FirstLine",
>> 0 as "Selected"
>> from
>> (
>> -- all the columns from the Messages table.
>> select M.*,
>> -- get the last note from Message History.
>> ( Select MH.NOTES
>> from HI_MESSAGE_HISTORY MH
>> where
>> MH.MESSAGE_ID = M.ID
>> -- next line finds the latest message history row with
>> -- a note, or null if there are no message history rows
>> -- with a note.
>> and MH.ID = (
>> -- next line finds the last Message History record for this
>> message
>> -- that has a non-null note.
>> select max(id)
>> from HI_MESSAGE_HISTORY HMH
>> where HMH.NOTES is not null
>> and HMH.MESSAGE_ID = M.ID)
>> ) LastNote
>> FROM HI_MESSAGES M
>> WHERE M.Status in ('failed', 'under review')
>> ) T
>>
>> and.
>>
>> public int ID { get; set; }
>> public string EntityType { get; set; }
>> public string EntityID { get; set; }
>> public string EntityName { get; set; }
>> public string UserID { get; set; }
>> public string Status { get; set; }
>> public string Reason { get; set; }
>> public DateTime? CreateDate { get; set; }
>> public DateTime? UpdateDate { get; set; }
>> public string LastNote { get; set; }
>> public string FirstLine { get; set; }
>> public bool Selected { get; set; }
>>
>>
> --
> *Bryan Valencia*
> *Owner*
> Software Services
> 916-572-6166 ph.
> 209-470-5728 cell
> http://209software.com/
>
>
> ------------------------------
> <http://www.avast.com/>
>
> This email is free from viruses and malware because avast! Antivirus
> <http://www.avast.com/> protection is active.
>
> --
> You received this message because you are subscribed to the Google Groups
> "nhusers" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/nhusers.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
