C# 从两个用 LINQ 连接的数据表创建组合数据表。C＃
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2379747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create combined DataTable from two DataTables joined with LINQ. C#
提问by Robin Day
I have the following code that fills dataTable1and dataTable2with two simple SQL queries, dataTableSqlJoinedis filled from the same tables but joined together.
我有以下代码填充dataTable1并dataTable2使用两个简单的 SQL 查询，dataTableSqlJoined从相同的表填充但连接在一起。
I'm trying to write a LINQ query that can create the dataTableLinqJoinedas if it had been created using SQL. In my example below, it only returns the values from dataTable1.
我正在尝试编写一个 LINQ 查询，该查询可以创建dataTableLinqJoined好像它是使用 SQL创建的一样。在我下面的示例中，它只返回 dataTable1 中的值。
The problem I have is what to put in the SELECTof the linq query. How can I create a new DataRow containing all the Columns from both DataRows. I will not know the exact column names / schema of the queries until runtime.
我SELECT遇到的问题是在 linq 查询中放入什么。如何创建一个包含来自两个 DataRow 的所有列的新 DataRow。直到运行时我才会知道查询的确切列名/模式。
sqlCommand = new SqlCommand("SELECT ID, A, B FROM Table1", sqlConnection, sqlTransaction);
sqlAdapter = new SqlDataAdapter(sqlCommand);
DataTable dataTable1 = new DataTable();
sqlAdapter.Fill(dataTable1);
sqlCommand = new SqlCommand("SELECT ID, C, D FROM Table2", sqlConnection, sqlTransaction);
sqlAdapter = new SqlDataAdapter(sqlCommand);
DataTable dataTable2 = new DataTable();
sqlAdapter.Fill(dataTable2);
sqlCommand = new SqlCommand("SELECT Table1.ID, A, B, Table2.ID, C, D FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID", sqlConnection, sqlTransaction);
sqlAdapter = new SqlDataAdapter(sqlCommand);
DataTable dataTableSqlJoined = new DataTable();
sqlAdapter.Fill(dataTableSqlJoined);
var dataRows =
from
dataRows1 in dataTable1.AsEnumerable()
join
dataRows2 in dataTable2.AsEnumerable()
on
dataRows1.Field<int>("ID") equals dataRows2.Field<int>("ID")
select
dataRows1; // + dataRows2;
DataTable dataTableLinqJoined = dataRows.CopyToDataTable();
For a bit more background, the combined query is very DB intensive and is causing performance issues. The data returned by the first query is fairly static and can be heavily cached. The data returned by the second query changes constantly but is fast to run and therefore doesn't need to be cached. There is also a lot of code reliant upon the passing of the combined DataTable and therefore there are not many feasible options available in passing the data in a different format.
对于更多背景信息，组合查询是非常密集的数据库，并且会导致性能问题。第一个查询返回的数据是相当静态的，可以被大量缓存。第二个查询返回的数据不断变化，但运行速度很快，因此不需要缓存。还有很多代码依赖于组合数据表的传递，因此在以不同的格式传递数据时没有很多可行的选择。
采纳答案by Aaronaught
Have you looked at this page yet?
你看过这个页面了吗？
HOW TO: Implement a DataSet JOIN helper class in Visual C# .NET
如何：在 Visual C# .NET 中实现 DataSet JOIN 帮助器类
If that approach isn't LINQy enough for you, you could break out the row data into object arrays:
如果这种方法对您来说不够 LINQy，您可以将行数据分解为对象数组：
DataTable targetTable = dataTable1.Clone();
var dt2Columns = dataTable2.Columns.OfType<DataColumn>().Select(dc =>
new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
targetTable.Columns.AddRange(dt2Columns.ToArray());
var rowData =
from row1 in dataTable1.AsEnumerable()
join row2 in dataTable2.AsEnumerable()
on row1.Field<int>("ID") equals row2.Field<int>("ID")
select row1.ItemArray.Concat(row2.ItemArray).ToArray();
foreach (object[] values in rowData)
targetTable.Rows.Add(values);
I think that's about as terse as you're going to be able to make it and I'll explain why: it's the schema.
我认为这与您将能够做到的一样简洁，我将解释原因：这是模式。
A DataRowis not an independent object; it depends on its owning DataTableand cannot live without it. There is no supported wayto create a "disconnected" DataRow; the CopyToDataTable()extension method works on rows that already exist in one DataTableand simply copy the schema from the source (remember, every DataRowhas a reference to its parent Table) before copying the rows themselves (most likely using ImportRow, though I haven't actually opened up Reflector to check).
ADataRow不是一个独立的对象；它取决于它的拥有DataTable，没有它就无法生存。有没有支持的方法来创建一个“断开连接” DataRow; 该CopyToDataTable()扩展方法适用于已经存在在一个行DataTable，只是从源复制的模式（请记住，每一个DataRow都有它的父级的引用Table）复制行本身（最有可能使用之前ImportRow，虽然我还没有真正打开了反射来查看）。
In this case you have a new schema that you need to create. Before you can create any (new) rows, you need to create the table to hold them first, and that means writing at least the 3 lines of code at the top of the method above.
在这种情况下，您需要创建一个新架构。在您可以创建任何（新）行，你需要创建表来保存它们首先，这意味着在上述方法的顶部写至少3行代码。
Then you can finally create the rows - but only one at a time, since the DataTableand its associated DataRowCollectiondon't expose any methods to add multiple rows at a time. You could, of course, add your own extension method for the DataRowCollectionto make this "look" nicer:
然后您最终可以创建行 - 但一次只能创建一个行，因为DataTable和它的关联DataRowCollection不会公开任何方法来一次添加多行。当然，您可以添加您自己的扩展方法DataRowCollection以使这个“看起来”更好：
public static void AddRange(this DataRowCollection rc,
IEnumerable<object[]> tuples)
{
foreach (object[] data in tuples)
rc.Add(tuples);
}
Then you could get rid of the foreachin the first method and replace it with:
然后你可以摆脱foreach第一种方法中的 并将其替换为：
targetTable.Rows.AddRange(rowData);
Although that's really just moving the verbosity, not eliminating it.
虽然这实际上只是移动了冗长，而不是消除它。
Bottom line, as long as you're working with the legacy DataSetclass hierarchy, there's always going to be a little cruft. The Linq to DataSet extensions are nice, but they are only extensions and can't alter the limitations above.
最重要的是，只要您使用的是遗留DataSet类层次结构，总会有一些小问题。Linq to DataSet 扩展很好，但它们只是扩展，不能改变上述限制。
回答by Roger Lipscombe
select new {
ID = dataRows1.ID, // no need to select dataRows2.ID, because of JOIN.
A = dataRows1.A,
B = dataRows1.B,
C = dataRows2.C,
D = dataRows2.D
};
回答by shahkalpesh
Pardon me if I sound like an idiot.
如果我听起来像个白痴，请原谅我。
I think, you should have the final table ready (with all the fields of table A & table B).
And, instead of using LINQ, do a join & then do a ForEachon the result & insert the value into final datatable.
我认为，您应该准备好决赛桌（包括表 A 和表 B 的所有字段）。
并且，不要使用 LINQ，而是进行连接，然后ForEach对结果进行操作，然后将值插入最终数据表中。
Pseudocode:
伪代码：
dt1.Join(dt2).Where(...).ForEach(row => code to read the content of anonymous object & add it to finalTable.Rows)
dt1.Join(dt2).Where(...).ForEach(row => 读取匿名对象内容的代码并将其添加到 finalTable.Rows)
回答by suryakiran
Aaronaught that was great. But would like add a few enhancements to your LINQy code. While adding columns from dataTable2 to Target table, there would be chance that few column would be already existing in Target table(on which we are joining). So here we go.
Aaronaught 太棒了。但是想为您的 LINQy 代码添加一些增强功能。将 dataTable2 中的列添加到目标表时，目标表中可能已经存在很少的列（我们正在加入）。所以我们开始了。
DataTable targetTable = dataTable1.Clone();
var dt2Columns = dataTable2.Columns.OfType<DataColumn>().Select(dc =>
new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
var dt2FinalColumns=from dc in dt2Columns.AsEnumerable()
where targetTable.Columns.Contains(dc.ColumnName) == false
select dc;
targetTable.Columns.AddRange(dt2FinalColumns.ToArray());
var rowData =from row1 in dataTable1.AsEnumerable()
join row2 in dataTable2.AsEnumerable()
on row1.Field<int>("ID") equals row2.Field<int>("ID")
select row1.ItemArray.Concat(row2.ItemArray.Where(r2=> row1.ItemArray.Contains(r2)==false)).ToArray();
foreach (object[] values in rowData)
targetTable.Rows.Add(values);
Hope this would be helpful for the guys like me.
希望这对像我这样的家伙有帮助。
