LINQ 中的不同連線

在以下示例中，我們將使用以下示例：

List<Product> Products = new List<Product>()
{
  new Product()
  {
    ProductId = 1,
    Name = "Book nr 1",
    Price = 25
  },
  new Product()
  {
    ProductId = 2,
    Name = "Book nr 2",
    Price = 15
  },
  new Product()
  {
    ProductId = 3,
    Name = "Book nr 3",
    Price = 20
  },
};
List<Order> Orders = new List<Order>()
{
  new Order()
  {
    OrderId = 1,
    ProductId = 1,
  },
  new Order()
  {
    OrderId = 2,
    ProductId = 1,
  },
  new Order()
  {
    OrderId = 3,
    ProductId = 2,
  },
  new Order()
  {
    OrderId = 4,
    ProductId = NULL,
  },
};

INNER JOIN

查詢語法

var joined = (from p in Products
              join o in Orders on p.ProductId equals o.ProductId
              select new
              {
                o.OrderId,
                p.ProductId,
                p.Name
              }).ToList();

方法語法

var joined = Products.Join(Orders, p => p.ProductId, 
                                   o => o.OrderId, 
                                     => new 
                                    { 
                                      OrderId   = o.OrderId, 
                                      ProductId = p.ProductId, 
                                      Name      = p.Name 
                                    })
                     .ToList();

結果：

{ 1, 1, "Book nr 1" },
{ 2, 1, "Book nr 1" },
{ 3, 2, "Book nr 2" }

LEFT OUTER JOIN

var joined = (from p in Products
              join o in Orders on p.ProductId equals o.ProductId into g
              from lj in g.DefaultIfEmpty()
              select new
              {
                //For the empty records in lj, OrderId would be NULL
                OrderId = (int?)lj.OrderId,
                p.ProductId,
                p.Name
              }).ToList();

結果：

{ 1, 1, "Book nr 1" },
{ 2, 1, "Book nr 1" },
{ 3, 2, "Book nr 2" },
{ NULL, 3, "Book nr 3" }

交叉加入

var joined = (from p in Products
              from o in Orders
              select new
              {
                o.OrderId,
                p.ProductId,
                p.Name
              }).ToList();

結果：

{ 1, 1, "Book nr 1" },
{ 2, 1, "Book nr 1" },
{ 3, 2, "Book nr 2" },
{ NULL, 3, "Book nr 3" },
{ 4, NULL, NULL }

GROUP JOIN

var joined = (from p in Products
              join o in Orders on p.ProductId equals o.ProductId
                into t
              select new
              {
                p.ProductId,
                p.Name,
                Orders = t
              }).ToList();

Propertie Orders 現在包含一個包含所有連結訂單的 IEnumerable<Order>。

結果：

{ 1, "Book nr 1", Orders = { 1, 2 } },
{ 2, "Book nr 2", Orders = { 3 } },
{ 3, "Book nr 3", Orders = { } },

如何加入多個條件

加入單一條件時，你可以使用：

join o in Orders 
  on p.ProductId equals o.ProductId

加入多個時，請使用：

join o in Orders 
  on new { p.ProductId, p.CategoryId } equals new { o.ProductId, o.CategoryId }

確保兩個匿名物件具有相同的屬性，並且在 VB.NET 中，它們必須標記為 Key，儘管 VB.NET 允許由 And 分隔的多個 Equals 子句：

Join o In Orders 
  On p.ProductId Equals o.ProductId And p.CategoryId Equals o.CategoryId