Been getting into using Epicor Functions for common lookups and calculations, but I’m running into something I did not expect.
In normal BPM’s you can use a LINQ C# Expression to lookup data in other tables (like a User’s Email address). But when creating a Function, you have to define all the References for the Library. I can’t work out the Assembly or Services to add to be able to use LINQ.
Anyone else already figured this out? (@hkeric.wci)
You need to add tables in References when referring to data like user’s email address and you can use like others BPMs.
Already did that, I get the error 'The name “‘Db’ does not exist in the current context”
Are you trying update a field? If yes, then you need to define Read Write with Update flag check against the table.
No this is just a LINQ query looking up a value and assigning it to an Argument:
Db.UserFile.FirstOrDefault(u => u.DcdUserID == callContextClient.CurrentUserId).EMailAddress
You can try this
var UserFile = Db.UserFile.FirstOrDefault(u => u.DcdUserID == Session.UserID);
string lEmail = (UserFile != null ? UserFile.EMailAddress : "") ;
I’m not writing a Custom Code Widget, I’m using a ‘Setter’ widget.
The expression I posted works fine in other BPM’s, the only difference is this is a Function, so I suspect I am missing an Assembly Reference. Function scope seems to be different.
I have opened an Epicor Case, I was just hoping someone else had already worked this out.
((Erp.ErpContext)((Epicor.Functions.IFunctionHost)this.GetType().BaseType.BaseType.GetField("host", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(this)).GetIceContext()).UserFile.FirstOrDefault(u => u.DcdUserID == callContextClient.CurrentUserId).EMailAddress
or
((Erp.ErpContext)(Ice.Services.ContextFactory.CreateContext()).GetIceContext()).UserFile.FirstOrDefault(u => u.DcdUserID == callContextClient.CurrentUserId).EMailAddress
or
// Once you add the table, you should be able to use the following.
// Note: The Editor will throw an error because its a partial class, if you ignore it and save it should work fine.
this.Db.UserFile.Where(u => u.DcdUserID == callContextClient.CurrentUserId).Select(x => x.EMailAddress).First()
Thanks @hkeric.wci
But I am trying to do this as a widget function.
I’m trying to do this without Reflection.
Also this is in a Cloud environment.
Other BPM’s where I use LINQ works fine, it’s almost like Functions do not have the same scope.
I’ve already added the UserFile table Reference.
It seems like I need an Assembly reference that will allow me to use LINQ.
…Still poking around…
If you add the table and do
this.Db.UserFile.Where(u => u.DcdUserID == callContextClient.CurrentUserId).Select(x => x.EMailAddress).First()
You will get an error, ignore it, press ok and that should work.
Here is Epicor’s Fill Table Generated Code and below my widget expression despite the Designer Error:
I assume the error only happens because the Designer doesn’t see the this.Db variable since its validating only a Partial Class.
Side Note - On-Prem View EFx Code
If you are on-prem and would like to see the code your EFx generates you can add the following to your web.config
It will then generate your code into the following server side folder:
Example: C:\inetpub\wwwroot\EpicorERP\Server\BPM\Sources
Thanks @hkeric.wci that’s what I needed.
The ol’ you get an error but it’s ok… I just needed to use this.db and actually try it despite the error.
I usually use the Epicor Widgets, generate examples and look at the code. It is odd however that the widgets don’t fail, but the Expression does. It may just be the Expression Editor Validator, which doesn’t run against a normal widget.
This is how the base .cs file looks like:
They are basically doing the same thing as in the above post:
((Erp.ErpContext)(Ice.Services.ContextFactory.CreateContext()).GetIceContext()).UserFile.FirstOrDefault(u => u.DcdUserID == callContextClient.CurrentUserId).EMailAddress
I dont like to ignore errors and it seems yucky to do it… But I am under the assumption that it will be fixed and its a bug. I know EFx early discussions stated there won’t be access to a Db Context period. But as we got into .600 and .700 looks like, they are improving it.
CORRECTION! THIS WORKS FINE!!!
Ok, I had some time to test this more thoroughly and here is what I have found as of 10.2.700.2:
-
My syntax above worked just fine and I was getting terribly distracted by the error. (meaning I didn’t have a problem to begin with).
-
The only requirement to use this LINQ syntax is that you have references for all the Tables you are using.
-
If there is an actual compilation problem you will get an error when you actually save and activate the Function.
-
Someone had suggested that the Library’s DB Access from Code may have been the issue. This seemed reasonable as at the time mine was set to ‘none’ and @hkeric.wci ‘this.db’ seemed to work fine with that. So I switched my syntax back to my original and changed the DB Access from Code and it didn’t seem to have any affect on the LINQ query.
-
I suspect Functions are still a bit buggy and the C# Expression syntax checker doesn’t have the proper scope defined to check the expression properly. It seems it’s not aware of the References of the Library.
Just wanted to set the record straight… as of 10.2.700.2
I do have an Epicor Case on this issue and I have updated Epicor Support on what I have found.