Skip to main content
Solved

Sequence contains no element error when select result is null

  • Apr 8, 2022 7:52 AM UTC
  • 8 replies
  • 1130 views
  • Translate All

aaghaei
Captain II
Forum|alt.badge.img+11

Hello gang,

 

I have the below select in one of my methods. When there is data that meets the conditions, it works fine but when there is no data with this conditions I get an error “Sequence contains no element”. How can I prevent system from raising error. I’m checking this select result later in my code whether is null or not.

 

                EPApproval ownerApproval = PXSelect<EPApproval,
Where<EPApproval.refNoteID, Equal<Required<APInvoice.noteID>>,
And<EPApproval.ownerID, Equal<Required<AccessInfo.contactID>>,
And<EPApproval.status, Equal<EPStatuses.pending>>>>,
OrderBy<Asc<EPApproval.approvalID>>>
.Select(this.Base, this.Base.Document.Current.NoteID, PXAccess.GetContactID().Value, EPStatuses.Pending).First();

 


 

Best answer by Shawn Burt

 

aaghaei,

I have had this issue if the result is empty, you might change to FirstOrDefault() instead. 

FirstOrDefault will return a null if the list is empty. First will return the error you are getting if results are empty.

View original

8 replies

Naveen Boga
Captain II
Forum|alt.badge.img+20
  • Captain II
  • Apr 8, 2022 7:58 AM UTC

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

 

Translate

aaghaei
Captain II
Forum|alt.badge.img+11
  • Author
  • Captain II
  • Apr 8, 2022 8:22 AM UTC

Thanks @Naveen Boga ,

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

Translate

Naveen Boga
Captain II
Forum|alt.badge.img+20
  • Captain II
  • Apr 8, 2022 8:26 AM UTC

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

 

Translate

aaghaei
Captain II
Forum|alt.badge.img+11
  • Author
  • Captain II
  • Apr 8, 2022 9:03 AM UTC

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();

 

Translate

Naveen Boga
Captain II
Forum|alt.badge.img+20
  • Captain II
  • Apr 8, 2022 9:31 AM UTC

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

 

Translate

Shawn Burt
Jr Varsity I
Forum|alt.badge.img+1
  • Jr Varsity I
  • Answer
  • Apr 8, 2022 1:54 PM UTC

 

aaghaei,

I have had this issue if the result is empty, you might change to FirstOrDefault() instead. 

FirstOrDefault will return a null if the list is empty. First will return the error you are getting if results are empty.

Translate

Dmitrii Naumov
Community Manager
Forum|alt.badge.img+7

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. 

Translate

aaghaei
Captain II
Forum|alt.badge.img+11
  • Author
  • Captain II
  • Apr 9, 2022 8:08 AM UTC

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

 

much appreciated

Translate