Using Linq (Language Integrated Query) In Epicor BPMs

For Epicor ERP: Kinetic, v10, & v9

For many new Epicor developers, one of the first hurdles to get over is using Linq if they have not used it before. Linq (Language Integrated Query) is a modern C# technique for querying all of the bits in the programming world that are not databases.

Consider a complex array, for example, where you want to pull out all of the values that match certain criteria. If it was a database you’d query it using SQL language (SELECT * FROM TABLE WHERE X=’123’), but for non-database objects, it was always much more tedious. So enter Linq which gives us a query interface for these non-database objects but at the same time tries to fix some age-old paradoxes in SQL language (like why do I say what values I want before telling it where I want it from). If you have an application that is composed of both database and non-database objects it becomes tedious to have two different query languages - enter Linq to SQL.

This is where we start off with Epicor as they have very sensibly standardized on using Linq as the universal query language in BPMs. The rest of this article is going to focus on table queries within Epicor BPMs but the concepts are the same for any sort of objects you may wish to query.

The Syntax

The first thing to know about Linq is that there are two types of syntax for it - “Query Syntax” and “Lambda Syntax”. Query syntax will be more familiar to you if you come from a SQL background and Lambda syntax will be more familiar if you come from a web programming background. They both have the same end result - it is purely a matter of preference.

The Query

So let’s start with a really basic Linq query - getting a list of all of the active employees. Epicor stores employee data in a table named EmpBasic and there is a field for EmpStatus that is set to ‘A’ if they are active. Here is what that query looks like:

// Query Syntax
var emps = (from e in Db.EmpBasic
                where 
                  e.Company == "EPIC06"
                  && e.EmpStatus == "A"
                select new { e.EmpID, e.Name } );

// Lambda Syntax
var emps = Db.EmpBasic.Where(e => e.Company == "EPIC06"
                               && e.EmpStatus == "A");

So as you can see here, very similar but query syntax is going to look more like what you’d be used to with a SQL background - with the biggest difference being the “SELECT” part is moved to the end.  Lambda comes out a little more condensed.

Now if you want to loop through these results you can just do something like this:

foreach(var emp in emps) {
    string blah = emp.EmpID + ' ' + emp.Name;
}

You will notice here that the columns from the database (EmpID and Name) are accessible as simple properties as we iterate over the results - that works for only non UD fields.  If you wish to access a UD field you need to use the square bracket syntax like so:

foreach(var emp in emps) {
    string blah = emp.EmpID + " " + emp.Name + " " + emp["FavoriteColor_c"].ToString();
}

How about when you need to query across multiple tables? That is a situation where I put my preference for Lambda aside as query syntax is clearly the best choice. Here is an example:

var rows = (from d in Db.OrderDtl
                join p in Db.Part on 
                  new {d.Company, d.PartNum} equals 
                  new {p.Company, p.PartNum}
                join r in Db.OrderRel on 
                  new {d.Company, d.OrderNum, d.OrderLine} equals 
                  new {r.Company, r.OrderNum, r.OrderLine}
                join pp in Db.PartPlant on 
                  new {d.Company, d.PartNum} equals 
                  new {pp.Company, pp.PartNum}
                where 
                  d.Company == "EPIC06" 
                  && d.OrderNum == 12345
                  && pp.Plant == "MfgSys"
                select new { r.Make, p.PartNum, p.ClassID, pp.PersonID } );

Here I am starting with OrderDtl aliased as d. Then I join to Part (aliased as p) like so:

join p in Db.Part on 
new {d.Company, d.PartNum} equals 
new {p.Company, p.PartNum}

So the second line is all of the columns to join on from OrderDtl (d) and the third line is all of the columns to join to in Part (p). This would be the equivalent of the following in SQL:

FROM
  OrderDtl d
  
  LEFT OUTER JOIN Part p ON
  p.Company = d.Company
  AND p.PartNum = d.PartNum

We continue down the line pulling in all of the other tables we wish to join in, apply a where clause, and then finish up by picking our columns on the select.

Conclusion

All of this is really just the tip of the iceberg but hopefully, it gets you pointed in the right direction. I hope it helps!