C#, Datatable, Join, LINQ, 子查詢

void Main()
{
	DataTable dt1 = new DataTable();
	dt1.Columns.Add("A", typeof(string));
	dt1.Columns.Add("B", typeof(string));
	dt1.Columns.Add("C", typeof(string));
	dt1.Columns.Add("D", typeof(int));

	// 資料填充
	DataRow dr1 = dt1.NewRow();
	dr1["A"] = "A1";
	dr1["B"] = "B1";
	dr1["C"] = "C1";
	dr1["D"] = "1";
	dt1.Rows.Add(dr1);

	dr1 = dt1.NewRow();
	dr1["A"] = "A2";
	dr1["B"] = "B2";
	dr1["C"] = "C2";
	dr1["D"] = "2";
	dt1.Rows.Add(dr1);

	DataTable dt2 = new DataTable();
	dt2.Columns.Add("E", typeof(string));
	dt2.Columns.Add("F", typeof(string));
	dt2.Columns.Add("G", typeof(int));

	// 資料填充
	DataRow dr2 = dt2.NewRow();
	dr2["E"] = "A1";
	dr2["F"] = "B1";
	dr2["G"] = "10";
	dt2.Rows.Add(dr2);

	dr2 = dt2.NewRow();
	dr2["E"] = "A2";
	dr2["F"] = "B2";
	dr2["G"] = "20";
	dt2.Rows.Add(dr2);


	// 使用 LINQ 合併 dt1 和 dt2
	var query = from row1 in dt1.AsEnumerable()
				select dt1.Clone().LoadDataRow(new object[]
				{
				row1["A"],
				row1["B"],
				row1["C"],
				
				//D (子查詢, 由dt2取得資料)
                    //->因為dt1["D"]是int, 若使用select得到的型態為System.Int32, 不匹配, 改用sum取得資料型態為int (需視資料結構是否符合)
				(dt2.AsEnumerable().Where(r => r.Field<string>("E") == row1.Field<string>("A") &&
				                               r.Field<string>("F") == row1.Field<string>("B"))
								   //.Select(s => s.Field<int>("G"))),  //==> 無法將類型 'System.Data.EnumerableRowCollection`1[System.Int32]' 的物件轉換為類型 'System.IConvertible' 
								   .Sum(s => s.Field<int>("G"))),
				}, false);

	DataTable dt3 = dt1.Clone();
	if (query.Count() > 0)
	{
		dt3 = query.CopyToDataTable();
	}
	
	dt3.Dump();
}