Geek asks and answers

Combining Dynamic Data Tables Using Linq

I have two data tables whose columns are dynamic, and both of them have one common column. Now I want to join these two tables. To get a combined result.

Thanks in advance.

+3
source share
3 answers

Hi, you might want to join the two data tables that are generated at runtime. Check out this link. Perhaps you will be useful

Finding common columns from two data and using conditions for a join condition in LINQ

+3
source

A simple way is to use AsEnumerable () in tables and combine them into common column data.

, : [Table1] -> [ID] [Name] [Location] | [Table2] -> [ID] [Description],


DataTable table1 = new DataTable();
table1.Columns.Add("ID", typeof(int));
table1.Columns.Add("Name", typeof(string));
table1.Columns.Add("Location", typeof(string));
table1.Rows.Add(1, "Name1", "Location1");
table1.Rows.Add(2, "Name2", "Location2");
table1.Rows.Add(3, "Name3", "Location3");

DataTable table2 = new DataTable();
table2.Columns.Add("ID", typeof(int));
table2.Columns.Add("Description", typeof(string));
table2.Rows.Add(1, "Description1");
table2.Rows.Add(2, "Description2");
table2.Rows.Add(3, "Description3");

ID


var joinedTables = from data1 in table1.AsEnumerable()
                   join data2 in table2.AsEnumerable() on data1.Field("ID") equals data2.Field("ID")
                   select new {  id= data1.Field("ID"), 
                     name = data1.Field("Name"), 
                     loc = data1.Field("Location"),
                     desc = data2.Field("Description")
                   };

:

id name  loc       desc
1  Name1 Location1 Description1
2  Name2 Location2 Description2
3  Name3 Location3 Description3
0

, ExpandoObject, LINQ , . , , - , ,

public ExpandoObject CombineMe(DataRow r1, DataRow r2) 
{
    dynamic x = new ExpandoObject();
    x.ID = r1.Field<int>("ID");
    x.Name = r1.Field<string>("Name");

    // use Expando as dictionary
    IDictionary<String, Object> xd = (IDictionary<String, Object>)x;

    // enumerat both rows for all columns not ID and Name and add to Expando
    foreach (DataColumn c in r1.Table.Columns) 
        if (c.ColumnName != "ID" && c.ColumnName != "Name") 
            xd.Add(c.ColumnName, r1[c]);
    foreach (DataColumn c in r2.Table.Columns)
        if (c.ColumnName != "ID" && c.ColumnName != "Name")
            xd.Add(c.ColumnName, r2[c]);
    return x;
}

/// .... further down

var p = from a in table1.AsEnumerable()
        join b in table2.AsEnumerable() on a.Field<int>("ID") equals b.Field<int>("ID")
        select CombineMe(a, b);

select linq SQL select, , , .

NOTE. If you do not want to use dynamic, then a solution can be made in a similar way by listing all the columns in both tables and creating a third table. CombineMe is then modified to generate DataRowinstead ExpandoObject, and p is finally enumerated and all of its entries (combined instances DataRow) are added to the resulting table.

0
source

More articles:


All Articles