LINQ关联两张表查询出现重复值，需获取去重后的预期结果

嗨，这个问题很常见——因为你的Transaction表中同一个OrderID对应多条交易记录，内连接后自然会把每个匹配的交易都返回，所以才会出现重复的客户-订单组合。咱们来优化一下查询逻辑，轻松得到去重后的结果：

方案一：先对Transaction表按OrderID去重（兼容所有.NET版本） #

这种方法先处理Transaction集合，提取每个OrderID对应的唯一商店名称（假设同一OrderID的StoreName是一致的，就像你例子里那样），再和Customer表关联，从根源避免重复。

修改后的完整代码：

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

public class Program {
    public static void Main() {
        // Customer collection
        IList<Customer> CustomertList = new List<Customer>() {
            new Customer() { CustomerID = 1, CustomerName = "John", OrderID = 4 } ,
            new Customer() { CustomerID = 2, CustomerName = "Moin", OrderID = 7 } ,
        };
        // Transaction collection
        IList<Transaction> TransactionList = new List<Transaction>() {
            new Transaction() { TransactionID = 1, OrderID = 4, Purchased = "TV",StoreName ="Amazon"} ,
            new Transaction() { TransactionID = 2, OrderID = 7, Purchased = "Laptop",StoreName ="Ebay"} ,
            new Transaction() { TransactionID = 3, OrderID = 7, Purchased = "Mobile",StoreName ="Ebay"} ,
        };

        // 先对Transaction按OrderID分组，获取每个订单对应的唯一商店名称
        var uniqueOrderStores = TransactionList
            .GroupBy(t => t.OrderID)
            .Select(g => new { OrderID = g.Key, StoreName = g.First().StoreName });

        // LINQ关联查询
        var result = (from cus in CustomertList
                      join tran in uniqueOrderStores on cus.OrderID equals tran.OrderID
                      select new {
                          CustomerName = cus.CustomerName,
                          OrderID = cus.OrderID,
                          StoreName = tran.StoreName
                      }).ToList();

        foreach(var item in result){
            Console.WriteLine( "Name - {0} , Order ID - {1} , StoreName - {2}",item.CustomerName , item.OrderID,item.StoreName);
        }
    }
}

public class Customer {
    public int CustomerID { get; set; }
    public string CustomerName { get; set; }
    public int OrderID { get; set; }
}

public class Transaction {
    public int TransactionID { get; set; }
    public int OrderID { get; set; }
    public string Purchased { get; set; }
    public string StoreName { get; set; }
}

执行后得到预期结果：

Name - John , Order ID - 4 , StoreName - Amazon
Name - Moin , Order ID - 7 , StoreName - Ebay

方案二：使用DistinctBy简化代码（.NET 6+适用） #

如果你的项目基于.NET 6或更高版本，可以直接利用LINQ新增的DistinctBy方法，在原查询结果中按客户名称+订单ID去重，代码更简洁：

// 替换原查询部分即可
var result = (from Cus in CustomertList
              join TRan in TransactionList on Cus.OrderID equals TRan.OrderID
              select new {
                  CustomerName = Cus.CustomerName,
                  OrderID = Cus.OrderID,
                  StoreName = TRan.StoreName
              }).DistinctBy(x => new { x.CustomerName, x.OrderID }).ToList();

这个方法会自动过滤掉重复的客户-订单组合，同样能得到你想要的去重结果。

内容的提问来源于stack exchange，提问作者BHAS