Compare two DataTables for differences and Add a column accordingly

Question

I have two DataTable A and B, with columns ID, Receipt and Amount in both.

I need to display the result in a GridView, but it will have one more column called 'Issued'. Now I have created another empty DataTable.

I need to compare A with B, in such a way, that if 'ID' of A is available in 'ID' of B, I need to add another column in that row, called 'Issued' as 'Yes' in A. If not available, then 'Issued' will be 'No'.

I read about comparing using Linq but I have no clue how to go about adding the new column.

As your first step, you can see [this issue](https://stackoverflow.com/questions/10984453/compare-two-datatables-for-differences-in-c) for compare 2 `DataTable`, then decide `Issued` column value afterwards. — Tetsuya Yamamoto, May 24 '18 at 07:25
Start with `yourTableA.Columns.Add("Issued");`. That solves the first part of your problem. You haven't indicated if you need specific help on the Linq (or any other matching strategy), but you might want to give it a go first and update your question with your attempt. — Anthony Pegram, May 24 '18 at 07:33
Hi, @Gaurang Dave. I tried the For Loop code which you gave. It works perfectly, but since it is a loop inside a loop, it takes a lot of time to get through the two DataTable. Is there any other way, I can achive it. For example, linq or merge? — Laba Ningombam, May 24 '18 at 09:58

score 0 · Answer 1 · answered May 24 '18 at 07:48

0

I tried to simulate the scenario you asked and developed a simple console program. I hope it will help you.

static void Main(string[] args)
{
    DataTable dtA = new DataTable();
    dtA.Columns.Add("ID");
    dtA.Columns.Add("Receipt");
    dtA.Columns.Add("Amount");
    dtA.Columns.Add("Issued");

    DataTable dtB = new DataTable();
    dtB.Columns.Add("ID");
    dtB.Columns.Add("Receipt");
    dtB.Columns.Add("Amount");

    for (int i = 1; i <= 10; i++)
        dtA.Rows.Add(i, "Receipt" + i, i * 10, "");

    for (int i = 1; i <= 5; i++)
        dtB.Rows.Add(i, "Receipt" + i, i * 10);

    for (int i = 0; i < dtA.Rows.Count; i++)
    {
        for (int j = 0; j < dtB.Rows.Count; j++)
        {
            if (Convert.ToInt32(dtA.Rows[i]["ID"]) == Convert.ToInt32(dtB.Rows[j]["ID"]))
                dtA.Rows[i]["Issued"] = "Yes";
        }
    }

    Console.ReadLine();
}

answered May 24 '18 at 07:48

Gaurang Dave

3,956
2
15
34

`code`for (int i = 0; i < A.Rows.Count; i++) { for (int j = 0; j < B.Rows.Count; j++) { if (Convert.ToInt32(A.Rows[i]["ID"]) == Convert.ToInt32(B.Rows[j]["ID"])) { A.Rows[i]["Issued"] = "Yes"; break; } else { A.Rows[i]["Issued"] = "No"; } } } `code` – Laba Ningombam May 24 '18 at 10:00
As the questioner alluded to in the comments below the question, performance on this approach will degrade rapidly with larger sets of data, as this is O(n * m), where n and m are the number of rows of data. It can be improved using a Linq join, but also with an old-fashioned looping approach that first goes over B and loads they key field into a HashSet, and *then* over A to check the key field against the contents of the HashSet. – Anthony Pegram May 24 '18 at 13:54

Anthony Pegram · Answer 2 · 2018-05-24T14:08:38.390

0

tableA.Columns.Add("Issued"); 

// the join assumes your ID field is of type int. Change type/names as appropriate
var query = from rowA in tableA.AsEnumerable()
            join rowB in tableB.AsEnumerable() 
            on rowA.Field<int>("ID") equals rowB.Field<int>("ID") into grp 
            from B in grp.DefaultIfEmpty()
            select new { A = rowA, B }; 

foreach (var pair in query)
{
    if (pair.B != null)
        pair.A["Issued"] = "Yes"; 
    else
        pair.A["Issued"] = "No";
}

This matching could also be done without Linq with old fashioned looping.

var idsInB = new HashSet<int>();
foreach (DataRow row in tableB.Rows)
    idsInB.Add((int)row["ID"]);

foreach (DataRow row in tableA.Rows)
{
    if (idsInB.Contains((int)row["ID"]))
        row["Issued"] = "Yes";
    else
        row["Issued"] = "No";
}

edited May 24 '18 at 14:08

answered May 24 '18 at 13:50

Anthony Pegram

123,721
27
225
246

Do you have something against the ternary operator? – NetMage May 24 '18 at 19:09
Perhaps a group join instead of left join? Replace `from B`... with `select new { A = rowA, InB = grp.Any() };`, then `pair.A["Issued"] = pair.InB ? "Yes" : "No";` – NetMage May 24 '18 at 19:12
@NetMage, regarding the conditional operator, the if/else is going to obvious to most any reader, the conditional would be less so, which could mean there's more to explain. But once the logic is understood, the reader is free to reduce it further. Regarding your second suggestion on the Linq join, that's doable and could certainly reduce redundancy in processing if the relationship between A and B could be viewed as 1:M. – Anthony Pegram May 24 '18 at 19:31
Under what condition to you envision a different result? – NetMage May 24 '18 at 20:05

score 0 · Answer 3 · answered May 24 '18 at 20:14

Perform left join

List<C> ListC=(from B in ListB
               join A in ListA
               on B.Id equals A.Id
               into tempList
               from temp in TempList.DefaultIfEmpty()
               select new{B,temp})
              .Select(x=>new C
                {
                  x.B.Id, 
                  x.B.Receipt,
                  x.B.Amount,
                  Issued=x.temp==null?"No":"Yes"
                 }.ToList();

So C will contain all the records of B along with your additional column Issued. You can do one more thing , you can have an additional field in class B and set it's value. You won't have to create an additional class C.

class B{
 int id{get;set;}
 int Receipt{get;set;}
 int Amount{get;set;}
 string Issued{get;set;}
}

Compare two DataTables for differences and Add a column accordingly

3 Answers3