Apparently, LINQ method syntax for joins is broken in BPMs...?

Kinetic ERP
, ,
35

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

Case Closed Yes GIF by Disney Channel

5 Likes