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…
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.
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.
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.