C# LINQ - Left Join Operator. All rows from Left, only matching from Right.

Source code for video

-- class objects
public class PurchaseOrder
{
public int OrderID { get; set; }
public DateTime OrderDate { get; set; }
public int? CustomerID { get; set; }
public OrderStatusType Status { get; set; }
public DateTime? ShippedDate { get; set; }
}


public class OrderLine
{
public int? OrderId { get; set; }
public int? LineID { get; set; }

public string? ItemName { get; set; }
public int? Qty { get; set; }
public decimal? ItemCost { get; set; }
}

public class Customer
{
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}



public enum OrderStatusType : int
{
ORD = 1,
PCK = 2,
SHP = 3,
FIN = 4,
CAN = 5
}


-- build data
public static List<PurchaseOrder> GetPurchaseOrders()
{
var PO = new List<PurchaseOrder>()
{
new PurchaseOrder() { OrderID = 1, CustomerID = 1,
OrderDate = new DateTime(2022, 6, 3),
Status = OrderStatusType.ORD,
ShippedDate = null },

new PurchaseOrder() { OrderID = 2, CustomerID = 3,
OrderDate = new DateTime(2022, 6, 3),
Status = OrderStatusType.FIN,
ShippedDate = new DateTime(2022,6,3) },

new PurchaseOrder() { OrderID = 3, CustomerID = 5,
OrderDate = new DateTime(2022, 6, 3),
Status = OrderStatusType.FIN,
ShippedDate = new DateTime(2022,6,3) },

new PurchaseOrder() { OrderID = 4, CustomerID = 7,
OrderDate = new DateTime(2022, 6, 3),
Status = OrderStatusType.CAN,
ShippedDate = null },

new PurchaseOrder() { OrderID = 5, CustomerID = 9,
OrderDate = new DateTime(2022, 6, 3),
Status = OrderStatusType.FIN,
ShippedDate = new DateTime(2022,6,3) },

};

return (PO);
}


private static List<OrderLine> GetPODetails()
{
List<OrderLine> OL = new List<OrderLine>()
{
new OrderLine() { OrderId=1, LineID=1, ItemName="Part 1", ItemCost=3.55M, Qty=3 },
new OrderLine() { OrderId=1, LineID=2, ItemName="Part 2", ItemCost=6.25M, Qty=7 },
new OrderLine() { OrderId=1, LineID=3, ItemName="Part 3", ItemCost=7.99M, Qty=4 },
new OrderLine() { OrderId=1, LineID=4, ItemName="Part 4", ItemCost=1.29M, Qty=10 },

new OrderLine() { OrderId=2, LineID=1, ItemName="Part 1", ItemCost=3.55M, Qty=3 },
new OrderLine() { OrderId=2, LineID=2, ItemName="Part 2", ItemCost=6.25M, Qty=7 },

new OrderLine() { OrderId=3, LineID=1, ItemName="Part 1", ItemCost=3.55M, Qty=3 },

// orderId=4 was cancelled, so, NO orderline informatino

new OrderLine() { OrderId=5, LineID=1, ItemName="Part 2", ItemCost=6.25M, Qty=10 },
new OrderLine() { OrderId=5, LineID=2, ItemName="Part 3", ItemCost=7.99M, Qty=10 }
};

return (OL);
}

private static List<Customer> GetAllCustomers()
{
List<Customer> C = new List<Customer>()
{
new Customer() { CustomerId=1, CustomerName="Customer 1"},
new Customer() { CustomerId=2, CustomerName="Customer 2"},
new Customer() { CustomerId=4, CustomerName="Customer 4" },
new Customer() { CustomerId=5, CustomerName="Customer 5"},
new Customer() { CustomerId=7, CustomerName="Customer 7"},
new Customer() { CustomerId=8, CustomerName="Customer 8" },
new Customer() { CustomerId=9, CustomerName="Customer 9" }
};

return (C);
}

-- EXAMPLE 1
static void example1(string[] args)
{

var orders = GetPurchaseOrders();
List<Customer> allCustomers = GetAllCustomers();

var finishedOrder = orders
.Where(p => (int)p.Status == (int)OrderStatusType.FIN).ToList();


var query = (from po in finishedOrder
join customer in allCustomers
on po.CustomerID equals customer.CustomerId into joinedGroup

// we know some of the purchaseOrders do not have customers
from cart in joinedGroup.DefaultIfEmpty()

// create an anonymous type
select new
{
order_id = po.OrderID,
status = po.Status,
customer_name = cart == null
? $"ERROR: {po.CustomerID} NOT IN Customer Table"
: cart.CustomerName
}).ToList();

foreach (var line in query)
{
Console.WriteLine($"{line.order_id},{line.status},{line.customer_name}");
}

}



--EXAMPLE 2
static void EXAMPLE2(string[] args)
{

var orders = GetPurchaseOrders();
var orderLines = GetPODetails();


var query = (from po in orders
join lines in orderLines
on po.OrderID equals lines.OrderId into joinedGroup

// we know some of the orders do not have order lines
from cart in joinedGroup.DefaultIfEmpty()

//create an anonymous type
select new
{
order_id = po.OrderID,
item_name = cart == null ? "" : cart.ItemName,
line_id = cart == null ? null : cart.LineID
}).ToList();

foreach (var line in query)
{
Console.WriteLine($"{line.order_id},{line.line_id},{line.item_name}");
}

}