加入DataTables通过LINQ获取新的DataTable[英] Join DataTables to get new DataTable via LINQ

本文是小编为大家收集整理的关于加入DataTables通过LINQ获取新的DataTable的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一个命名的数据词,其中包含带有ID列的数据,firstName,lastName.另一个数据表包含代码列,userID1,userID2,userID3,工作.

现在,我想要一个新的数据表,该数据应包含两个数据表的列,并具有适当的数据.

新数据词应包含数据为:ID,userfullname1,userfullname2,userfullname3,工作.

在这里,我们获得了userfullname1的值,datatable2的datatable1&userID1的姓氏.同样,我们获得了userfullname2的值, datatable1 &userID2 datatable2 datatable2 等等.

Datatable1 中ID的值与userID1,userID2,userID3相同

最后,我想获得一个带有代码,userfullname1,userfullname2,userfullname3,work的新数据.但是用户ID在DataTable1中.因此,我想通过其ID将DataTable1的名称绑定到DataTable2的所有3个用户ID,这两个表中都存在.

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

新数据表:

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

如何加入并获取新的数据表?

推荐答案

听起来您有一个带有用户ID的表,您想加入个人资料表以获取这些名称.您可以使用:

之类的子征服来完成此操作.
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...
});

您可以使用:

进行连接
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...
               });

其他推荐答案

您可以使用Linq实现所需的目标:

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"));
}

其他推荐答案

如果您只希望结果表具有名称和ID,则应该这样做:

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();

本文地址:https://www.itbaoku.cn/post/1557087.html

问题描述

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 ?

推荐答案

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...
               });

其他推荐答案

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"));
}

其他推荐答案

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();