LINQ in C# - Examples - Join, GroupJoin - Joining

• The Join operator performs an INNER JOIN on two sequences based on keys extracted from each element in the sequences.
• In this is example we are going to pull customers only who are having orders
• So As we know while using INNER JOIN, table on left side should have matching record on right side table only will be pulled out

class Order
    {
        public int OrderID { get; set; }
        public int CustomerID { get; set; }
        public string OrderDate { get; set; }
        public decimal Total { get; set; }    
    }
    class Customer
    {
        public int CustomerID { get; set; }      
        public string Name { get; set; }
    }

class Program
    {      
        static void Main(string[] args)
        {
            List<Customer> Customers = new List<Customer> {
                                    new Customer(){ CustomerID=1, Name="XYZ Private Limited" },
                                    new Customer(){ CustomerID=2, Name="ABC Private Limited"},
                                    new Customer(){ CustomerID=3, Name="MS Infotech"},
                                    new Customer(){ CustomerID=4, Name="Oracle" },
                                    new Customer(){ CustomerID=5, Name="IBM Hardware Limited"},
                                 };

            List<Order> Orders = new List<Order> {
                                    new Order(){ OrderID=1, CustomerID=1, Total=1025.24M },
                                    new Order(){ OrderID=2, CustomerID=1, Total=1000.24M },
                                    new Order(){ OrderID=3, CustomerID=2, Total=1140.24M },
                                    new Order(){ OrderID=4, CustomerID=2, Total=1160.24M },
                                    new Order(){ OrderID=5, CustomerID=3, Total=1567.24M },
                                 };

            var CustomerOrder = Customers.Join(Orders,                                           
                                            c=> c.CustomerID,
                                            o => o.CustomerID,
                                            (c, o)=> new { c.Name, order = o }
                                         );
            foreach (var item in CustomerOrder)
            {
                Console.WriteLine($"{item.Name}\n");
               
                    Console.WriteLine("Order Details");
                    Console.WriteLine("-------------");
                    Console.WriteLine($"ID:{item.order.OrderID}, Total:{item.order.Total}");
                    Console.WriteLine();
            }
            Console.ReadLine();
        }

//Output

XYZ Private Limited

Order Details
-------------
ID:1, Total:1025.24

XYZ Private Limited

Order Details
-------------
ID:2, Total:1000.24

ABC Private Limited

Order Details
-------------
ID:3, Total:1140.24

ABC Private Limited

Order Details
-------------
ID:4, Total:1160.24

MS Infotech

Order Details
-------------
ID:5, Total:1567.24

• The GroupJoin operator performs an LEFT JOIN on two sequences based on keys extracted from each element in the sequences, But one Exception is that, it return collection of values from the right side sequence, per matching record in the left side.
• In this is example we are going to pull all customers regardless of they are having matching orders or not and if they have their corresponding orders pulled as a Sequence(Collection) instead of pulling a single record for each matching in the right side of the Sequence
• i.e Single Customer => Collection of Orders they have
• So As we know while using LEFT JOIN, table on left side either or not have matching record on right side table will return all the records

var CustomerOrder = Customers.GroupJoin(Orders,                                           
                                            c=> c.CustomerID,
                                            o => o.CustomerID,
                                            (c, o)=> new { c.Name, orders = o }
                                         );
            foreach (var item in CustomerOrder)
            {
                Console.WriteLine($"{item.Name} => Number of Orders: {item.orders.Count()}\n");
                if (item.orders.Count() > 0)
                {
                    Console.WriteLine("Order Details");
                    foreach (var details in item.orders)
                    {
                        Console.WriteLine($"ID:{details.OrderID}, Total:{details.Total}");
                    }
                }
                Console.WriteLine();
            }

            Console.ReadLine();

//Output

XYZ Private Limited => Number of Orders: 2

Order Details
-------------
ID:1, Total:1025.24
ID:2, Total:1000.24

ABC Private Limited => Number of Orders: 2

Order Details
-------------
ID:3, Total:1140.24
ID:4, Total:1160.24

MS Infotech => Number of Orders: 1

Order Details
-------------
ID:5, Total:1567.24

Oracle => Number of Orders: 0


IBM Hardware Limited => Number of Orders: 0