Hi all,
I’m trying to join 2 dataTables and then filter that joined dataTable using Linq query.
So, for that I’ve used following query:
(From DT1 In dt1.AsEnumerable() Join DT2 In dt2.AsEnumerable() On DT1(“ID”).ToString() Equals DT2(“SID”).ToString() Select DT2).ToArray().CopyToDatatable
By this I’m able to get Data from one of the 2 DataTables, with ID’s —> 1, 2 and 3. Only which is right.
But, now I want Data from both DataTables (As one joined DataTable with Columns —> ID | Name | City
So, to get the desire output I’ve used
(From DT1 In dt1.AsEnumerable() Join DT2 In dt2.AsEnumerable() On DT1(“ID”).ToString() Equals DT2(“ID”).ToString() Select New With
{
.ID = DT1.Field(Of String)(“ID”),
.Name = DT1.Field(Of String)(“Name”),
.City = DT1.Field(Of String)(“City”)
}).ToList()
It’s doesn’t allow me to use .CopyToDataTable at the end of this query, to get it in to datatable.
So, am I missing something? OR I need to try anything else?
I can’t find any correct syntax with regards with joining of two tables then selecting Columns from two datatables via LINQ in the community. Why not use the Join Data Table Activity. It returns all column from two datatable.
Hi,
you can use join datatable and filter datatable activity to join the tables and filter them, because the linq is little slow compared to normal activities.
hey, @Emman_Pelayo@pavanh003
yeah ya, I know it’ll be too easy with Join & Filter Data table activity, and I’d done this (in the same way) earlier.
but, now I’m trying to do the same with LinQ to learn more about it, as we know capabilities of Linq in complex Filtering, Sorting, Grouping.
Try to add the column “City” in DT1 before the query
(From x In DT1 Join y In DT2 On x("ID").tostring Equals y("SID").tostring
Select DT1.clone.LoadDataRow(New Object(){x("ID").tostring,x("Name").tostring,y("City").tostring},False)).CopyToDataTable
hey thanks,
this works for me but as you said, I have to take AddDataColumn activity just before the Query to add City column in dt1,
and I know its because of clone()… As It returns a new dataTable with same schema as the current DataTable.
So is there any way to Add column Or reinitialize schema of the DataTable and add a new column in it within the query iteself?? @ranjith
Take a new assign variable DT3 and add the linq on the right side:
(From a In Dt1.AsEnumerable()
Join b In Dt2.AsEnumerable()
On a(“ID”).ToString Equals b(“SID”).ToString
Select Dt3.LoadDataRow (New Object() {
a.Field(Of String)(“ID”),
a.Field(Of String)(“Name”),
b.Field(Of String)(“City”)
},False)).CopyToDataTable
First of all, I would say that you have a typo in the second part - you want to query ON DT2(“ID”), but I guess you intended to write DT2(“SID”).
Nevertheless, it won’t work.
So here is my suggestion (I am sure there are more options available):
You will have to create a new result table. Let’s call it DT3. You can do that for eg. by using a ‘Build Data Table’ activity and create all the expected columns beforehand.
Then use:
(From DT1 In dt1.AsEnumerable() Join DT2 In dt2.AsEnumerable() On DT1(“ID”).ToString() Equals
DT2(“SID”).ToString() Select DT3.LoadDataRow(
new Object() {
dt1.Field(Of String)(“ID”),
dt1.Field(Of String)(“Name”),
dt1.Field(Of String)(“City”)
}, False)
).CopyToDataTable
Hey, they are just 2 variables, used to hold value of the two data tables object.
We are declaring a to have all the objects of the data table Dt1.
For more details on datatableExtensions.As.Enumerable() see below docs: