Data Directive BPM C# Linq Help

Chris_Conn · June 15, 2018, 8:44pm

It doesnt like your select new statement me thinks. What happens if you do this:

 select new 
       {
          OrderNum = o.OrderNum, RequestDate = o.RequestDate

       }).ToList();

asmar · June 15, 2018, 9:07pm

Got the same error message:

Description: There is at least one compilation error.

Details:

Error CS1061: ‘System.Collections.Generic.List<AnonymousType#1>’ does not contain a definition for ‘OrderNum’ and no extension method ‘OrderNum’ accepting a first argument of type ‘System.Collections.Generic.List<AnonymousType#1>’ could be found (are you missing a using directive or an assembly reference?) [GetTaskList.Post.Set_Quote_Number.cs(279,50)]

Error CS1061: ‘System.Collections.Generic.List<AnonymousType#1>’ does not contain a definition for ‘RequestDate’ and no extension method ‘RequestDate’ accepting a first argument of type ‘System.Collections.Generic.List<AnonymousType#1>’ could be found (are you missing a using directive or an assembly reference?) [GetTaskList.Post.Set_Quote_Number.cs(281,58)]

Error CS1061: ‘System.Collections.Generic.List<AnonymousType#1>’ does not contain a definition for ‘DocTotalCharges’ and no extension method ‘DocTotalCharges’ accepting a first argument of type ‘System.Collections.Generic.List<AnonymousType#1>’ could be found (are you missing a using directive or an assembly reference?) [GetTaskList.Post.Set_Quote_Number.cs(282,70)]

Program: Epicor.Customization.dll

Method: PrepareException

asmar · June 15, 2018, 9:08pm

 /* Start new code */

             var OrderHedVar = (from o in Db.OrderHed.With(LockHint.NoLock)

                                                            // *** Add left outer join to OrderDtl

                                                 join od in

Db.OrderDtl.With(LockHint.NoLock)

             on new { o.Company, o.OrderNum } equals new { od.Company, od.OrderNum }

     into oodj

     from od in oodj.DefaultIfEmpty()

                                                            // ***

   

 where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber  

       select new

       {

          OrderNum = o.OrderNum, RequestDate =

o.RequestDate, DocTotalChargest =
o.DocTotalCharges

       }).ToList();

  // end new code

Chris_Conn · June 15, 2018, 9:22pm

