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

Kinetic ERP
, ,
1

Sooo, I just got thrown a real doozer…

Apparently, we’ve got “reliable information”, that using LINQ method syntax for joins in BPMs and data directives causes the joins to happen in-memory server side (!!). But using query syntax is apparently fine.

This seems highly suspect to me. This would mean that Epicor has intentionally overriden the normal Entity Framework .Join method, for whatever reason. According the the “reliable source”, this causes massive performance issues, because whole tables are being joined on the App server instead of this being done correctly on the SQL server.

Has anyone ever heard anything of the sort? Keep in mind the “reliable source” is not just Joe Blow from XYZ inc…

2

I don’t know all implementation details, but from technical point of view BPM queries often work with so-called tt-tables. which are not database tables, but just in-memory data. It is not possible to make pure DB query when you use those tables.

You can dump SQL queries created by EF into server log using this trace flag,
<add uri="profile://system/db/epiprovider/sqltext" />
and see what exactly is retrieved.

3 Likes
3

if you do a join to a ttTable then yes the join happens in memory it has to there’s no way o send that data to sql to perform the join there.

That’s why for years we’ve been saying you should NEVER join to a ttTable or a dataset. Joining between Customer and OrderHed is fine…

joining between ttOrderHed and Customer is a bad idea. THis isn’t an Epicor issue this is just the way that the framework works Linq2SQL etc has no way to send our memorry data tot he server for a join even if structurally it is the same data.

3 Likes
4

Here’s a bunch of discussions around the topic we’ve had years ago

1 Like
5

Yes if you are using any in-memory table, joined to a regular table, the join has to happen in memory.

You can send some data down in a list for contains type stuff, but that’s about it.

Good practice to structure it to join on the least amount of data.

2 Likes
6

I am talking just about the Db context. Not ttTables or the dataset…

This makes absolutely no sense to me. But the info comes from CSG.

Tell me this blows your mind too, please…

7

Db.OrderHed.Join(Db.OrderDtl … apparently is VERY VERY BAD.

from h in Db.OrderHed join d in Db.OrderDtl… is fine.

8

This is standard behavior. They are either not explaining it very well, or you are not understanding it very well.

Or, they are full of it.

9

That is incorrect information I don’t know who the reliable source is but Db context is entity framework and joins are done using standard Linq2SQL that all is underlying framework of .NET and works properly as it should. Whoever gave you that information either misunderstands how things work or maybe something was lost in translation.

There isn’t anything magical or proprietary about the way that Epicor implements Db context it is mostly out of the box using EF.

3 Likes
10

That is what I would expect also. But then it puts us in a difficult situation with the customer…

11

Ok now I’m lost. :popcorn:

12

They are putting out this argument to the customer after having analyzed a bunch of BPMs for performance issues.

13

No it doesn’t, it is ok to be wrong. CSG isn’t all knowing or maybe you misunderstood but it is fine to go back and correct the mis-information.

Either way this is extremely easy to test out / provem out and I believe there may even be an example or 30 on this site of SQL Traces against Linq queries.

2 Likes
14

I hope I misunderstood. But the end result is the same unfortunately.

15

If you wanna share one of those slow bpms I’m sure we can take a look at them and see if we can find improvements that don’t require a rewrite of entitfy framework.

Maybe some of the BPMs had joins to ds/tt tables and that’s what they were talking about

2 Likes
16

The solution they suggested (to the customer, not to us!) is that we rewrite ALL our linq queries to use query syntax. They specifically said method syntax is the problem…

So you see how we’re boned… If Epicor says X, how can we possibly argue?

1 Like
17

Yeah, no.

Frustrated Parks And Recreation GIF

18

They aren’t infalible… you should see some of the bug reports we’ve summited :stuck_out_tongue:

2 Likes
19

Sounds like the suggested solution is job security for you though. Now you have to rewrite everything!! lol

What is the size of this project? Think the BPM/Customization search tool could help figure this out. (search for .Join( )

20

Easier to define joins though :rofl:

1 Like