Epicor BPM LINQ in C# left outer join two tables with DefaultIfEmpty() is not working

​I have a working BPM with an LINQ left outer join. I found several examples online. The code I have compiles and runs during form execution but does not work as advertised. The DefaultIfEmpty() function is suppose work for an left outer join. In my example, I have a main table QuoteHed and lookup table SchedPri. I can only get the main table rows that equals the look table rows. I am trying to get all rows of the main table. See below:

var QuoteListVar = (from s in Db.QuoteHed.With(LockHint.NoLock)
                    join p in Db.SchedPri.With(LockHint.NoLock) on s.rsiSchedCode_c equals p.SchedCode into pSubGroup
                    from b in pSubGroup.DefaultIfEmpty()
                   where s.Company == TaskListVar.Company && s.HDCaseNum == CaseNumber && s.Company == b.Company
               select new
               {
                 HDCaseNum = s.HDCaseNum,
                 QuoteNum  = s.QuoteNum,
                 SchedDesc = (b != null? b.Description : "NoCode" )

               }).FirstOrDefault();


  if (QuoteListVar != null )
           {
  TaskListVar["rsiQuoteNum_c"]  = QuoteListVar.QuoteNum;
           TaskListVar["rsiHDCaseNum_c"] = QuoteListVar.HDCaseNum;
           TaskListVar["rsiSchedDesc_c"] = QuoteListVar.SchedDesc;
  //Epicor.Customization.Bpm.InfoMessage.Publish("Mazin Testing - " + CaseNumber);

           }

In previous working BPMs, I have been successful with two and three equal join tables which are extremely more efficient.

?

left join p in Db.SchedPri.With(LockHint.NoLock) on s.rsiSchedCode_c equals …

I get an error and my BPM will not compile. I also tried left outer join.

Hmmm LINQ isn’t my forte. Maybe there is some help here:

I tried many of these proposed examples as you can judge by my code. The issue is that the rows returned are always of the INNER join instead of the LEFT OUTER JOIN. This is verified because no rows are returned in the var QuoteListVar when the s.rsiSchedCode_c is null or empty…

Slap this guy around, he probably knows -> @knash

Could be two changes. Does this work? I am thinking the variable change will not be the fix more of the where clause.

from https://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9/description#leftouterjoin

var QuoteListVar = (from s in Db.QuoteHed.With(LockHint.NoLock)
                    join p in Db.SchedPri.With(LockHint.NoLock) on s.rsiSchedCode_c equals p.SchedCode into pSubGroup
                    from p in pSubGroup.DefaultIfEmpty()
                   where s.Company == TaskListVar.Company && s.HDCaseNum == CaseNumber
               select new
               {
                 HDCaseNum = s.HDCaseNum,
                 QuoteNum  = s.QuoteNum,
                 SchedDesc = (p != null? p.Description : "NoCode" )

               }).FirstOrDefault();

I tried all types of combinations but it did not work. Epicor support will not deny or confirm their BPM LINQ environment supports left outer joins. I moved on to an alternate programmatic solution.

Maybe is a bit late, but hope this will help someone out there.

I managed to convert an SQL query with left join into LINQ

select 
	[UD02].[Key1] as [UD02_Key1],
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
	[OrderDtl].[OrderLine] as [OrderDtl_OrderLine]
from Ice.UD02 as UD02
left outer join Erp.OrderDtl as OrderDtl on 
	UD02.Company = OrderDtl.Company
	and UD02.Character01 = OrderDtl.ProjectID
	and UD02.Date01 = OrderDtl.NeedByDate
	and UD02.DeliveryLoad_c = OrderDtl.DeliveryLoad_c
where OrderDtl.OrderNum is null

into Linq

	    var UD02RowList = from UD02Row in Db.UD02.With(LockHint.UpdLock)
                  join OrderDtlRow in Db.OrderDtl
                  on new {Company = UD02Row.Company, Character01 = UD02Row.Character01, Date01 = UD02Row.Date01,  DeliveryLoad_c = UD02Row.DeliveryLoad_c} equals 
                    new  {Company = OrderDtlRow.Company, Character01 = OrderDtlRow.ProjectID, Date01 = OrderDtlRow.NeedByDate, DeliveryLoad_c = OrderDtlRow.DeliveryLoad_c}
                    into jointData
                  from jointRecord in jointData.DefaultIfEmpty()
                      
                  where jointRecord.OrderNum ==  null
                      select UD02Row;
2 Likes

Dose this LINQ compile and run from a BPM method event?

Yes, the code is copied from a working Method Directive BPM and is working nicely. I am on 10.2.200.4

The DefaultIfEmpty() seems to work. I used LinqPAD to generate the query.

