C# DataTable Inner Join 合併：純記憶體資料表關聯查詢實作

場景

離線資料集 (DataSet/DataTable) 需要像 SQL 一樣做 Inner Join，不想/不能回資料庫。ADO.NET 提供 DataRelation 機制可高效完成。

核心實作

using System.Data;

public static class DataTableExtensions
{
    /// 

    /// 執行 Inner Join，回傳合併欄位的新 DataTable
    /// 
    public static DataTable InnerJoin(
        this DataTable left,
        DataTable right,
        IEnumerable leftKeys,
        IEnumerable rightKeys)
    {
        if (left == null || right == null) throw new ArgumentNullException();
        if (leftKeys.Count() != rightKeys.Count()) throw new ArgumentException("鍵欄位數量必須相同");

        var result = new DataTable("JoinResult");
        using (var ds = new DataSet())
        {
            // 1. 複製結構與資料到 DataSet (避免污染原表)
            ds.Tables.Add(left.Copy());
            ds.Tables.Add(right.Copy());

            // 2. 取得複製後的對應欄位
            var leftKeyCols = leftKeys.Select(c => ds.Tables[0].Columns[c.ColumnName]).ToArray();
            var rightKeyCols = rightKeys.Select(c => ds.Tables[1].Columns[c.ColumnName]).ToArray();

            // 3. 建立關聯
            var relation = new DataRelation("JoinRelation", leftKeyCols, rightKeyCols, false);
            ds.Relations.Add(relation);

            // 4. 建立結果表結構 (處理同名欄位)
            foreach (DataColumn c in ds.Tables[0].Columns)
                result.Columns.Add(c.ColumnName, c.DataType);

            foreach (DataColumn c in ds.Tables[1].Columns)
            {
                string name = result.Columns.Contains(c.ColumnName)
                    ? c.ColumnName + "_Right" // 衝突改名
                    : c.ColumnName;
                result.Columns.Add(name, c.DataType);
            }

            // 5. 依關聯填入資料
            result.BeginLoadData();
            foreach (DataRow leftRow in ds.Tables[0].Rows)
            {
                DataRow[] rightRows = leftRow.GetChildRows(relation);
                if (rightRows.Length == 0) continue; // Inner Join：無對應則略過

                object[] leftValues = leftRow.ItemArray;
                foreach (DataRow rightRow in rightRows)
                {
                    object[] rightValues = rightRow.ItemArray;
                    var joined = new object[leftValues.Length + rightValues.Length];
                    Array.Copy(leftValues, 0, joined, 0, leftValues.Length);
                    Array.Copy(rightValues, 0, joined, leftValues.Length, rightValues.Length);
                    result.LoadDataRow(joined, true);
                }
            }
            result.EndLoadData();
        }
        return result;
    }

    /// 
單欄位鍵簡化版
    public static DataTable InnerJoin(this DataTable left, DataTable right, string leftKey, string rightKey)
        => left.InnerJoin(right, new[] { left.Columns[leftKey] }, new[] { right.Columns[rightKey] });
}

使用範例

// 模擬資料
var orders = new DataTable("Orders");
orders.Columns.Add("OrderId", typeof(int));
orders.Columns.Add("CustomerId", typeof(int));
orders.Columns.Add("Amount", typeof(decimal));
orders.Rows.Add(1, 101, 1500);
orders.Rows.Add(2, 102, 2300);
orders.Rows.Add(3, 103, 900);

var customers = new DataTable("Customers");
customers.Columns.Add("CustomerId", typeof(int));
customers.Columns.Add("Name", typeof(string));
customers.Columns.Add("City", typeof(string));
customers.Rows.Add(101, "Alice", "Taipei");
customers.Rows.Add(102, "Bob", "Taichung");
customers.Rows.Add(104, "Cindy", "Kaohsiung"); // 無訂單

// Join
var joined = orders.InnerJoin(customers, "CustomerId", "CustomerId");

foreach (DataRow row in joined.Rows)
{
    Console.WriteLine($"OrderId={row["OrderId"]}, Customer={row["Name"]}, City={row["City"]}, Amount={row["Amount"]}");
}
/*
OrderId=1, Customer=Alice, City=Taipei, Amount=1500
OrderId=2, Customer=Bob, City=Taichung, Amount=2300
*/

效能考量與替代方案

高效 Dictionary Lookup 實作 (大資料量推薦)

public static DataTable FastInnerJoin(DataTable left, DataTable right, string key)
{
    var result = left.Clone();
    foreach (DataColumn c in right.Columns)
        if (!result.Columns.Contains(c.ColumnName)) result.Columns.Add(c.ColumnName, c.DataType);
        else result.Columns.Add(c.ColumnName + "_Right", c.DataType);

    // 建立右表索引
    var rightIndex = new Dictionary>();
    foreach (DataRow r in right.Rows)
    {
        var k = r[key];
        if (!rightIndex.TryGetValue(k, out var list))
            rightIndex[k] = list = new List();
        list.Add(r);
    }

    // 掃描左表查找
    foreach (DataRow lrow in left.Rows)
    {
        if (rightIndex.TryGetValue(lrow[key], out var matches))
        {
            foreach (var rrow in matches)
            {
                var nr = result.NewRow();
                foreach (DataColumn c in left.Columns) nr[c.ColumnName] = lrow[c];
                foreach (DataColumn c in right.Columns)
                {
                    string name = result.Columns.Contains(c.ColumnName) ? c.ColumnName + "_Right" : c.ColumnName;
                    nr[name] = rrow[c];
                }
                result.Rows.Add(nr);
            }
        }
    }
    return result;
}

此法避免 DataSet 複製開銷，單次掃描建立雜湊索引，大量資料 (百萬列) 亦能秒級完成。