Epicor Function - UD Table breaks LINQ query?

Epicor ERP 10
, , ,
1

This is what i have currently, for some reason, when I try to add in my UD table as a join, it breaks the linq query, doing ctrl+space doesn’t suggest any methods when it’s added. I’ve checked and I’ve referenced the UD table in the function as well.

var irows =  (from id in Db.InvcDtl
              join ih in Db.InvcHead on
                new {id.Company, id.InvoiceNum} equals
                new {ih.Company, ih.InvoiceNum}
              /*join u in Db.UD10 on
                new {id.Company, id.CustNum, id.PartNum} equals
                new {u.Company, u.Key1, u.Key2}*/
              join p in Db.Part on 
                new {id.Company, id.PartNum} equals
                new {p.Company, p.PartNum}
              join pv in Db.PartXRefVend on
                new {id.Company, id.PartNum} equals
                new {pv.Company, pv.PartNum}
              join cs in Db.Customer on
                new {id.Company, id.CustNum} equals
                new {cs.Company, cs.CustNum}
              where
                id.Company == Session.CompanyID
              select
                new {id.InvoiceNum}).ToArray().Take(30);
2

Works for me. Did you add ICE.UD10 as a reference table? 9/10 times I run into this, it’s because I forgot the ref.

If UD10 has been added, did you try casting u.Key1 as an int (or id.CustNum as a string)? Can’t recall if that is an issue in LINQ, but I know I’ve run into type mismatches in SQL joins before. Though, if that were the case, intellisense should still return UD10 objects. It just might throw some other error.

3

For a linq query join to work the fields need to be named the same also CustNum is an integer and Key1 is a string that is not going to work even if you alias them your best bet is to just lookup the us table later individually

You could try Convert.ToInt32() but I don’t believe that will translate to Sql

4

I think that throws an error at runtime along the lines “LINQ-to-entity does not contain a definition for Convert.ToInt32().” It compiles and let’s you save, but won’t execute. Ran in to that a week ago.

5

I think this was it, however LINQ doesn’t like it when I tried converting the type. I assume I can probably query the UD table on it’s own, cast it and then somehow combine the two different tables together, any tips?

6

Tried a round-about way to getting this to work.

  • created a new integer UD field in UD10 - “Interger01”
  • set a data directive bpm to convert the string value in Key1 to an integer value in Integer01 (i know that I’ll only have numeric values within Key1 so it should be okay)

Then i tried joining UD10 using Integer01 onto my InvcDtl table using CustNum, but i’m still getting errors.

System.Drawing.Bitmap	CS1941	The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'.

Thoughts?

7

Can you show the Linq query?

8

I renamed my UD field “iNumber01_c” and has int as it’s type.

var irows =  (from id in Db.InvcDtl
              join ih in Db.InvcHead on
                new {id.Company, id.InvoiceNum} equals
                new {ih.Company, ih.InvoiceNum}
              join u in Db.UD10 on
                new {id.CustNum} equals
                new {u.iNumber01_c}
              join p in Db.Part on 
                new {id.Company, id.PartNum} equals
                new {p.Company, p.PartNum}
              join pv in Db.PartXRefVend on
                new {id.Company, id.PartNum} equals
                new {pv.Company, pv.PartNum}
              join cs in Db.Customer on
                new {id.Company, id.CustNum} equals
                new {cs.Company, cs.CustNum}
              where
                id.Company == Session.CompanyID
              select
                new {id.InvoiceNum}).ToArray().Take(30);
9

This probably isn’t the problem based on the error, but try using u["iNumber01_c"] instead of u.iNumber01_c

10

Different error in this case

              join u in Db.UD10 on
                new {id.CustNum} equals
                new {u["iNumber01_c"]}

System.Drawing.Bitmap	CS0746	Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.
11

Ah I see. I have trouble accessing UD fields sometimes with row.UDField in BPMs and that fixes it. If the type is int I can’t imagine why it would be giving you a type mismatch error. CustNum is always int.

12

Since its throwing that error at you, maybe you can store the UD10.iNumber01 in a different variable (list or int) with it’s own LINQ query, then use that in the where clause of the main query?

13

What @josecgomez was saying is that the fields have to match and if not you need to assign them the same name.

needs to be something like

new {CustNum  = id.CustNum} equals  new {CustNum = u.iNumber01_c}
4 Likes
14

As mentioned, the keys need to have the same name, and also, you cannot use Convert.ToInt32 in an EF6 query. However you CAN convert an int to a string, using the EF6 canonical functions:

using System.Data.Entity.SqlServer;

var irows =  (from id in Db.InvcDtl
              join ih in Db.InvcHead on
                new {id.Company, id.InvoiceNum} equals
                new {ih.Company, ih.InvoiceNum}
              join u in Db.UD10 on
                new {id.Company, Key1 = SqlFunctions.StringConvert((double)id.CustNum), Key2 = id.PartNum} equals
                new {u.Company, u.Key1, u.Key2}
              join p in Db.Part on 
                new {id.Company, id.PartNum} equals
                new {p.Company, p.PartNum}
              join pv in Db.PartXRefVend on
                new {id.Company, id.PartNum} equals
                new {pv.Company, pv.PartNum}
              join cs in Db.Customer on
                new {id.Company, id.CustNum} equals
                new {cs.Company, cs.CustNum}
              where
                id.Company == Session.CompanyID
              select
                new {id.InvoiceNum}).ToArray().Take(30);

This is for EF6 (Epicor 10.2)… In Kinetic (EF Core), you can just use Convert.ToString() (or Convert.ToInt32()). Technically in EF6.1 you can use .ToString() directly, but not sure if there is any Epicor version that uses 6.1…

1 Like