Linq query for not equal and equal condition

Help Studio
, , , , , ,
1

I have two datables which should be compared based on ID .On match dept and place columns added to result datatable in addition to dt1 columbs.On no match I would need “NA” in dept and place column.

I have two queries for each condition but how can we merge both?
(From d1 In Dt1.AsEnumerable()
Group Join d2 In Dt2.AsEnumerable()
On d2(“id”).ToString Equals d1(“id”).tostring Into gj = Group
From g In gj.DefaultIfEmpty
Where IsNothing(g)
Select a={d1(“ID”).tostring,d1(“Name”).tostring,d1(“year”).tostring,“TBH”,“TBH”}
Select out_FinalDt.rows.add(a)).CopyToDataTable()

(From d1 In Dt1.AsEnumerable()
Group Join d2 In Dt2.AsEnumerable()
On d2(“id”).ToString Equals d1(“id”).tostring
Select out_FinalDt.rows.add({d1(“ID”).tostring,d1(“Name”).tostring,d1(“year”).tostring,d2(“dept”).tostring,d2(“place”).tostring})).copytodatatable

Input:
DT 1
id name year
2 XX 2000
3 YY 2000
4 ZZ 2001

DT2
ID name year dept place transport marks
2 xx 2000 cse tn Self 90%
3 yy 2000 ECE kr Bus 95%

output:
id name year dept place
2 XX 2000 cse tn
3 YY 2000 ece Kr
4 ZZ 2001 NA NA

2

Hey!

Have a view on this video

Regards,
NaNi

3

Assign:
dtResult = dt2.Clone

Assign:
dtResult =

(From d1 In Dt1.AsEnumerable()
Group Join d2 In Dt2.AsEnumerable()
On d1("id").ToString Equals d2("id").tostring Into gj = Group
From g In gj.DefaultIfEmpty
Let ra1 = d1.ItemArray
Let ra2 = If(isNothing(g), new Object(){"TBH","TBH"}, {g(3), g(4)})
Let ra = ra1.Concat(ra2).toArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable
4

Thanks for the response .Incase if my tables had additional columns and I need to select certain columns from both tables for
“ra1”.How can we do that? instead of entire table as d1.item array?

5

just share the column structure with us and we will have a look at it

6

Thanks for the response .I have edited the dt2 in the post.I would need below fields only from respective tables
dt1:ID,name, year
dt2:name, year, dept, place,

7

Currently the requirement is not clear and has some doubts

give a try at following

Build datatable activity - configure an empty datatable with the needed datacolumns - dtResult

Assign Activity
arrCol1 | String array = {“id”, “name”, “year”}

Assign Activity
arrCol2 | String array = {“dept”, “place”}

ensure that arrCol1 + arrCol2 are similar as the configured col structure from dtResult

dtResult =

(From d1 In Dt1.AsEnumerable()
Group Join d2 In Dt2.AsEnumerable()
On d1("id").ToString Equals d2("id").tostring Into gj = Group
From g In gj.DefaultIfEmpty
Let ra1 = arrCol1.Select(Function (e1) d1(e1)).toArray
Let ra2 = If(isNothing(g), New Object(){"TBH","TBH"}, arrCol2.Select(Function (e2) g(e2)).toArray)
Let ra = ra1.Concat(ra2).toArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable