获取最近的关联记录
本文关键字:记录 关联 最近 获取 | 更新日期: 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讨论。只需在上下文上设置您的需求,您就不需要担心在查询级别过滤子元素。