Join DataTables to get new DataTable via LINQ

Question

I've a datatable named which contains data with a column of ID, firstname, lastname. Another datatable contains columns of code, userID1, userID2, userID3, work.

Now i want a new datatable which should contain the column of both the datatable with proper data.

New datatable should contain data as: ID, userfullname1, userfullname2, userfullname3, work.

Here we get the value of userfullname1 by firstname, lastname of datatable1 & userID1 of datatable2. Similarly we get the value of userfullname2 by firstname, lastname of datatable1 & userID2 of datatable2 & so on.

The value of ID in Datatable1 is same as userID1, userID2, userID3 in Datatable2.

Finally, i want to obtain a new datatable with code, userfullname1, userfullname2, userfullname3, work. But users IDs are in datatable1. So, i want to bind the names of Datatable1 to the all 3 userids of Datatable2 via their IDs whichare present in both the tables.

Datatable1 :

iD  name
1    b
2    d
3    f
4    s

....

Datatable2 :

   Code        userid1    userid2      userid3  work
    1f           1           3           6       gg
    2g           1           4           7       gg
    3b           3           4           7       gg
    4v           4           3           8       gg

New Datatable :

Code    username1   username2   username3  work
1f           a           b           c       gg
2g           d           f           r       gg
3b           c           h           g       gg
4v           d           s           h       gg

How can i join & get the new datatable ?

Answer 1

If you just want your resulting table to have name and id, you should do it like this:

DataTable dtResult = new DataTable();
dtResult.Columns.Add("ID", typeof(string));
dtResult.Columns.Add("name", typeof(string));


var result = from datatable1 in table1.AsEnumerable()
             join datatable2 in table2.AsEnumerable()
             on datatable1.Field<string>("ID") equals datatable2.Field<string>("userID")

             select dtResult.LoadDataRow(new object[]
             {
                datatable1.Field<string>("ID"),               
        string.Format("{0} {1}" ,datatable1.Field<string>("fname") ,datatable1.Field<string>("lname")),

              }, false);
result.CopyToDataTable();

Answer 2

It sounds like you have a table with User Ids and you want to join your profile table to get those names. You can do this with sub-queries like:

var myTable = UserIds.Select(u => new
{
    User1FullName = u.UserProfiles.FirstOrDefault(p => p.UserId == u.userId1).Select(p => p.FirstName + " " + p.LastName),
    User2FullName = u.UserProfiles.FirstOrDefault(p => p.UserId == u.userId2).Select(p => p.FirstName + " " + p.LastName)
    // etc...
});

You can do it with joins like:

var myTable = (from u in UserIds
               join p1 in UserProfiles on u.UserId1 equals p1.UserId
               join p2 in UserProfiles on u.UserId2 equals p2.UserId
               // etc...
               select new
               {
                   User1FullName = p1.FirstName + " " + p1.LastName,
                   User2FullName = p2.FirstName + " " + p2.LastName,
                   // etc...
               });

Answer 3

You can use LINQ to achieve what you want:

DataTable tblResult = new DataTable();
tblResult.Columns.Add("ID"); 
tblResult.Columns.Add("userfullname1"); 
tblResult.Columns.Add("userfullname2"); 
tblResult.Columns.Add("userfullname3"); 
tblResult.Columns.Add("Work");

var query = from r1 in datatable1.AsEnumerable()
            from r2 in datatable2.AsEnumerable()
            let id = r1.Field<int>("ID")
            let userID1 = r2.Field<int>("userID1")
            let userID2 = r2.Field<int>("userID2")
            let userID3 = r2.Field<int>("userID3")
            where id == userID1 && id == userID2 && id == userID3
            select new { r1, r2, id, userID1, userID2, userID3 };
foreach (var x in query)
{
    DataRow row = tblResult.Rows.Add();
    string firstName = x.r1.Field<string>("firstname");
    string lastName = x.r1.Field<string>("lastname");
    string userfullname1 = string.Format("{0} {1} {2}", firstName, lastName, x.userID1);
    string userfullname2 = string.Format("{0} {1} {2}", firstName, lastName, x.userID2);
    string userfullname3 = string.Format("{0} {1} {2}", firstName, lastName, x.userID3);
    row.SetField("ID", x.id);
    row.SetField("userfullname1", userfullname1);
    row.SetField("userfullname2", userfullname2);
    row.SetField("userfullname3", userfullname3);
    row.SetField("Work", x.r2.Field<string>("Work"));
}