从与LINQ连接的两个DataTable中创建组合的DataTable。 C# [英] Create combined DataTable from two DataTables joined with LINQ. C#
问题描述
我有以下代码,用两个简单的SQL查询 dataTable1
和 dataTable2
> dataTableSqlJoined 从相同的表填充,但是加在一起。
I have the following code that fills dataTable1
and dataTable2
with two simple SQL queries, dataTableSqlJoined
is filled from the same tables but joined together.
我正在尝试编写一个可以创建 dataTableLinqJoined
好像它是使用SQL创建的。在下面的例子中,它只返回dataTable1中的值。
I'm trying to write a LINQ query that can create the dataTableLinqJoined
as if it had been created using SQL. In my example below, it only returns the values from dataTable1.
我有一个问题是把 SELECT
的linq查询。如何创建一个包含DataRows中所有列的新DataRow。在运行时,我不会知道查询的列名/模式。
The problem I have is what to put in the SELECT
of 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.
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();
对于更多的背景,组合查询是非常DB密集型的,并导致性能问题。第一个查询返回的数据是相当静态的,可以被高度缓存。第二个查询返回的数据不断变化,但运行速度很快,因此不需要缓存。还有很多代码依赖于组合的DataTable的传递,因此在传递不同格式的数据时没有太多可行的选项。
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.
推荐答案
您是否看过这个页面?
如何在Visual C#.NET中实现DataSet JOIN助手类
如果这种方法不是LINQy对于您来说,您可以将行数据分解为对象数组:
If that approach isn't LINQy enough for you, you could break out the row data into object arrays:
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 DataRow
不是一个独立的对象;这取决于它拥有的 DataTable
,不能没有它生活。有不支持的方式创建一个断开连接 DataRow
; CopyToDataTable()
扩展方法适用于已经存在于一个 DataTable
中的行,只需从源代码复制模式(记住,在复制行本身之前,每个 DataRow
都有对其父表
的引用)(很可能使用 ImportRow
,虽然我没有真正打开反射器来检查)。
A DataRow
is not an independent object; it depends on its owning DataTable
and cannot live without it. There is no supported way to create a "disconnected" DataRow
; the CopyToDataTable()
extension method works on rows that already exist in one DataTable
and simply copy the schema from the source (remember, every DataRow
has 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).
在这种情况下,你有一个新的模式,你需要创建。在创建任何(新)行之前,您需要创建表以保持它们第一个,这意味着至少在上述方法的顶部写入3行代码。
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.
然后,您可以最终创建行,但每次只能有一个,因为 DataTable
及其关联的 DataRowCollection
不会公开任何方法一次添加多行。当然,您可以为 DataRowCollection
添加自己的扩展方法,使此外观更好:
Then you can finally create the rows - but only one at a time, since the DataTable
and its associated DataRowCollection
don't expose any methods to add multiple rows at a time. You could, of course, add your own extension method for the DataRowCollection
to make this "look" nicer:
public static void AddRange(this DataRowCollection rc,
IEnumerable<object[]> tuples)
{
foreach (object[] data in tuples)
rc.Add(tuples);
}
然后你可以摆脱 foreach
在第一个方法,并替换为:
Then you could get rid of the foreach
in the first method and replace it with:
targetTable.Rows.AddRange(rowData);
虽然这只是移动详细程度,而不是消除它。
Although that's really just moving the verbosity, not eliminating it.
底线,只要你使用传统的 DataSet
类层次结构,总是会有一点点。 Linq to DataSet扩展是很好的,但它们只是扩展,不能改变上述限制。
Bottom line, as long as you're working with the legacy DataSet
class 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.
这篇关于从与LINQ连接的两个DataTable中创建组合的DataTable。 C#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!