I was able to use your example to make my query compile without errors. However, when I try to reference/set a field from one of selected query columns, I get the error message. See below for more details:
Error message:

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(391,53)]
Program:  Epicor.Customization.dll
Method:  PrepareException
Line Number:  99
Column Number:  13
`

New left outer join:

var OrderHedDtlVar = (from o in Db.OrderHed.With(LockHint.NoLock)
join od in Db.OrderDtl.With(LockHint.NoLock) on o.OrderNum equals od.OrderNum into jointData_od
join cu in Db.Customer.With(LockHint.NoLock) on o.CustNum equals cu.CustNum into jointData_cu
from jointRecord_od in jointData_od.DefaultIfEmpty()
from jointRecord_cu in jointData_cu.DefaultIfEmpty()
where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber && jointRecord_od.OrderLine==1

     //orderby l.TaskSeqNum ascending
//orderby l.TaskSeqNum descending

select new
{
o.OrderNum, o.RequestDate, jointRecord_od.PickListComment, jointRecord_cu.CreditHold

           });

 if (OrderHedDtlVar != null)
{
	TaskListVar["rsiOrderNum_c"] = OrderHedDtlVar.OrderNum; <-- error occurs at this line. 

}

`
old code without the left outer join:

    	
var OrderHedDtlVar = (from o in Db.OrderHed.With(LockHint.NoLock)
join od in Db.OrderDtl.With(LockHint.NoLock) on o.OrderNum equals od.OrderNum
join cu in Db.Customer.With(LockHint.NoLock) on o.CustNum equals cu.CustNum 
 where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber &&  od.OrderLine==1 
           
         //orderby l.TaskSeqNum ascending
				 //orderby l.TaskSeqNum descending
               select new 
               {
                  o.OrderNum, o.RequestDate, od.PickListComment, cu.CreditHold

               }).FirstOrDefault();  

		
`

I am at 10.1.400.38 perhaps this is the reason why I am getting the error message.

I also tried using the following and it resulted in the same error message:

select new 
 {
        OrderNum = o.OrderNum

 }).FirstOrDefault();

Sorry for the thread-necro

I’m having a similar problem, I need a BPM that’ll loop through a list of a Customer’s open SO#s that do not have any POs or Jobs linked. In a BAQ, I’ve done it with couple Left Outer joins and a Where checking for null returns. But having problems converting it into a LINQ for the BPM.
I’ve tried modifying the query Nate created in another thread LINQ nullable Bool? - #6 by Randy but so far no luck.

select 
	oh.OrderNum as OrderNum,
	oh.OpenOrder as OpenOrder,
	oh.CreditOverride as CreditOverride

from OrderHed as oh
left outer join Erp.JobProd as jp 
	on oh.Company = jp.Company and oh.OrderNum = jp.OrderNum
left outer join Erp.PORel as PORel 
	on oh.Company = PORel.Company and oh.OrderNum = PORel.BTOOrderNum

where  ( jp.JobNum is null and PORel.PONum is null ) and oh.OpenOrder = 1 and oh.CustNum = 'CustVar' 

Coincidentally I’ve been wrestling with something similar, and the following approach seems workable:

from orderrow in Db.OrderHed
where !(from porow in Db.PORel select porow.BTOOrderNum).ToList().Contains(orderrow.OrderNum)
&& !(from jrow in Db.JobProd select jrow.OrderNum).ToList().Contains(orderrow.OrderNum)
&& orderrow.OpenOrder
select orderrow
1 Like

Daryl,
I think your query is going to pull the all the porow.BTOOrderNum and jrow.OrderNum records into ram and maybe orderhed and do the work there.

If the query gets too painful in LINQ I will sometime just make a BAQ and call the BAQ in the BPM. Or have the BAQ do the work with a custom action.

Awesome, I’ll try it out.

Hmm, thanks, a possibility I had not considered.

Yes, calling a BAQ within the BPM is normally my approach too.

But I’ve just been working on something where a BAQ isn’t workable because I need to join to a table created from REST calls to a different system, so I’ve had to find another way. Maybe I’ve been lucky in that I’ve filtered my DB calls sufficiently, but it does seem to function effectively and without showing signs of hogging resources.

I’m no LINQ guru, though, so I’m hazy on how this type of query might be interpreted and whether it might cause problems in circumstances other than the ones I’ve used it.

1 Like

The following is a working example of an LINQ left outer join in Epicor 10.1.400.38


   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;
			TaskListVar["rsiHDCaseNum_c"] = CaseNumber;


 		 int myOrderNumber = 0;
  		 
			// MGA - 31-OCT-2018 - Example of outer join
			var OrderHedDtlVar = (from o in Db.OrderHed.With(LockHint.NoLock)
			join od in Db.OrderDtl.With(LockHint.NoLock) on new {OrderNumv1 = o.OrderNum, Company = o.Company} equals new {OrderNumv1 = od.OrderNum, Company=od.Company} into jointData_od
			        	  join cu in Db.Customer.With(LockHint.NoLock) on o.CustNum  equals cu.CustNum  into jointData_cu
			         	from jointRecord_od in jointData_od.DefaultIfEmpty()
			        	 from jointRecord_cu in jointData_cu.DefaultIfEmpty()
			       	  where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber && jointRecord_od.OrderLine==1 
			          
			  //orderby l.TaskSeqNum ascending
				//orderby l.TaskSeqNum descending
			              
			               select new  
			               {
			                 OrderNumv = o.OrderNum, o.RequestDate, jointRecord_od.PickListComment, jointRecord_cu.CreditHold
			               }).FirstOrDefault(); 
			
			 if (OrderHedDtlVar != null)
				{
			
					TaskListVar["rsiOrderNum_c"] = OrderHedDtlVar.OrderNumv;

           // MGA - 05/04/2018 - Added the RequestDate (ShipBy) from the OrderHed table, requested by M
           TaskListVar["rsiRequestDate_c"] = OrderHedDtlVar.RequestDate;
					 // MGA - 27-JUN-2018, Removed OrderHed.DocTotalCharges
           //TaskListVar["rsiOrderHedDocTotalCharges_c"] = OrderHedDtlVar.DocTotalCharges;
           TaskListVar["rsiOrderDtlPickListComment_c"] = OrderHedDtlVar.PickListComment;
					 TaskListVar["rsiCustomerCreditHold_c"] = OrderHedDtlVar.CreditHold;

			
				}
   }
`