Update DataTable using LINQ

Question

I have two datatables with same identity column. I want to update one column value in one table with other column value in another table. For example:

These are the two tables:

Table1:

       ID         Name           Amount
     ------      -------        ---------
        1          AA             0
        2          BB             0
        3          CC             0

Table2:

       ID        Amount
     ------    ---------
        1        5000
        2        6000
        3        7000

My desired datatable should like this:

DesiredTable:

       ID         Name           Amount
     ------      -------        ---------
        1          AA             5000
        2          BB             6000
        3          CC             7000

I don't want to use for loop. How to achieve this result using LINQ?

Again this arbitrary comment about not wanting to use a for loop. It does beg the question: why not? =) — J. Steen, Nov 28 '12 at 12:19
And do you expect us to write it for you without showing what you have tried. — L.B, Nov 28 '12 at 12:20
Also LINQ is usually used to create new collections/views, not produce side-effects in existing ones. For or foreach seems reasonable enough to be used. — Honza Brestan, Nov 28 '12 at 12:22
I have written the coding using for loop. thats why I mentioned not to use for loop. I want to know how to do using LINQ? — thevan, Nov 28 '12 at 12:24
This may be obvious to *you*, but not to *anyone else*. Please include information like that (and the relevant example) in your question. =) — J. Steen, Nov 28 '12 at 12:25

score 3 · Accepted Answer · answered Nov 30 '12 at 05:10

3

Here Consider Table1 as 'DTTable1' and Table2 as DTTable2:

DTTable1.AsEnumerable().Join(DTTable2.AsEnumerable(), 
                     dt1_Row => dt1_Row.ItemArray[0],
                     dt2_Row => dt2_Row.ItemArray[0],
                     (dt1_Row, dt2_Row) => new { dt1_Row, dt2_Row }).ToList() 
                     .ForEach(o => o.dt1_Row.SetField(2, o.dt2_Row.ItemArray[1]));

answered Nov 30 '12 at 05:10

thevan

10,052
53
137
202

+1, but you make an excellent case for no longer using DataTable. That's _so_ ugly. – John Saunders Nov 30 '12 at 05:19
@John: thank you. I will keep and follow your suggestion in future. – thevan Dec 01 '12 at 05:23

score 0 · Answer 2 · edited May 23 '17 at 12:09

I am just posting this here for future reference....

If you want to update dtblToUpdate with values from dtblToUpdateFrom but you need to join on multiple columns then this seems to work:

dtblToUpdate.Rows.Cast<DataRow>().Join(dtblToUpdateFrom.Rows.Cast<DataRow>(),
            r1 => new { p1 = r1["ColToJoinOn1"], p2 = r1["ColToJoinOn2"] },
            r2 => new { p1 = r2["ColToJoinOn1"], p2 = r2["ColToJoinOn2"] },
            (r1, r2) => new { r1, r2 }).ToList()
            .ForEach(o => o.r1.SetField("ColToUpdate", o.r2["ColToUpdateFrom"]));

If there is anything wrong with this please let me know.

Update:

