How to join 2 data tables

Question

DataTable1

LoginId LoginName SCount
1       Mohit     20
3       Riya      25

DataTable2

LoginId  LoginName ECount
2        Smita     11
3        Riya      13

I want to show result like this

LoginName  Scount  Ecount Total
Mohit      20      0      20
Smita      0       11     11
Riya       25      13     38

Query:

DataTable dtResult = new DataTable();

DataTable UserCount1 = new DataTable();
DataTable UserCount2 = new DataTable();

// Assigning value to datatable 

if (ds != null)
{
     UserCount1 = ds.Tables["UserCount1"];
     UserCount2 = ds.Tables["UserCount2"];
}

var LinqResult = 
     from dataRows1 in UserCount1.AsEnumerable()
     join dataRows2 in UserCount2.AsEnumerable()
     on dataRows1.Field<string>("LoginId") equals dataRows2.Field<string>("LoginId") into lj
     from r in lj.DefaultIfEmpty()
     select dtResult.LoadDataRow(new object[]
     {
         dataRows2.Field<string>("LoginName"),
         r == null ? 0 : r.Field<int>("SCount"),
         r == null ? 0 : r.Field<int>("ECount")
     }, false);

Getting complie time error in

select statement( dataRows2.Field<string>("LoginName"),)

that dataRows2 does not exist in current context.

How to achieve that result?

This question has an answer here https://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp — Ashokan Sivapragasam, Jan 24 '20 at 05:40
But dataRow1 is visible not giving any error.Can u pls modified query — swati, Jan 24 '20 at 05:58

Michał Turczyn · Accepted Answer · 2020-01-24T09:54:37.043

For the easy and strongly typed solution, I would strongly suggest defining classes, such as:

class User1 { public int LoginId; public string LoginName; public int SCount; }
class User2 { public int LoginId; public string LoginName; public int ECount; }

to enable LINQ extension methods, then your task becomes quite easy (explanation in comments in code):

  // Sample data.
  DataTable UserCount1 = new DataTable();
  DataTable UserCount2 = new DataTable();
  UserCount1.Columns.AddRange(new DataColumn[] { new DataColumn("LoginId"), new DataColumn("LoginName"), new DataColumn("SCount") });
  UserCount2.Columns.AddRange(new DataColumn[] { new DataColumn("LoginId"), new DataColumn("LoginName"), new DataColumn("ECount") });
  UserCount1.Rows.Add(1, "Mohit", 20);
  UserCount1.Rows.Add(3, "Riya", 25);
  UserCount2.Rows.Add(2, "Smita", 31);
  UserCount2.Rows.Add(3, "Riya", 13);
  // Here we create lists of our users.
  List<User1> users1 = new List<User1>();
  List<User2> users2 = new List<User2>();
  foreach (DataRow row in UserCount1.Rows)
    users1.Add(new User1() { LoginId = int.Parse(row["LoginId"].ToString()), LoginName = (string)row["LoginName"], SCount = int.Parse(row["SCount"].ToString()) });
  foreach (DataRow row in UserCount2.Rows)
    users2.Add(new User2() { LoginId = int.Parse(row["LoginId"].ToString()), LoginName = (string)row["LoginName"], ECount = int.Parse(row["ECount"].ToString()) });
  // Full outer join: first we join, then add entries, that were not included.
  var result = users1.Join(users2, u1 => u1.LoginId, u2 => u2.LoginId, (u1, u2) => new { LoginId = u1.LoginId, LoginName = u1.LoginName, SCount = u1.SCount, ECount = u2.ECount, Total = u1.SCount + u2.ECount }).ToList();
  result.AddRange(users1.Where(u1 => !result.Select(u => u.LoginId).Contains(u1.LoginId)).Select(u1 => new { LoginId = u1.LoginId, LoginName = u1.LoginName, SCount = u1.SCount, ECount = 0, Total = u1.SCount }));
  result.AddRange(users2.Where(u2 => !result.Select(u => u.LoginId).Contains(u2.LoginId)).Select(u2 => new { LoginId = u2.LoginId, LoginName = u2.LoginName, SCount = 0, ECount = u2.ECount, Total = u2.ECount }));

Then you can construct another result DataTable, for which I don't see any reason.

How to join 2 data tables

1 Answers1