Linq Query Join two tables getting duplicate values-CodePudding

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 = 04 } ,
                new Customer() { CustomerID = 2, CustomerName = "Moin",  OrderID = 07 } ,
            };
        
        // Transaction collection
        IList<Transaction> TransactionList = new List<Transaction>() { 
            new Transaction() { TransactionID = 1, OrderID = 04, Purchased = "TV",StoreName ="Amazon"} ,
            new Transaction() { TransactionID = 2, OrderID = 07, Purchased = "Laptop",StoreName ="Ebay"} ,
            new Transaction() { TransactionID = 3, OrderID = 07, Purchased = "Mobile",StoreName ="Ebay"} ,
        };
        
        // LINQ Query 
        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
                     }).ToList();
        
        
        foreach(var Cus in result){         
            Console.WriteLine( "Name - {0} ,  Order ID - {1} , StoreName - {2}",Cus.CustomerName , Cus.OrderID,Cus.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; }
}

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

From the above code i am getting the above result,I need to show what are all order and Customer name and Store where they purchase, but i am getting duplicate values, i put the normal join query but i need below Expected result.

Expected Result :
Name - John, Order ID - 4, StoreName - Amazon
Name - Moin, Order ID - 7, StoreName - Ebay .

CodePudding user response：

just try this

.....
 select new
              {
                  CustomerName = Cus.CustomerName,
                  OrderID = Cus.OrderID,
                  StoreName = TRan.StoreName
              }).Distinct().ToList();

CodePudding user response：

It's duplicating because your key is duplicated... If you try what Serge said it'll works fine.

 // LINQ Query 
        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
                     }).Distinct().ToList();

OR you can group by CustomerName, OrderID and StoreName in TransactionList before your result query. Using GroupBy you won't need to use Distinct()