BPM join on anonymous type

Epicor ERP 10
1

I am trying to join Task on HDCase on HDCaseNum = Task.Key1.
Task.Key1 is a string and HDCaseNum is an int.

This is my code:

/*Join HDCase*/
                      join caseRow in Db.HDCase.With(LockHint.NoLock)
                      on new {HDCaseNum = Convert.ToInt32(addedTaskRow.Key1), addedTaskRow.Company} equals new {HDCaseNum = caseRow.HDCaseNum, caseRow.Company}//join on anonymous types with common name and type

This compiles of course but throws a runtime error about

**LINQ to Entities does not recognize the method 'Int32 Int32(System.String)' method, and this method cannot be translated into a store expression.**`

How can I join these explicitly without forcing it in memory?

1 Like
2

I created a variable outside the join

int key1 = Convert.ToInt32(addedTaskRow.Key1);

then joined on the variable:

 /*Join HDCase*/
                      join caseRow in Db.HDCase.With(LockHint.NoLock)
                      on new {HDCaseNum = key1, addedTaskRow.Company} equals new {HDCaseNum = caseRow.HDCaseNum, caseRow.Company}//join on anonymous types with common name and type
2 Likes
3

If you want to keep it in memory, you can use Erp.ErpEFFunctions:

join caseRow in Db.HDCase.With(LockHint.NoLock)
on new {HDCaseNum = Erp.ErpEFFunctions.ConvertToInt(addedTaskRow.Key1), addedTaskRow.Company} equals new {HDCaseNum = caseRow.HDCaseNum, caseRow.Company}

https://www.epiusers.help/t/linq-cast-key1-to-integer-string-to-int-to-get-max/55201/7

1 Like
4

In 10.2 you can join on ToString(), but you couldn’t in 10.1.

5

@tsmith,
is Erp.ErpEFFunctions a temporary session table or is it one dimensional matrix? i.e. can i create as many as i need of variables on the fly and save them there?

6

It’s a C# namespace containing methods you can use in LINQ expressions. I’m not sure if there’s any explicit documentation on what exactly the functions do, though.
image

1 Like
7

ahaa thanks, but in this case what would be the reason to use it, the built-in function will work straight away i.e. why Erp.ErpEFFunctions.ConvertToInt(addedTaskRow.Key1) instead of addedTaskRow.Key1.ConvertToInt() ?

8

String.ConvertToInt() isn’t a valid method (at least in version 10.2.300.15), and if you try to use Convert.ToInt32(string), the code will compile, but you’ll get the runtime error Aaron pointed out at the top of this thread.

LINQ uses the Entity Framework which has limitations on the functions you can use in the queries, since the queries are translated into SQL and use SQL operations and functions. ErpEFFunctions provides Entity Framework-safe functions you can use when the regular functions (Convert.ToInt32, etc.) won’t work.

1 Like
9

aha, many thanks mate, just read your post about keeping variable in memory, and did not read Aaron issue,:ok_hand:

10

Didn’t know that existed, thanks!