使用GrandChild中的LinQ连接父表、子表和C#表，其中包含来自孙子表的最新记录

Question

--请注意，这只是为了模拟示例，而不是实际表本身.

为冗长的问题道歉

我有三张桌子，供应商，产品和订单。它们如下所示。

供应商-> Id、名称、位置(此表包含有关供应商的所有信息)

产品-> Id，VendorId，名称，描述(此表拥有供应商销售的所有产品)

订单-> Id，ProductId，日期(此表包含该产品的所有订单)

供应商与Product有一对一的关系，但有些供应商可能根本没有产品。

产品表与订单表有一对多的关系。

我正在尝试使用linq获得供应商的最新订单。

举例说明。

供应商：

     1 | Microsoft | Seattle
     2 | Apple     | California
     3 | Amazon    | Seattle

产品：

     1 | 1 | MS Office | Office Suite
     2 | 2 | iPhone    | Smart Phone

顺序：

     1 | 1 | 05/27/2016
     2 | 1 | 06/07/2016
     3 | 2 | 04/17/2016
     4 | 2 | 06/01/2016

预期结果：

     1 | Microsoft | Seattle
          1 | 1 | MS Office | Office Suite
                2 | 1 | 06/07/2016

     2 | Apple | California
          2 | 2 | iPhone | Smart Phone
                4 | 2 | 06/01/2016

     3 | Amazon | Seattle
          null
              null


var vendor = db.Vendor.Include(x => x.Product.Select(s => s.Order)).OrderBy(a => a.Name).Select(s => new VendorModel
        {
            Id = c.Id,
            Name = c.Name,
            Location = c.Location

            Product = c.Product.Select(m => new ProductModel
            {
                Id = m.Id,
                VendorId = m.VendorId,
                Name = m.Name,
                Description = m.Description,
            }).FirstOrDefault(),

            Order = c.Order.Select(m => new OrderModel
            {
                Id = m.Id,
                ProductId = m.ProductId,
                Date = m.Date,
            }).OrderBy(o=>o.Date).FirstOrDefault(),
        });

public class VendorModel
{
    public ApplicationModel() { }
    public ApplicationModel(int id, string name, string location)
    {
        this.Id = id;
        this.Name = name;
        this.Location = location;
    }
    public int Id { get; set; }
    public string Name { get; set; }
    public string Location{ get; set; }
    public virtual ProductModel Product { get; set; }
    public virtual OrderModel Order { get; set; }
}

 public class ProductModel 
{
    public ProductModel() { }
    public ProductModel(int id, int vendorId, string name, string description)
    {
        this.Id = id;
        this.VendorId = vendorId;
        this.Name = name;
        this.Description = description;
    }
    public int Id { get; set; }
    public int VendorId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public VendorModel Vendor { get; set; } 
}


public class OrderModel
{
    public OrderModel() { }
    public OrderModel(int id, int productId, DateTime date)
    {
        this.Id = id;
        this.ProductId = productId;
        this.Date = date;
    }
    public int Id { get; set; }
    public int ProductId { get; set; }
    public DateTime Date { get; set; }
    public ProductModel Product { get; set; }
}

Answer 1

由于Vendor可能有空值，所以我认为您应该使用左外部联接。下面是如何使用LINQ实现左外部联接的方法。

var query = from v in Vendor
            join p in Product on v.id equals p.vendorId into t1
            from vp in t1.DefaultIfEmpty()
            join o in Orders on p.id equals o.productId t2
            from vpo in t2.DefaultIfEmpty()
            orderby v.Name, vpo.date 

select new
{
    v.Name, v.Location,
    productId=(int?)vp.id,  // To handle null value if id(ProductId) is specified as not null in Product table.
    vp.Name, vp.Description,
    orderId = (int?)vpo.id, // To handle null value if id(OrderId) is specified as not null in Orders table.
    vpo.date 
}.ToList();