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


(Mazin Asmar) #1

​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.


Epicor Sales
(Chris Conn) #2

?

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


(Mazin Asmar) #3

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


(Chris Conn) #4

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


(Mazin Asmar) #5

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…


(Chris Conn) #6

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


(Ken Nash) #7

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

(Mazin Asmar) #8

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.


(Toby Lai) #9

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;

(Mazin Asmar) #10

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


(Toby Lai) #12

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.


(Mazin Asmar) #13

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

		
`

(Mazin Asmar) #14

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


(Mazin Asmar) #15

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

select new 
 {
        OrderNum = o.OrderNum

 }).FirstOrDefault();