Code Stage - C# Linq SQL

[RafaelFernandes]

Hello everyone,

I am trying to develop an code for BluePrism using C#. For this code, I am trying to use LINQ to create a Query.

However, the code stage does not understand the command lines of the Query.

var results = FROM e in dt1
right join c in dt2 
on e.ColumnNameDT1 equals c.ColumnNameDT2 into joined 
from j in joined.DefaultIfEmpty());

The System.Linq is already imported (I am using for other codes).

How can I edit this code for BluePrism undestand the commands for SQL?

Thanks and Regards,

[CharlieKovacs]

Hi Rafael,

I assume dt1 and dt2 are both DataTables and that your goal is to get a final DataTable that contains the content of the joined dt1 and dt2 tables. If so, the query will need some adjustments before it will compile properly. I've built up your original query into something that compiles in Visual Studio and Blue Prism. You will probably need to edit it a bit further to fit with the actual data in your DataTables.

Here are the main ideas to consider in the below query:

1. Lines 1-17: I'm constructing DataTables so I have some data to experiment with. dtResult is a temporary table to hold the joined DataTable result once we're in the query - the columns need to match exactly with what the joined output will be  (i.e. Lines 15-17 mirror Lines 29-30).
2. Lines  21-22: I don't think LINQ can do a right join - everything is a left join. So flip the order of the tables (start with dt2 and left join dt1 into it to effectively make a right join).
3. Lines 21-22: Add the .AsEnumerable() method on the DataTables - otherwise they don't play nicely with LINQ.
4. Line 23: Note how the column names are referenced (...Field<[datatype]>("ColumnName")) -- this is because DataTables are being used. I use string for all the field datatypes, but your columns may be composed of numbers, booleans, or other types, so you will have to match yours to your data appropriately.
5. Lines 25-32: The LINQ query needs to end with a select clause. In this case, I'm using the dtResult DataTable to load a data row containing the joined data from the original dt1 and dt2 DataTables.
6. Line 30: In case there is no match for one of the joined rows, avoid a null exception by writing an empty string instead.
7. Line 35: Finally, use the CopyToDataTable<DataRow>() method to place your LINQ query result into a new DataTable. At this point, you can output the new DataTable into a Blue Prism Collection when you exit the code stage.

1    // create input DataTables
2    DataTable dt1 = new DataTable();
3    DataTable dt2 = new DataTable();
4    DataTable dtResult = new DataTable();
5
6    dt1.Columns.Add("ColumnNameDt1", typeof(string));
7    dt1.Columns.Add("Dt1Value", typeof(string));
8
9    dt2.Columns.Add("ColumnNameDt2", typeof(string));
10
11   dt1.Rows.Add("hello", "world");
12   dt2.Rows.Add("hello");
13
14   // create temporary output DataTable
15   dtResult.Columns.Add("ColumnNameDt1", typeof(string));
16   dtResult.Columns.Add("ColumnNameDt2", typeof(string));
17   dtResult.Columns.Add("Dt1Value", typeof(string));
18
19   // LINQ Query           
20   var results =
21       from c in dt2.AsEnumerable()
22       join e in dt1.AsEnumerable()
23       on c.Field<string>("ColumnNameDt2") equals e.Field<string>("ColumnNameDt1") into joined
24       from j in joined.DefaultIfEmpty()
25       select dtResult.LoadDataRow(
26           new object[]
27               {
28                   c.Field<string>("ColumnNameDt2"),
29                   j.Field<string>("ColumnNameDt1"),
30                   j == null ? String.Empty : j.Field<string>("Dt1Value")
31               }, 
32           false);
33
34    // Send LINQ query result to a new DataTable
35    DataTable newTable = results.CopyToDataTable<DataRow>();​

Relevant links
Microsoft - LINQ Left Outer Join
StackOverflow - Joining two datatables
Microsoft - Create DataTable from LINQ Query

[ewilson]

Hi Rafael,

Is there a specific error message you receive from Blue Prism? Have you tried running this code in Visual Studio or VS Code to validate it?

Cheers,
Eric​

[RafaelFernandes]

Hi @ewilson,

​The only errors I get are compilation: missing ; or ")". The code works fine in Visual Studio.

Thanks and Regards,

Rafael

[ewilson]

In the code snippet that's shown there's an extra ")" at the end before the semicolon. There's no associated opening "(" for it.


Cheers,
Eric