Here is some test code that shows that it works:

        DataTable dtblToUpdate = new DataTable();
        dtblToUpdate.Columns.AddRange(new DataColumn[]{new DataColumn("ColToJoinOn1"), new DataColumn("ColToJoinOn2"), new DataColumn("ColToUpdate")});
        dtblToUpdate.Rows.Add("1", "1", "nothing");
        dtblToUpdate.Rows.Add("2", "1", "nothing");
        dtblToUpdate.Rows.Add("3", "1", "nothing");

        DataTable dtblToUpdateFrom = new DataTable();
        dtblToUpdateFrom.Columns.AddRange(new DataColumn[]{new DataColumn("ColToJoinOn1"), new DataColumn("ColToJoinOn2"), new DataColumn("ColToUpdateFrom")});
        dtblToUpdateFrom.Rows.Add("1", "1", "something");
        dtblToUpdateFrom.Rows.Add("2", "1", "something");
        dtblToUpdateFrom.Rows.Add("3", "2", "something"); //Won't be updated since ColToJoinOn2 does not match in dtlbToUpdate

        Console.WriteLine("dtblToUpdate before update:");
        foreach(DataRow drow in dtblToUpdate.Rows)
        {
            Console.WriteLine(drow["ColToJoinOn1"].ToString() + "." + drow["ColToJoinOn2"].ToString() + " - " + drow["ColToUpdate"].ToString());
        }

        dtblToUpdate.Rows.Cast<DataRow>().Join(dtblToUpdateFrom.Rows.Cast<DataRow>(),
        r1 => new { p1 = r1["ColToJoinOn1"], p2 = r1["ColToJoinOn2"] },
        r2 => new { p1 = r2["ColToJoinOn1"], p2 = r2["ColToJoinOn2"] },
        (r1, r2) => new { r1, r2 }).ToList()
        .ForEach(o => o.r1.SetField("ColToUpdate", o.r2["ColToUpdateFrom"]));

        Console.WriteLine("dtblToUpdate after update:");
        foreach(DataRow drow in dtblToUpdate.Rows)
        {
            Console.WriteLine(drow["ColToJoinOn1"].ToString() + "." + drow["ColToJoinOn2"].ToString() + " - " + drow["ColToUpdate"].ToString());
        }

Output:

dtblToUpdate before update:
1.1 - nothing
2.1 - nothing
3.1 - nothing
dtblToUpdate after update:
1.1 - something
2.1 - something
3.1 - nothing

Also, see this post for another option for when one table is missing the Amount column. You can join the tables on one column and get a new DataTable with all unique columns between the two tables: How to Left Outer Join two DataTables in c#?

@thevan I know this is old but I have updated my answer with some test code to show that it does update the column. — Soenhay, Mar 20 '17 at 18:56

score 0 · Answer 3 · answered Feb 02 '14 at 08:08

I didn't like any of the examples I saw on the web, so here's my example

        DataTable dt = new DataTable();
        dt.Columns.Add("Year");
        dt.Columns.Add("Month");
        dt.Columns.Add("Views");
        for (int year = 2011; year < 2015; year++)
        {
            for (int month = 1; month < 13; month++)
            {
                DataRow newRow = dt.NewRow();
                newRow[0] = year;
                newRow[1] = month;
                newRow[2] = 0;
                dt.Rows.Add(newRow);
            }
        }

        dataGridView1.DataSource = dt;

        //if using Lambda 
        //var test = dt.AsEnumerable().Where(x => x.Field<string>("Year") == "2013" && x.Field<string>("Month") == "2").ToList();

        var test = (from x in dt.AsEnumerable()
                    where x.Field<string>("Year") == "2013"
                    where x.Field<string>("Month") == "2"
                    select x).ToList();

         var records = from p in dt.AsEnumerable()
                            where p.Field<string>("Year") == "2013" && p.Field<string>("Month") == "2"
                            select new
                            {
                                Views = p.Field<string>("Views")//,
                                //Month = p.Field<string>("Month")
                            };
                        foreach (var record in records)
                        {
                            //Console.WriteLine(string.Format("{0}. {1}  ({2})", record.Views, record.Month, record.Views));
                            thisCount = Convert.ToInt32(record.Views);
                            thisCount++;
                        }
        test[0][2] = thisCount.ToString();

        dt.AcceptChanges();

        //if writing to sql use  dt.SubmitChanges() instead

score -1 · Answer 4 · answered Nov 28 '12 at 12:26

-1

You can use join statement:

from item1 in table1
join item2 in table2 on item1.ID equals item2.ID
select new YourType { ID = item1.ID, Name = item1.Name, Amount = item2.Amount }

answered Nov 28 '12 at 12:26

Honza Brestan

10,637
2
32
43

Update DataTable using LINQ

4 Answers4