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