从两个用 LINQ 连接的数据表创建组合数据表.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 的 LINQ 查询，就好像它是使用 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 的所有列的新 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();

对于更多的背景知识，组合查询是非常密集的数据库，并且会导致性能问题.第一个查询返回的数据是相当静态的，可以被大量缓存.第二个查询返回的数据不断变化，但运行速度很快，因此不需要缓存.还有很多代码依赖于组合数据表的传递，因此以不同的格式传递数据的可行选项并不多.

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 都有在复制行本身之前对其父 Table 的引用(最有可能使用 ImportRow，尽管我实际上还没有打开 Reflector 进行检查).

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 连接的数据表创建组合数据表.C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！