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!)