创建数据表合并从两个DataTable的LINQ加入。 C＃ [英] Create combined DataTable from two DataTables joined with LINQ. C#

问题描述

我有以下的code，填补 dataTable1 和 dataTable2 有两个简单的SQL查询， 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.

我想写一个LINQ查询，可以创建 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.

我的问题是要放什么东西在LINQ查询的 SELECT 。我怎样才能创建一个包含所有来自两个数据行的列一个新的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密集，导致性能问题。由第一查询返回的数据是相当静态的，可以在很大程度上缓存。该数据通过不断的第二查询的变化返回，但运行速度快，因此，不需要进行缓存。还有在路过依赖很多code的结合数据表，因此不会有在传递数据以不同的格式提供多种可行方案。

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.

推荐答案

你有没有看这个页面了吗？

Have you looked at this page yet?

如何：实现在Visual C一个DataSet JOIN Helper类＃.NET

如果这个方法不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 不是一个独立的对象;这取决于其所属数据表，不能没有它。还有的不支持的方法的创建断开连接的DataRow ;在 CopyToDataTable（）扩展方法适用于已经存在于一个数据表，简单的模式从源复制（行记住，每一个的DataRow （最有可能使用<$ C复制行本身之前有提到其父表） $ C> 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行code的。

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.

然后你终于可以创建行 - 但只有一次，因为数据表及其相关联的 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 并将其替换为：

targetTable.Rows.AddRange(rowData);

虽然这真的只是移动冗长，不排除它。

Although that's really just moving the verbosity, not eliminating it.

底线，只要你与传统的DataSet 类层次的工作，总是要有点克鲁夫特。 LINQ的为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.

这篇关于创建数据表合并从两个DataTable的LINQ加入。 C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！