I’ll rewrite all of them for $200 an hour, whether they need it or not.
2 Likes
I’ll do it for $400/hr but do it in half the time.
2 Likes
2 Likes
I am someone who uses Method Syntax like the plague… Haven’t experienced any issues… I have 100s of complex queries like this…
I havent tested it against 2023.2 – not sure.
Func<string, int, int?, decimal> GetPOLinesAmountThatHaveFinal2 = (pCompany, pPONum, pPOLine) =>
{
var rows = Db.PODetail.Where(x => x.Company == pCompany && x.PONUM == pPONum);
if (pPOLine > 0)
{
rows = rows.Where(x => x.POLine == pPOLine);
}
else
{
rows.Where(y =>
Db.POMisc
.Join(Db.PurMisc,
pm => new { pm.Company, pm.MiscCode, LCFlag = true },
pur => new { pur.Company, pur.MiscCode, pur.LCFlag },
(pm, pur) => pm)
.Where(x => x.Company == y.Company && x.PONum == y.PONUM && x.POLine == y.POLine)
.Any() == false
);
}
return rows.Select(x => x.ExtCost).DefaultIfEmpty(0).Sum();
};
Func<string, int, int?, decimal> GetPOMiscChargesAmount = (pCompany, pPONum, pPOLine) =>
{
return Db.POMisc
.Join(Db.PurMisc,
pom => new { pom.Company, pom.MiscCode },
pum => new { pum.Company, pum.MiscCode },
(pom, pum) => pom)
.Where(x => x.Company == pCompany && x.PONum == pPONum && x.POLine == pPOLine)
.Select(x => x.MiscAmt).DefaultIfEmpty(0).Sum();
};
// This Excludes Lines with LC Charges
Func<string, int, int?, decimal> GetPOLinesAmountThatHaveFinal = (pCompany, pPONum, pPOLine) =>
{
var rows = Db.PODetail
.Where(x => x.Company == pCompany && x.PONUM == pPONum)
.Select(pod => new
{
pod.Company,
PONum = pod.PONUM,
pod.POLine,
pod.ExtCost,
LCCharges = Db.POMisc
.Join(Db.PurMisc,
pm => new { pm.Company, pm.MiscCode, LCFlag = true },
pur => new { pur.Company, pur.MiscCode, pur.LCFlag },
(pm, pur) => pm)
.Where(x => x.Company == pod.Company && x.PONum == pod.PONUM && x.POLine == pod.POLine)
.Select(x => x.MiscAmt).DefaultIfEmpty(0).Sum()
});
if (pPOLine > 0)
{
rows = rows.Where(x => x.POLine == pPOLine && x.LCCharges > 0);
}
else
{
rows = rows.Where(x => x.LCCharges == 0);
}
return rows.Select(x => x.ExtCost).DefaultIfEmpty(0).Sum();
};
Func<string, int, int, IQueryable<Erp.Tables.POMisc>> GetPOLCMiscChargesRows = (pCompany, pPONum, pPOLine) =>
{
return Db.POMisc
.Join(Db.PurMisc,
pom => new { pom.Company, pom.MiscCode, LCFlag = true },
pum => new { pum.Company, pum.MiscCode, pum.LCFlag },
(pom, pum) => pom)
.Where(x => x.Company == pCompany && x.PONum == pPONum && x.POLine == pPOLine)
.Select(x => x);
};
Func<string, int, string, string, int?, decimal> GetReceiptValueRemaining = (pCompany, pVendorNum, pPurPoint, pPackSlip, pPackLine) =>
{
var rows = Db.RcvDtl
.Join(Db.PORel,
rd => new { rd.Company, rd.PONum, rd.POLine, rd.PORelNum },
pr => new { pr.Company, pr.PONum, pr.POLine, pr.PORelNum },
(rd, pr) => new { rd, pr })
.Where(x => x.rd.Company == pCompany && x.rd.VendorNum == pVendorNum && x.rd.PurPoint == pPurPoint && x.rd.PackSlip == pPackSlip);
if (pPackLine > 0)
{
rows = rows.Where(x => x.rd.PackLine == pPackLine);
}
else
{
rows = rows.Where(y =>
Db.POMisc
.Join(Db.PurMisc,
pum => new { pum.Company, pum.MiscCode, LCFlag = true },
pur => new { pur.Company, pur.MiscCode, pur.LCFlag },
(pum, pur) => pum)
.Where(x => x.Company == y.rd.Company && x.PONum == y.rd.PONum && x.POLine == y.rd.POLine)
.Any() == false
);
}
return rows.Select(x => ((x.pr.RelQty - x.pr.ArrivedQty) / x.pr.PurchasingFactor) * x.rd.VendorUnitCost)
.DefaultIfEmpty(0).Sum();
};
5 Likes
Just talked with my team lead, I will write a test suite for it. Otherwise we have to spend hours rewriting some 400 queries, so it’s time well invested anyway…
Haso you need to re-write all of those
Jk of course
You have 400 queries with Db Context lookups!!! why!?!?!
1 Like
Because it’s way more efficient than querying the same info through the BOs? 
what happened to the post @hkeric.wci ???
maybe for another thread… Are you converting them to functions? or is this an external dll?
1 Like
Anyways as Jose pointed out I know if you mix Tablesets with Db it will bring things in memory like ttTable I think callContext as well… but if its just mixing Db with Db it should work fine.
1 Like
I am really glad that the thread that Jose posted with Tim is being expanded upon a little bit right now.
Here’s the same query written both ways and the equivalent executued SQL (Recorded by SQL profiler)
LINQ Query (Methods):
var query = Db.OrderHed
.Where(orderHed => orderHed.ReadyToCalc == true && orderHed.EntryPerson=="EPI-METHOD")
.Join(Db.OrderDtl,
orderHed => orderHed.OrderNum,
orderDtl => orderDtl.OrderNum,
(orderHed, orderDtl) => new { orderHed, orderDtl })
.Join(Db.Part,
combined => new { combined.orderDtl.Company, combined.orderDtl.PartNum },
part => new { part.Company, part.PartNum },
(combined, part) => new { combined.orderHed, combined.orderDtl, part })
.Join(Db.Customer,
combined => new { combined.orderHed.Company, combined.orderHed.CustNum },
customer => new { customer.Company, customer.CustNum },
(combined, customer) => new { combined.orderDtl, combined.part, customer })
.GroupBy(x => new { x.part.PartDescription, x.customer.AccountCoordinator_c })
.Select(grouped => new
{
PartManager = grouped.Key.PartDescription,
AccountCordinator = grouped.Key.AccountCoordinator_c,
OrderDtls = grouped.Select(x => x.orderDtl).ToList()
}).ToList();
Linq Query Syntax:
var query2 = (from orderHed in Db.OrderHed
where orderHed.ReadyToCalc == true && orderHed.EntryPerson=="EPI-LINQ"
join orderDtl in Db.OrderDtl on orderHed.OrderNum equals orderDtl.OrderNum
join part in Db.Part on new { orderDtl.Company, orderDtl.PartNum } equals new { part.Company, part.PartNum }
join customer in Db.Customer on new { orderHed.Company, orderHed.CustNum } equals new { customer.Company, customer.CustNum }
group new { orderDtl, part, customer } by new { part.PartDescription, customer.AccountCoordinator_c } into grouped
select new
{
PartManager = grouped.Key.PartDescription,
AccountCordinator = grouped.Key.AccountCoordinator_c,
OrderDtls = grouped.Select(x => x.orderDtl).ToList()
}).ToList();
SQL Diff
It literally generated the EXACT SAME SQL Down the the intermediate table names. I purposely chose a mildly complex query with joins where clauses and grouping.
This was in a Pre Processing BPM on ABCode->GetNew()
5 Likes
umm. that could be like a million threads. which one is this you speak of?
1 Like
The one near the top of this thread Apparently, LINQ method syntax for joins is broken in BPMs...? - #4 by josecgomez
Yup
Godspeed @HLalumiere , I think we all have been pitted into one of these situations at one point or another.
Thank you @josecgomez for taking the time to expand on your previous discourse.
1 Like
STOP TALKING TO YOURSELF!!!
Man , you’re fast… lol
Since I do not have access to the profiler on my own, would you be so kind as to redo your test with the .Where clause after the joins? I’ll have all I need then… 
Thanks a bunch!
1 Like