[.NET]LINQ join multiple DataTables 將結果產生另一個 DataTable

  • 10645
  • 0
  • .NET
  • 2014-12-26

透過 LINQ 去 Join 2 個DataTable時,要回傳想要的資料。
但是要回傳DataTable物件,要如何處理呢?

假設透過 LINQ 去 Join 2 個DataTable時,要回傳想要的資料,可以使用 Anonymous type 將資料傳出去,如下,

image

DataTable users = new DataTable("users");
users.Columns.Add("UserId", typeof(int));
users.Columns.Add("UserName", typeof(string));
users.Rows.Add(1, "Rainmaker");
users.Rows.Add(2, "Terry");
DataTable userRoles = new DataTable("userRoles");
userRoles.Columns.Add("RoleId", typeof(int));
userRoles.Columns.Add("RoleName", typeof(string));
userRoles.Columns.Add("UserId", typeof(int));
userRoles.Rows.Add(1, "Admin", 1);
userRoles.Rows.Add(2, "Users", 1);
userRoles.Rows.Add(2, "Users", 2);



var userRolesInfo1 = from u in users.AsEnumerable()
					join ur in userRoles.AsEnumerable()
					on u.Field<int>("UserId") equals ur.Field<int>("UserId")
					select new {
						UserId = u.Field<int>("UserId"),
						 UserName = u.Field<string>("UserName"),
						 RoleId = ur.Field<int>("RoleId"),
						RoleName = ur.Field<string>("RoleName")
					};
foreach (var item in userRolesInfo1)
{
	Console.WriteLine("UserId:{0}, UserName:{1}, RoleId:{2}, RoleName:{3}", 
						item.UserId, item.UserName, item.RoleId, item.RoleName);
}

image

 

那如果要的結果是要產生另一個DataTable呢?

那可以從DB取得一個空的DataTable,或是程式中建立一個,然後透過 LoadDataRow Method

來將資料放到DataTable之中,如下,

//result datatable
DataTable dt = new DataTable();
dt.Columns.Add("UserId", typeof(int));
dt.Columns.Add("UserName", typeof(string));
dt.Columns.Add("RoleId", typeof(int));
dt.Columns.Add("RoleName", typeof(string));
var userRolesInfo2 = (from u in users.AsEnumerable()
					join ur in userRoles.AsEnumerable()
					on u.Field<int>("UserId") equals ur.Field<int>("UserId")
					select dt.LoadDataRow(
					new object[] {
						u.Field<int>("UserId"),
						 u.Field<string>("UserName"),
						 ur.Field<int>("RoleId"),
						ur.Field<string>("RoleName")
					}, false)).ToList();
foreach (DataRow r in dt.Rows)
{
	 
	Console.WriteLine("UserId:{0}, UserName:{1}, RoleId:{2}, RoleName:{3}",
						r["UserId"], r["UserName"], r["RoleId"], r["RoleName"]);
}

 

參考資料

Converting Anonymous type generated by LINQ to a DataTable type

Join Two DataTables Using LINQ In ASP.Net C#

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^