Joins using Linq

Help
1

Hi all,
I’m trying to join 2 dataTables and then filter that joined dataTable using Linq query.
dt1 dt2
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?

Thanks.

2 Likes
2

Hi Samir,

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.

image

Output:
image

3

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.

Regards,
Pavan H

3 Likes
4

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.

thanks for replying guys. :slight_smile:

1 Like
5

@samir

Can you try this query below.

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
3 Likes
6

hey thanks, :blush:
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

Thanks

1 Like
7

@samir

I have also tried to add the column within the query but I can’t do it while posting that solution. Will check on that when I got time.

1 Like
8

Okay sure, @ranjith
Let me know then.

9

how to add where condition in query Pls help

10

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

11

Hi Samir.

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

Hope this helps.
Regards.

12

Hello Anshul, what are ‘a’ and ‘b’ respectively?

13

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:

14

Hi @ranjith

if columns name are not matching into two or more table for this case how to merge the datatable…

Thanks
Shyam