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.
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…
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;
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
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’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
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.
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;
}
}
`
