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 <http://x.Message.Id>,....etc,
> FirstLine=MethodWhichExtractsTheFirstLine(x.LastNote)
>
> 2015-01-21 21:56 GMT+01:00 Bryan Valencia <[email protected]
> <mailto:[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",
> --nextline pulls inthe first part of the LastNoteup to the first
> Carriage
> --Return.
> replace(regexp_substr(T.LastNote,'.*$',1,1,'m'),CHR(13),'')
> as"FirstLine",
> 0as"Selected"
> from
> (
> --all the columns fromthe Messagestable.
> selectM.*,
> --getthe lastnote fromMessageHistory.
> (SelectMH.NOTES
> fromHI_MESSAGE_HISTORY MH
> where
> MH.MESSAGE_ID =M.ID
> --nextline finds the latest message history row with
> --a note,ornullifthere are nomessage history rows
> --witha note.
> andMH.ID =(
> --nextline finds the lastMessageHistoryrecord forthismessage
> --that has a non-nullnote.
> selectmax(id)
> fromHI_MESSAGE_HISTORY HMH
> whereHMH.NOTES isnotnull
> andHMH.MESSAGE_ID =M.ID)
> )LastNote
> FROM HI_MESSAGES M
> WHERE M.Statusin('failed','under review')
> )T
> |
>
> and.
>
> |
> publicintID {get;set;}
> publicstringEntityType{get;set;}
> publicstringEntityID{get;set;}
> publicstringEntityName{get;set;}
> publicstringUserID{get;set;}
> publicstringStatus{get;set;}
> publicstringReason{get;set;}
> publicDateTime?CreateDate{get;set;}
> publicDateTime?UpdateDate{get;set;}
> publicstringLastNote{get;set;}
> publicstringFirstLine{get;set;}
> publicboolSelected{get;set;}
> |
>
--
*Bryan Valencia*
/Owner/
Software Services
916-572-6166 ph.
209-470-5728 cell
http://209software.com/
---
This email is free from viruses and malware because avast! Antivirus protection
is active.
http://www.avast.com
--
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.
<<attachment: 209software.vcf>>
