Multiple on clause from DataTable to DataTable via LINQ
I have a question about LINQ There are two DataTables I wanna to inner join, the code as follows:
DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
dt1.Columns.Add("TEST1");
dt1.Columns.Add("TEST2");
dt1.Columns.Add("TEST3");
dt1.Columns.Add("TEST4");
dt2.Columns.Add("TEST2");
dt2.Columns.Add("TEST5");
dt2.Columns.Add("TEST6");
dt2.Columns.Add("TEST7");
for (int i = 0; i < 10; i++)
{
DataRow row = dt1.NewRow();
row["TEST1"] = "aaa";
row["TEST2"] = string.Format("bbb-{0}{1}{2}", i, (i % 2 == 0 ? "-" : ""), (i % 2 == 0 ? "ddd" : ""));
row["TEST3"] = i.ToString();
row["TEST4"] = "ddd";
dt1.Rows.Add(row);
}
for (int i = 0; i < 8; i++)
{
DataRow row = dt2.NewRow();
row["TEST2"] = "bbb";
row["TEST5"] = i.ToString();
row["TEST6"] = i % 2 == 0 ? "ddd" : "";
row["TEST7"] = "sss";
dt2.Rows.Add(row);
}
The description of dt1 is:
TEST1 TEST2 TEST3 TEST4
---------------------------
aaa bbb-0-ddd 0 ddd
aaa bbb-1 1 ddd
aaa bbb-2-ddd 2 ddd
aaa bbb-3 3 ddd
aaa bbb-4-ddd 4 ddd
aaa bbb-5 5 ddd
aaa bbb-6-ddd 6 ddd
aaa bbb-7 7 ddd
aaa bbb-8-ddd 8 ddd
aaa bbb-9 9 ddd
And the description of dt2 is:
TEST1 TEST5 TEST6 TEST7
----------------------------
bbb 0 ddd sss
bbb 1 sss
bbb 2 ddd sss
bbb 3 sss
bbb 4 ddd sss
bbb 5 sss
bbb 6 ddd sss
bbb 7 sss
The first question is, I wanna to join dt1 and dt2 as
TEST2 TEST3 TEST7
---------------------------
bbb-0-ddd 0 sss
bbb-1 1 sss
bbb-2-ddd 2 sss
bbb-3 3 sss
bbb-4-ddd 4 sss
bbb-5 5 sss
bbb-6-ddd 6 sss
bbb-7 7 sss
so I write the code as status below, I wanna to join dt1 and dt2 to dt
DataTable dt = new DataTable();
dt.Columns.Add("TEST2");
dt.Columns.Add("TEST3");
dt.Columns.Add("TEST7");
var result = from A1 in dt1.AsEnumerable()
join
A2 in dt2.AsEnumerable()
on
A1.Field<string>("TEST2") equals (A2.Field<string>("TEST2") + "-" + A2.Field<string>("TEST5") + (A2.Field<string>("TEST6") == string.Empty ? "-" : string.Empty) + A2.Field<string>("TEST6"))
select new dt.LoadDataRow(new object[] { A1.Field<string>("TEST2"), A1.Field<string>("TEST3"), A2.Field<string>("TEST7") }, false);
result.CopyToDataTable();
but it doesn't work~ furthermore, I wanna to change the column of TEST3 and TEST7,TEST3 will be named X1 and TEST3 will be named X5, so the code of the latest line should be
select new dt.LoadDataRow(new object[] { A1.Field("TEST2"), X1=A1.Field("TEST3"), X5=A2.Field("TEST7") }, false);
that's another problem....what should I do????
Instead of the LoadDataRow + CopyToDataTable approach i would always prefer a simple foreach where i add those rows in the query. Thats much more clear and doesn't rely on side-effects.
You can join on the string and put both rows in an anonymous type:
var query = from r1 in dt1.AsEnumerable()
join r2 in dt2.AsEnumerable()
on r1.Field<string>("TEST2")
equals string.Format("{0}-{1}-{2}"
, r2.Field<string>("TEST2")
, r2.Field<string>("TEST5")
, r2.Field<string>("TEST6"))
select new { r1, r2 };
foreach (var bothRows in query)
{
DataRow addedRow = dt.Rows.Add();
addedRow.SetField("TEST2", bothRows.r1.Field<string>("TEST2"));
addedRow.SetField("TEST3", bothRows.r2.Field<string>("TEST3"));
addedRow.SetField("TEST7", bothRows.r2.Field<string>("TEST7"));
}
On the internet, you can find a very useful feature called LinqToDataTable (which attached below), with it you can convert any query LINQ to DataTable, keeping the types of data resulting from the query.
public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
{
DataTable dtReturn = new DataTable();
PropertyInfo[] oProps = null;
if (varlist == null) return dtReturn;
foreach (T rec in varlist)
{
// Use reflection to get property names, to create table, Only first time, others will follow
if (oProps == null)
{
oProps = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}
}
DataRow dr = dtReturn.NewRow();
foreach (PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
(rec, null);
}
dtReturn.Rows.Add(dr);
}
return dtReturn;
}
Should call the function easily:
var result = (from A1 in dt1.AsEnumerable()
join
A2 in dt2.AsEnumerable()
on
A1.Field<string>("TEST2") equals (A2.Field<string>("TEST2") + "-" + A2.Field<string>("TEST5") + (A2.Field<string>("TEST6") == string.Empty ? "-" : string.Empty) + A2.Field<string>("TEST6"))
select new {
TEST2 = A1.Field<string>("TEST2"),
x1 = A1.Field<string>("TEST3"),
x2 = A2.Field<string>("TEST7")
});
DataTable dtFinal = LINQToDataTable(result);
上一篇: Linq将（或）将两个数据表合并为一个