How to get last children records with parent record with EF
I asked similar question but this time I want to use Entity Framework.
I have database with two tables: Customers (Id PK, LastName, State, etc) and Orders (Id PK, CustomerId FK, ProductName, Price, etc.)
开发者_开发问答I want to retrieve only customer' last order's details together with customer name. I started with:
var orders = from o in db.Orders
where o.Customer.State == "NY"
select o;
var lastOrders = orders.
Where(x => x.Customer.Orders.Where(y=>!y.IsCancelled).
OrderByDescending(z => z.Id).First().Id == x.Id);
But I have feeling that this may not be efficient.
You could try this:
var query = from c in db.Customers
where c.State == "NY"
&& c.Orders.Any(o => !o.IsCancelled)
select new {
Name = c.Name,
Order = c.Orders.Where(o => !o.IsCancelled)
.OrderByDescending(o => o.Id).FirstOrDefault()
};
var customersWithLastOrder = query.ToList();
It gives you a collection of an anonymous type which contains the name and the last order of the customer.
Edit
(Where(...).Count() > 0
replaced by Any(...)
, thanks to BritishDeveloper!)
精彩评论