Hi,
I am trying to update a column in a datatable with another datatable column using linq query
Case :
dt1 - there is an empty column - Name
dt2 - column named - Names
Common column for both the tables is Emp ID column
need to update Name column in dt1
Help me with the linq query
Sudarsan, this is not going to solve my requirement.
If there is anything that can be done using linq query that would be great
Sample input:
dt1 has 10 rows with below columns
Emp ID, Name, Area - out of these Column ‘Name’ is empty
dt2 has 5 rows with below columns
Emp ID, Name, Location
I have to perform left join and need output in the same dt1
dt1 should have same 10 rows with all the columns
Emp ID, Name, Area but only for the matched empIDs in dt2 , Name column in dt1 should be filled
i did check this video, since he has done with the same number of rows in both the data tables, it is not the exact solution to my problem. And also the common column values are all matching with both the tables and hence he got all the rows.
But in my case i have less number of rows in dt2 and when i try to execute the query i am only getting the matching rows …rest of my rows in dt1 are not coming
Try to join dt2 to dt1 and check once @Sasikanth
Hi @Sasikanth,
Use below code
dt1=dt1.AsEnumerable().ToList().ForEach(Sub(row) row("Name")= If(dt2.AsEnumerable().Where(Function(r) r("Emp ID").ToString.Equals(row("Emp ID").ToString)).Count<>0, dt2.AsEnumerable().Where(Function(r) r("Emp ID").ToString.Equals(row("Emp ID").ToString))(0)("Name").ToString,row("Name").ToString))
Regards,
Arivu
When i try to use the code which you gave @arivu96 getting the below
Error ERROR No compiled code to run
error BC36639: ‘ByRef’ parameter ‘dt2’ cannot be used in a lambda expression. At line 3
error BC36639: ‘ByRef’ parameter ‘dt2’ cannot be used in a lambda expression. At line 4 _Test.xaml
have an introduction on several options for updating a column here:
How to Update Data Column Values of a Data Table | Community Blog
so datatable reconstruction along with left join / LookUp Left:dt1 - Right:dt2) would be one option to solve it
this is nice @ppr . But i am not getting how to implement the logic for my problem
If you can help me with the query or any solution it would be helpful
@ppr - i did see your post below
and tried to implement the same but this query also gives me only matched records
just share sample data along with expected output with us. Thanks
@ppr . PFB
dt1
ID Area Name
1 Bangalore
2 Hyd
3 Chennai
4 Pune
5 Gurgaon
6 Kolkata
dt2
ID Name
1 Rajesh
2 Suresh
3 Rocky
5 Gopi
Output Dt - Expected Output
ID Name Area
1 Rajesh Bangalore
2 Suresh Hyd
3 Rocky Chennai
4 Pune
5 Gopi Gurgaon
6 Kolkata
let me know if this is fine to solve the problem. Name is the empty column in dt1 that needs to filled with dt2 column values based on the matching ID
We recommend also to study
One of many options:
Assign Activity:
dtResult = dt1.Clone | Assumption: Colstructure: ID, Area, Name
Assign Activity:
dtResult =
(From d1 In dtData1.AsEnumerable
Group Join d2 In dtData2.AsEnumerable
On d1("ID").toString.Trim Equals d2("ID").toString.Trim Into gj = Group
From g In gj.DefaultIfEmpty
Select ra = {d1("ID"), d1("Area") ,If(isNothing(g), Nothing, g("Name")) }
Select dtResult.Rows.Add(ra)).CopyToDataTable
dtResult is then the reconstructed DataTable as described as approach in the shared Blog
As an alternate Option you can also create a LookUp Dictionary and use it for the name retrieval.
Hi @ppr ,
I even tried the query you gave, but still its giving me only matched rows in dt1 instead of the whole dt1. Sorry i am bit slow learner if this is bothering you…but i understood the query and implemented the same…still its not giving me the desired output.
Is there any other way we can make a modification in the query because we can actually get this using nested for each but query seems the optimized way and faster…so trying for that
an implementation
done as described above will bring this sample data
to the following:
If the column positions matter then:
OR
mkankatala
ashokkarale
pravallikapaluri
postwick
Yoichi
Anushka_Purohit
A_Learner
sullivanne
dokumentor
Yomi_Oluwadara