C# BPM Linq - check if record is non-existent or null

Epicor ERP 10
,
1

In a C# bpm I have it so that I’m looking up a record in UD07 table with Linq and if I get a record back I display an error. the query is formatted in such a way that if there is a record that meets the criteria that it’s an error.

I also want to display the error if there is no corresponding part record in the UD07 table. I’m not sure what the syntax is for it. Can anyone help with the syntax?

foreach(var ppap in (from apqp in Db.UD07 
                         where apqp.Company == tt.Company 
                            && apqp.Key1 == tt.PartNum
                            && apqp.Key2 == tt.RevisionNum                             
                            && !apqp.QualityApproved_c 
                         select apqp)) 
{
        CallContext.Current.ExceptionManager.AddBLException(string.Format("Error: Part not Quality Approved{0}{0}Part: {1}   Rev: {2}", Environment.NewLine, ppap.Key1, ppap.Key2));
}

The above code works fine but I also want the BPM to display the same error message if there isn’t a UD07 record at all.

2

Something Like This:

var ppapRecords = Db.UD07.Where(x=>x.Company == tt.Company 
                            && x.Key1 == tt.PartNum
                            && x.Key2 == tt.RevisionNum                             
                            && !x.QualityApproved_c);
if (ppapRecords = null){
    CallContext.Current.ExceptionManager.AddBLException("no records found");
} else {
    foreach(var ppap in ppapRecords) {
        CallContext.Current.ExceptionManager.AddBLException(string.Format("Error: Part not Quality Approved{0}{0}Part: {1}   Rev: {2}", Environment.NewLine, ppap.Key1, ppap.Key2));
    }
}
1 Like
3

I would get rid of the foreach statement loop and move it down. Create a variable for the LINQ query and then test for null. If it’s null, throw the exception. If not, run your foreach loop.

Edit:
Exactly what @timshuwy said.

4

I would actually make this more efficient by adding the fields that you want to select in the initial query since you dont need the entire dataset.

var ppapRecords = Db.UD07.Where(x=>x.Company == tt.Company 
                            && x.Key1 == tt.PartNum
                            && x.Key2 == tt.RevisionNum                             
                            && !x.QualityApproved_c).Select(x=>x.Key1,x.Key2);
if (ppapRecords = null){
    CallContext.Current.ExceptionManager.AddBLException("no records found");
} else {
    foreach(var ppap in ppapRecords) {
        CallContext.Current.ExceptionManager.AddBLException(string.Format("Error: Part not Quality Approved{0}{0}Part: {1}   Rev: {2}", Environment.NewLine, ppap.Key1, ppap.Key2));
    }
}
2 Likes
5

I understood that the no records error is meant when there is no record, approved or not, so the end result would be something like this

var ppapRecords = Db.UD07.Where(x=>x.Company == tt.Company 
                            && x.Key1 == tt.PartNum
                            && x.Key2 == tt.RevisionNum).Select(x => x.Key1,x.Key2, x.QualityApproved_c);

if (ppapRecords == null){
    CallContext.Current.ExceptionManager.AddBLException("no records found");
} else {
    foreach(var ppap in ppapRecords.Where(x => !x.QualityApproved_c)) {
        CallContext.Current.ExceptionManager.AddBLException(string.Format("Error: Part not Quality Approved{0}{0}Part: {1}   Rev: {2}", Environment.NewLine, ppap.Key1, ppap.Key2));
    }
}
1 Like
6

Thank you for all the knowledge sharing. It makes C# not daunting with all these nice examples.

Things I’ve learned here (some of these might be old tricks for you but new to me!):

  1. You can assign the result of the LINQ outside of a foreach loop. All the code examples I had were assigning it inside.

  2. You can select specific fields instead of the entire record.
    Select(x=>x.Key1,x.Key2);

  3. How to query against an variable that is already a LINQ result set.
    foreach(var ppap in ppapRecords.Where(x => !x.QualityApproved_c) {

Thank you very much @timshuwy & @Jonathan I can make use of these in this and future BPM code tasks I come across.

7

Of course, if you did this, you would also need to select that value as well in your select statement above.

8

Should it be ?
if (ppapRecords == null){

Isn’t single = the assignment operator?

9

Note that this is actually more efficient in most cases, because the query goes to the server one time to gather all the data at once.

10

Whoops… ABSOLUTLY should be ‘==’… Sorry about that!

below is correct!

if (ppapRecords == null){
11

Note that this is actually more efficient in most cases, because the query goes to the server one time to gather all the data at once.