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

[jgiese.wci (Joshua Giese)]

Post summary/solution

@Epic_Santiago

There’s a pretty good chance that I’m the mysterious “reliable source” so allow me to dispel some confusion.

I reviewed a BPM that was reading an entire table (as confirmed via SQL Profiler) as part of an EF query. I rewrote the query to fix the issue and could now see the where clause in the profiler.

The fact that it was re-written in query syntax instead of method syntax has nothing to do with the fix. Either syntax will compile to the same thing and Epicor is not doing anything nefarious here.

It is possible to write a good query with either syntax and a bad query with either syntax. The bottom line is that the problem was a bad query, not the flavour of syntax.

Method syntax was not discouraged nor blamed as the source of the problem. If I gave that impression (assuming I’m the mystery man) then I’ll take the blame for not being clearer.

Query build syntax

@josecgomez

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

MethodVsQueryJoinsKinetic2023 - Diffchecker

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

Epicor object/compiler results

@jgiese.wci

I added these equal queries of each syntax to a BPM

var queryMethodSyntax = Db.Part
    .Join(Db.PartRev, part => new { part.Company, part.PartNum }, rev => new { rev.Company, rev.PartNum }, (part, rev) => new { part, rev })
    .Where(joined => joined.rev.Approved == true)
    .Select(joined => new { joined.part, joined.rev });
var queryRegularSyntax =
    from part in Db.Part
    join rev in Db.PartRev on new { part.Company, part.PartNum } equals new { rev.Company, rev.PartNum }
    where rev.Approved == true
    select new { part, rev };

I then decompiled them and the decompiled code out of Ice.CustomizationStore was the same with the exception of one directly queryable the other was an anonymous function delegate. So even at the c# level nothing is managed different in terms of memory. This would indicate no funny business from Epicor.

When I looked into the diffrence between the expression tree vs enumerable it actually sounds like the enumerable would possibly be microseconds slower and one occurs in memory and the other out of memory. That just determines when the query happens though nothing else. one occurs when the statement is written the other occurs when you start a foreach on it. Nothing else that I can find.