C# LINQ join two DataTables with a common field-CodePudding

I've the following two exampled DataTables:

                                     table1:

pk	groupID	cField	field1	field2
1	1	0.5	10	$5
2	1	1	10	$10
3	1	2	10	$20
4	1	5	10	$50

                                     table2:

pk	groupID	cField	field1	field2
10	1	0.5	20	$10
11	1	1	20	$20
12	1	2	20	$40
13	1	5	20	$100

The expected result would be:

                                     dtAux:

groupID	cField	t1_field1	t1_field2	t2_field1	t2_field2
1	0.5	10	$5	20	$10
1	1	10	$10	20	$20
1	2	10	$20	20	$40
1	5	10	$50	20	$100

Based on this other post, I've tried the following code:

dtAux = new DataTable();
dtAux.Columns.Add("groupID"); 
dtAux.Columns.Add("cField"); 
dtAux.Columns.Add("t1_field1"); 
dtAux.Columns.Add("t1_field2"); 
dtAux.Columns.Add("t2_field1"); 
dtAux.Columns.Add("t2_field2");

IEnumerable<DataRow> qry = from t1 in table1.AsEnumerable() join t2 in table2.AsEnumerable()
                           on t1.Field<float>("cField") equals t2.Field<float>("cField")
                           where t1.Field<int>("groupID") == 1
                           select dtAux.LoadDataRow(new Object[] {
                                 t1.Field<int>("groupID"), t1.Field<float>("cField"),                                                                    
                                 t1.Field<int>("field1"), t1.Field<decimal>("field2"),                                                                   
                                 t2.Field<int>("field1"), t2.Field<decimal>("field2")
                           }, false);

Yet, it returns an empty table. Any hints or help is greatly appreciated.

CodePudding user response：

The qry is not executed. You need .CopyToDataTable() to execute it.

qry.CopyToDataTable();

Sample program

CodePudding user response：

You could try Zip.

var table1 = new [] {
    new { pk = 1, groupID = 1, field2 = 5 },
    new { pk = 2, groupID = 1, field2 = 10 }
};

var table2   = new [] {
    new { pk = 10, groupID = 1, field2 = 10 },
    new { pk = 11, groupID = 1, field2 = 20 }
};

var table3 = table1.Zip(
    second: table2, 
    resultSelector: (t1, t2) => new { t1_field2 = t1.field2, t2_field2 = t2.field2});

foreach(var t3 in table3 )
{
    Console.WriteLine($"{t3.t1_field2}, {t3.t2_field2}");
}

This prints:

5, 10
10, 20