Understanding LINQ in EPICOR

For many newcomers to Epicor development, LINQ (Language Integrated Query) can be a daunting challenge. LINQ, a modern C# technique, simplifies querying non-database elements within the programming landscape.

Traditionally, querying non-database elements lacked the simplicity of SQL queries used for databases. Enter LINQ, offering a unified query interface while addressing SQL complexities.

LINQ streamlines querying tasks and resolves age-old SQL headaches. In applications with both database and non-database elements, managing two query languages becomes impractical. LINQ bridges this gap, offering a cohesive approach.

Within Epicor, LINQ serves as the standard query language in BPMs (Business Process Management). This article focuses on table queries in Epicor BPMs but applies to querying various entities.

Join us as we unravel how LINQ streamlines table queries within Epicor BPMs, shedding light on its universal applicability across entities.

THE SYNTAX

LINQ offers two syntax options: Query syntax, resembling SQL style, and Lambda syntax, akin to a more programmatic approach. While Query syntax mirrors SQL's structure, Lambda syntax provides a more code-centric style.

CONTENTS

Let's dive into a fundamental LINQ query: fetching a roster of active parts. Within Epicor's database, employee information resides in the erp.Part table. To determine an part's type (Manufactured or Purchased) , we inspect the TypeCode field, marked 'P' for purchased parts. Here's a glimpse of the query in action:

// Query Syntax Extraction Example 1

var dts = (from e in Db.Part

where e.Company == "YOURCOMPANYID" &&

e.TypeCode == "P" select new { e.PartNum, e.PartDescription } );

As you can observe, the structure of this LINQ query closely resembles that of a SQL query, albeit with a few slight adjustments. Now the data is extracted and saved in the variable called "dts". Since there could be numerous parts that is in "purchased" type, the outcomes may consist of multiple entries. If you only want one of them you can do the following:

// Query Syntax Extraction Example 2

var dts = (from e in Db.Part

where e.Company == "YOURCOMPANYID" &&

e.TypeCode == "P" select new { e.PartNum, e.PartDescription } ).FirstOrDefault();

If you are using a single row extraction (Example 1). Then you can use the following code to get the results.

if (dts != null)

{ // do something with dts.PartNum ; }

If you are using a single row extraction (Example 2). Then you can use the following code to get the results.

foreach (var dt in dts)

{ string blah = dt.PartNum + ' ' + dt.PartDescription; }

If you are using Lambda approach you can use the following code

// Lambda Syntax Extraction Example 1

var dts = Db.Part.Where(e => e.Company == "YOURCOMPANYID" && e.TypeCode == "P");

// Lambda Syntax Extraction Example 2

var dts = Db.Part.Where(e => e.Company == "YOURCOMPANYID" && e.TypeCode == "P").FirstOrDefault();

Sometimes the fields don't automatically in the dataset, such as UD fields, to avoid that you can use the array method

foreach(var dt in dts)

{ string blah = dt.PartNum + " " + dt.PartDescription + " " + dt["YOUR OWN UD FIELD_c"].ToString(); }

In a lot of cases you need multiple tables to join each other, like SQL inner joins. You can surely do that in LINQ. For example, you for the part table, check the example below.

var rows = (from d in Db.Part

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

new {p.Company, p.PartNum}

where d.Company == "YOURCOMPANYID" && p.Plant == "MfgSys"

select new { d.PartNum, p.Plant, p.PrimWhse } );

In the example above, we do a extraction on Part table and link it to PartPlant table, so we can get the Primary Warehouse field.

After extraction, you can do the same thing for multiple extraction example above

foreach (var row in rows)

{ string blah = row.PartNum + ' ' + row.PrimWhse; }