Whew, that formatting is horrible. Use```cs to format code.

I’m with Jose, what’s the purpose of this exercise if you arent even using those other joins?

At any rate, what if you move your where clause to after the query (done in editor - syntax may not be correct)

/* Start new code */
             var OrderHedVar = (from o in Db.OrderHed.With(LockHint.NoLock)
                                                            // *** Add left outer join to OrderDtl
                                                 join od in Db.OrderDtl.With(LockHint.NoLock)
                                                  on new { o.Company, o.OrderNum } equals new { od.Company, od.OrderNum}
                                                 into oodj
                                                  from od in oodj.DefaultIfEmpty()

                                                            // ***


       select new

       {

          OrderNum = o.OrderNum, 
          RequestDate = o.RequestDate, 
          DocTotalCharges =
          o.DocTotalCharges,
          Company = o.Company,
          HDCaseNum = o.HDCaseNum
       }).Where(o => o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber).ToList();

  // end new code

asmar · June 15, 2018, 9:53pm

  To first validate that the left outer join syntax is working.

  To make sure the new code functions as before without any anomies and retuning the correct rows and columns.

  Then to get the necessary fields from the QuoteDtl table.

josecgomez · June 15, 2018, 10:03pm

This syntax is valid, are you getting this error at runtime?
Also how is TaskListVar created?
Also what method is this BPM on?

asmar · June 15, 2018, 9:59pm

This following is the compilation error I get after moving the where clause:

 /* Start new code */

             var OrderHedVar = (from o in Db.OrderHed.With(LockHint.NoLock)

                                                            // *** Add left outer join to OrderDtl

                                                 join od in Db.OrderDtl.With(LockHint.NoLock)

             on new { o.Company, o.OrderNum } equals new { od.Company, od.OrderNum }

     into oodj

     from od in oodj.DefaultIfEmpty()

                                                            // ***

   

 //where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber  

       select new

       {

          OrderNum = o.OrderNum, RequestDate =

o.RequestDate, DocTotalCharges = o.DocTotalCharges

       }).where o.Company == TaskListVar.Company &&

o.HDCaseNum == CaseNumber.ToList();

  // end new code

Error Detail

asmar · June 15, 2018, 10:13pm

These are last syntax error messages after moving the “where” clause.
Error Detail

Description: There is at least one compilation error.
Details:
Error CS1002: ; expected [GetTaskList.Post.Set_Quote_Number.cs(260,21)]
Program: Epicor.Customization.dll
Method: PrepareException
Line Number: 99
Column Number: 13

asmar · June 15, 2018, 10:14pm

The last changes are below:
/* Start new code */
var OrderHedVar = (from o in Db.OrderHed.With(LockHint.NoLock)
// *** Add left outer join to OrderDtl
join od in Db.OrderDtl.With(LockHint.NoLock)
on new { o.Company, o.OrderNum } equals new { od.Company, od.OrderNum }
into oodj
from od in oodj.DefaultIfEmpty()

			// ***
    
 //where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber   

       select new 
       {
          OrderNum = o.OrderNum, RequestDate = o.RequestDate, DocTotalCharges = o.DocTotalCharges

       }).where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber.ToList();

  // end new code

asmar · June 15, 2018, 10:11pm

I am getting the error when I save the BPM changes.

I am using a foreach (var TaskListVar in (from r in ttTask select r)) to loop through the TaskList rows being displayed. The TaskListVar is created for each row as I loop. This works very well with old code.

The method is Erp.TaslGetTaskList – Post-Processing.

asmar · June 15, 2018, 10:17pm

Below is the full code after the foreach loop:
foreach(var TaskListVar in (from r in ttTask select r))

{
int CaseNumber = 0;
int QuoteNumber = 0;

  CaseNumber = Convert.ToInt32(TaskListVar.Key1);
  QuoteNumber = (int) TaskListVar.TaskQuoteNum;

Chris_Conn · June 15, 2018, 11:15pm

A few things

Replace this -foreach(var TaskListVar in (from r in ttTask select r))
With this -foreach(var TaskListVar in ttTask.Where(t => t.Added || t.Updated()))

You’re where clause is wrong. Look at my example above:
}).Where(o => o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber).ToList();

asmar · June 16, 2018, 12:13am

The where clause is not needed at the TaskList level or the ttTaskList temp table. I loop through the task list rows, for each key1 column which should contain the case number most of the time.

The Where clause is needed to link to the OrderHed and to obtain other columns which will be added to the Key1=CaseNum row and so on.

Chris_Conn · June 16, 2018, 8:43pm

I think you are missing the point of what I am saying. You intend to link EVERY OrderHed in your DB to its OrderDtls (no criteria) on EVERY loop of your ttTable? That seems like a horrible idea.

I was trying to limit your joins to only needed data.

Excluding your where clause, that pattern does work to give you a left outer join - although as we’ve mentioned there seems to be no need for it in your example since you arent even selecting the data you are trying to join.

Chris_Conn · June 16, 2018, 9:27pm

Here is an example in VS. I faked some classes to emulate Db.orderHed, OrderDtl, but it works the same. Note that I had to DefaultOrEmpty to an a new blank object of the type (Dtl in this case)

     class Header
        {
            public string Company;
            public int OrderNum;
            public int HDCaseNum;
        }

        class Dtl
        {
            public string Company;
            public int OrderNum;
            public string DtlNote;
        }

        class aDb
        {
           public List<Header> OrderHed;
           public List<Dtl> OrderDtl;

           public aDb(){
                 OrderHed = new List<Header>();
                 OrderDtl = new List<Dtl>();
            }
        }
        private void button2_Click(object sender, EventArgs e)
        {
       

        aDb Db = new aDb();
            Header h1 = new Header() { Company = "A", OrderNum = 1, HDCaseNum = 69 };
            Dtl d1 = new Dtl() { Company = "A", OrderNum = 1, DtlNote = "D1"};
            Dtl d2 = new Dtl() { Company = "A", OrderNum = 1, DtlNote = "D2" };

            Header h2 = new Header() { Company = "A", OrderNum = 2, HDCaseNum = 69 };
            Dtl d3 = new Dtl() { Company = "A", OrderNum = 2, DtlNote = "D3" };
            //Dtl d4 = new Dtl { Company = "A", OrderNum = 2, DtlNote = "D4" }();

            Header h3 = new Header() { Company = "A", OrderNum = 3, HDCaseNum = 69 };

            Db.OrderHed.Add(h1);
            Db.OrderHed.Add(h2);
            Db.OrderHed.Add(h3);

            Db.OrderDtl.Add(d1);
            Db.OrderDtl.Add(d2);
            Db.OrderDtl.Add(d3);

            /* Start new code */
            var OrderHedVar = (from o in Db.OrderHed.Where(o => o.Company == "A" && o.HDCaseNum == 69) //this would be TaskList.Company, HDCasenum
                                   // *** Add left outer join to OrderDtl
                               join od in Db.OrderDtl
                                on new { o.Company, o.OrderNum } equals new { od.Company, od.OrderNum }
                               into oodj
                               from joins in oodj.DefaultIfEmpty(new Dtl())

                                   // ***


                               select new

                               {
                                  Comp = o.Company,
                                  Order = o.OrderNum,
                                  Case = o.HDCaseNum,
                                  DtlNote = joins.DtlNote

                               });

           var list = OrderHedVar.ToList();
            // end new code
        }
    }

Note that Header3 had no detail

PS - @hkeric.wci - trying out this tasty LINQ debugging plugin

asmar · June 17, 2018, 5:00am

I am so greatful that your taking the time to show me the LINQ left outer join in BPM. However, I still get the same error message. Here is the new code based on your last post.
Note for each row in the task list with a HDCaseNum, and Company we look to get the OrderHed and if successful get the OrderDtl using an outer join.

 /* Start new code */
 var OrderHedVar = (from o in Db.OrderHed.Where (o=> o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber)
			// *** Add left outer join to OrderDtl
		     join od in Db.OrderDtl.With(LockHint.NoLock) 
             on new { o.Company, o.OrderNum } equals new { od.Company, od.OrderNum }
     into oodj 
     from joins in oodj.DefaultIfEmpty()

			// *** 
    
 //where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber   

       select new 
       {
          OrderNum = o.OrderNum, 
          RequestDate = o.RequestDate,
          DocTotalCharges = o.DocTotalCharges

       });

  // end new code

Error Detail

Description: There is at least one compilation error.
Details:
Error CS1061: ‘System.Linq.IQueryable<AnonymousType#1>’ does not contain a definition for ‘OrderNum’ and no extension method ‘OrderNum’ accepting a first argument of type ‘System.Linq.IQueryable<AnonymousType#1>’ could be found (are you missing a using directive or an assembly reference?) [GetTaskList.Post.Set_Quote_Number.cs(281,50)]
Error CS1061: ‘System.Linq.IQueryable<AnonymousType#1>’ does not contain a definition for ‘RequestDate’ and no extension method ‘RequestDate’ accepting a first argument of type ‘System.Linq.IQueryable<AnonymousType#1>’ could be found (are you missing a using directive or an assembly reference?) [GetTaskList.Post.Set_Quote_Number.cs(283,58)]
Error CS1061: ‘System.Linq.IQueryable<AnonymousType#1>’ does not contain a definition for ‘DocTotalCharges’ and no extension method ‘DocTotalCharges’ accepting a first argument of type ‘System.Linq.IQueryable<AnonymousType#1>’ could be found (are you missing a using directive or an assembly reference?) [GetTaskList.Post.Set_Quote_Number.cs(284,70)]
Program: Epicor.Customization.dll
Method: PrepareException
Line Number: 99
Column Number: 13

asmar · June 17, 2018, 5:16am

In my original working code, I only link the current CaseNum on the TaskList row to the OrderHed which was working very efficiently and correctly. All I am trying to do is Link another table, the OrderDtl using a left outer join. I can use an equal join which works, but I would rather use a left outer join. Note, I am not linking the CaseNum on the Row of the tasklist to all order heads that’s not possible.

asmar · June 17, 2018, 5:19am

  /* start of old code */
	 var OrderHedVar = (from o in Db.OrderHed.With(LockHint.NoLock)
     where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber   
     //orderby l.TaskSeqNum ascending
			 //orderby l.TaskSeqNum descending
           select new 
           {
              o.OrderNum, o.RequestDate, o.DocTotalCharges

           }).FirstOrDefault();
			
		/* old code */

Chris_Conn · June 17, 2018, 7:25am

Im confused, works for me. What version?

asmar · June 17, 2018, 12:00pm

Epicor 10.1.400.17. The error occurs after I exit from the C# editor and save from the Method Directives Maintenance screen.

Mazin

Data Directive BPM C# Linq Help

These are last syntax error messages after moving the “where” clause. Error Detail

Error Detail

These are last syntax error messages after moving the “where” clause.
Error Detail