Sequence contains no element error when select result is null

Hi @aaghaei  Please use below query and let me know if you have any issues?

   EPApproval ownerApproval = PXSelect<EPApproval,
                            Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                            And<EPApproval.ownerID, Equal<Current<AccessInfo.contactID>>,
                            And<EPApproval.status, Equal<Required<EPApproval.status>>>>>,
                            OrderBy<Asc<EPApproval.approvalID>>>
                           .Select(Base, this.Base.Document.Current?.NoteID, EPStatuses.Pending);

Thanks @Naveen Boga ,

Now I get this error “The multi-part identifier "AccessInfo.ContactID" could not be bound.”

Hi @aaghaei Sorry, Please try below query!!

  EPApproval ownerApproval = PXSelect<EPApproval,
                                        Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                                        And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>,
                                        And<EPApproval.status, Equal<Required<EPApproval.status>>>>>,
                                        OrderBy<Asc<EPApproval.approvalID>>>
                                       .Select(Base, this.Base.Document.Current?.NoteID, Base.Accessinfo.ContactID, EPStatuses.Pending);

Thanks @Naveen Boga 

Now it is back to square 1. “Sequence contains no elemen” please note I only need the top 1 so I have .First() at the end. here is the code I run

                EPApproval ownerApproval = PXSelect<EPApproval,
                            Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                            And<EPApproval.status, Equal<Required<EPApproval.status>>,
                            And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>>>>,
                            OrderBy<Asc<EPApproval.approvalID>>>
                            .Select(Base, Base.Document.Current?.NoteID, EPStatuses.Pending, Base.Accessinfo.ContactID).First();

//also I tried this

                EPApproval ownerApproval = PXSelect<EPApproval,
                            Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                            And<EPApproval.status, Equal<Required<EPApproval.status>>,
                            And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>>>>,
                            OrderBy<Asc<EPApproval.approvalID>>>
                            .Select(Base, Base.Document.Current?.NoteID, EPStatuses.Pending, Base.Accessinfo?.ContactID).First();

//and tried this
        int CurrentOwnerID = PXAccess.GetContactID().Value;

                EPApproval ownerApproval = PXSelect<EPApproval,
                            Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                            And<EPApproval.status, Equal<Required<EPApproval.status>>,
                            And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>>>>,
                            OrderBy<Asc<EPApproval.approvalID>>>
                            .Select(Base, Base.Document.Current?.NoteID, EPStatuses.Pending, CurrentOwnerID).First();

Hi @aaghaei  If you wanted to fetch the TOP 1 record, please use the SELECTWINDOWED syntax below and NO need to FIRST

Here is the example:

            EPApproval ownerApproval = PXSelect<EPApproval,
                         Where<EPApproval.refNoteID, Equal<Required<EPApproval.refNoteID>>,
                         And<EPApproval.status, Equal<Required<EPApproval.status>>,
                         And<EPApproval.ownerID, Equal<Required<EPApproval.ownerID>>>>>,
                         OrderBy<Asc<EPApproval.approvalID>>>
                         .SelectWindowed(Base, 0, 1, Base.Document.Current?.NoteID, EPStatuses.Pending, Base.Accessinfo.ContactID);

You can also just skip the First() completely. 

The engine is smart enough to recognize that if you save the result to variable of type EPApproval and not IEnumerable<EPApproval> that you expect only one result. The engine will automatically add the TOP1 to the SQL query if you save the query result to such variable. 

Thank you @Dmitrii Naumov, @Shawn Burt and @Naveen Boga 

much appreciated