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:
- 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).
- 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).
- Lines 21-22: Add the .AsEnumerable() method on the DataTables - otherwise they don't play nicely with LINQ.
- 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.
- 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.
- Line 30: In case there is no match for one of the joined rows, avoid a null exception by writing an empty string instead.
- 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 JoinStackOverflow - Joining two datatablesMicrosoft - Create DataTable from LINQ Query
Charlie Kovacs
