获取最近的关联记录

本文关键字：记录 关联 最近 获取 | 更新日期: 2023-09-27 18:14:33

假设我有Customer和Order对象，其中一个Customer可以有许多Orders(因此Order类具有CustomerId属性)，并且我想返回所有CustomerAndMostRecentOrder对象的集合，其定义如下:

public class CustomerAndMostRecentOrder
{
  public Customer Customer { get; set; }
  public Order MostRecentOrder { get; set; }
}

我怎么写一个Linq查询这样做(我使用Linq SQL)?

获取最近的关联记录

您可以使用以下查询:

from c in customers
select new CustomerAndMostRecentOrder
    {
        Customer = c,
        MostRecentOrder = c.Orders.OrderByDescending(o => o.PurchaseDate).FirstOrDefault()
    };

将使用从customer到order的导航属性。MostRecentOrder是通过对一些DateTime属性进行排序，然后加载第一个。

您需要在Order表中有一个CreatedDate日期来获取最近的订单。然后，要获得CustomerAndMostRecentOrder对象，执行以下查询:

from c in customers
join o in orders on c.ID equals o.CustomerID into co
select new CustomerAndMostRecentOrder
{
    Customer = c,
    MostRecentOrder = co.OrderByDescending(o => o.CreatedDate).FirstOrDefault()
}

public class CustomerAndMostRecentOrder
{
    public CustomerAndMostRecentOrder(Customer customer, Order mostRecentOrder)
    {
        Customer = customer;
        MostRecentOrder = mostRecentOrder;
    }
    public Customer Customer { get; set; }
    public Order MostRecentOrder { get; set; }
}
public class Order
{
}

public class Customer
{
    public IEnumerable<Order> GetOrders()
    {
    }
}

public static class UsageClass
{
    public static void Sample(IEnumerable<Customer> allCustomers)
    {
        IEnumerable<CustomerAndMostRecentOrder> customerAndMostRecentOrders =
            allCustomers.Select(customer => new CustomerAndMostRecentOrder(customer, customer.GetOrders().Last()));
    }
}

作为另一种选择，您可能希望查看一下DataLoadOptions。AssociateWith在http://msdn.microsoft.com/en-us/library/system.data.linq.dataloadoptions.associatewith.aspx讨论。只需在上下文上设置您的需求，您就不需要担心在查询级别过滤子元素。

相关文章：