LINQ to SQL 集合運算符 Union / Intersect / Except 詳解

核心概念

LINQ提供三大集合運算符操作序列：

• Intersect（交集）：返回兩個序列中都存在的不重複元素。延遲執行。先遍歷第一序列建立唯一元素集，再遍歷第二序列匹配。
• Except（差集）：返回僅存在於第一序列的不重複元素。延遲執行。先建立第一序列唯一集，再遍歷第二序列排除。
• Union（並集）：合併兩序列並自動去重。延遲執行。

完整演示代碼

using System;
using System.Collections.Generic;
using System.Linq;

namespace SetOperatorsDemo {
    class Customer { public string ID, City, Country, Region; public decimal Sales; }
    class Order { public string ID; public decimal Amount; }

    class Program {
        static void Main() {
            var orders = new List<Order> {
                new Order { ID="P", Amount=100 }, new Order { ID="Q", Amount=200 },
                new Order { ID="R", Amount=300 }, new Order { ID="S", Amount=400 },
                new Order { ID="T", Amount=500 }, new Order { ID="U", Amount=600 },
                new Order { ID="V", Amount=700 }, new Order { ID="W", Amount=800 },
                new Order { ID="X", Amount=900 }, new Order { ID="Y", Amount=1000 },
                new Order { ID="Z", Amount=1100 }
            };

            var customers = new List<Customer> {
                new Customer { ID="A", City="New York", Country="USA", Region="North America", Sales=9999 },
                new Customer { ID="B", City="Mumbai", Country="India", Region="Asia", Sales=8888 },
                new Customer { ID="C", City="Karachi", Country="Pakistan", Region="Asia", Sales=7777 },
                new Customer { ID="D", City="Delhi", Country="India", Region="Asia", Sales=6666 },
                new Customer { ID="E", City="Sao Paulo", Country="Brazil", Region="South America", Sales=5555 },
                new Customer { ID="F", City="Moscow", Country="Russia", Region="Europe", Sales=4444 },
                new Customer { ID="G", City="Seoul", Country="Korea", Region="Asia", Sales=3333 },
                new Customer { ID="H", City="Istanbul", Country="Turkey", Region="Asia", Sales=2222 },
                new Customer { ID="I", City="Shanghai", Country="China", Region="Asia", Sales=1111 },
                new Customer { ID="J", City="Lagos", Country="Nigeria", Region="Africa", Sales=1000 },
                new Customer { ID="K", City="Mexico City", Country="Mexico", Region="North America", Sales=2000 },
                new Customer { ID="L", City="Jakarta", Country="Indonesia", Region="Asia", Sales=3000 },
                new Customer { ID="M", City="Tokyo", Country="Japan", Region="Asia", Sales=4000 },
                new Customer { ID="N", City="Los Angeles", Country="USA", Region="North America", Sales=5000 },
                new Customer { ID="O", City="Cairo", Country="Egypt", Region="Africa", Sales=6000 },
                new Customer { ID="P", City="Tehran", Country="Iran", Region="Asia", Sales=7000 },
                new Customer { ID="Q", City="London", Country="UK", Region="Europe", Sales=8000 },
                new Customer { ID="R", City="Beijing", Country="China", Region="Asia", Sales=9000 },
                new Customer { ID="S", City="Bogota", Country="Colombia", Region="South America", Sales=1001 },
                new Customer { ID="T", City="Lima", Country="Peru", Region="South America", Sales=2002 }
            };

            var customerIDs = from c in customers select c.ID;
            var orderIDs = from o in orders select o.ID;

            // 1. 交集：既是客戶又有訂單的ID
            var customersWithOrders = customerIDs.Intersect(orderIDs);
            Console.WriteLine("Customers with Orders:");
            foreach (var id in customersWithOrders) Console.Write("{0} ", id);
            Console.WriteLine();

            // 2. 差集：有訂單但非客戶的ID
            var ordersNoCustomers = orderIDs.Except(customerIDs);
            Console.WriteLine("Orders with no customers:");
            foreach (var id in ordersNoCustomers) Console.Write("{0} ", id);
            Console.WriteLine();

            // 3. 並集：所有不重複ID
            var allCustomerOrderIDs = orderIDs.Union(customerIDs);
            Console.WriteLine("All Customer and Order IDs:");
            foreach (var id in allCustomerOrderIDs) Console.Write("{0} ", id);
            Console.WriteLine();

            Console.Write("Press Enter to exit...");
            Console.ReadLine();
        }
    }
}

運行結果

Customers with Orders:
P Q R S T
Orders with no customers:
U V W X Y Z
All Customer and Order IDs:
P Q R S T U V W X Y Z A B C D E F G H I J K L M N O

關鍵要點

• 三者均為延遲執行，僅在 foreach 迭代時真正計算。
• 自動去重：結果序列不包含重複元素。
• 適用於實現 IEnumerable<T> 的任意數據源（內存集合、數據庫查詢等）。
• 自定義類型需實現 IEquatable<T> 或傳入 IEqualityComparer<T> 以定義「相等」語義。