LINQ查询-只从子集合中获取订单和MAX日期[英] LINQ Query - Only get Order and MAX Date from Child Collection

问题描述

I'm trying to get a list that displays 2 values in a label from a parent and child (1-*) entity collection model.

I have 3 entities:

1. [Customer]: CustomerId, Name, Address, ...
2. [Order]: OrderId, OrderDate, EmployeeId, Total, ...
3. [OrderStatus]: OrderStatusId, StatusLevel, StatusDate, ...

A Customer can have MANY Order, which in turn an Order can have MANY OrderStatus, i.e. [Customer] 1--* [Order] 1--* [OrderStatus]

Given a CustomerId, I want to get all of the Orders (just OrderId) and the LATEST (MAX?) OrderStatus.StatusDate for that Order.

I've tried a couple of attempts, but can seem to get the results I want.

private IQueryable<Customer> GetOrderData(string customerId)
{
     var ordersWithLatestStatusDate = Context.Customers

     // Note: I am not sure if I should add the .Expand() extension methods here for the other two entity collections since I want these queries to be as performant as possible and since I am projecting below (only need to display 2 fields for each record in the IQueryable<T>, but thinking I should now after some contemplation.

     .Where(x => x.CustomerId == SelectedCustomer.CustomerId)
     .Select(x => new Custom 
     {
         CustomerId = x.CustomerId,
         ...
         // I would like to project my Child and GrandChild Collections, i.e. Orders and OrderStatuses here but don't know how to do that. I learned that by projecting, one does not need to "Include/Expand" these extension methods.  
     });
     return ordersWithLatestStatusDate ;
}

---- UPDATE 1 ----

After the great solution from User: lazyberezovsky, I tried the following:

var query = Context.Customers
            .Where(c => c.CustomerId == SelectedCustomer.CustomerId)
            .Select(o => new Customer 
            { 
                Name = c.Name, 
                LatestOrderDate = o.OrderStatus.Max(s => s.StatusDate) 
            });

In my hastiness from my initial posting, I didn't paste everything in correctly since it was mostly from memory and didn't have the exact code for reference at the time. My method is a strongly-typed IQueryabled where I need it to return a collection of items of type T due to a constraint within a rigid API that I have to go through that has an IQueryable query as one of its parameters. I am aware I can add other entities/attributes by either using the extension methods .Expand() and/or .Select(). One will notice that my latest UPDATED query above has an added "new Customer" within the .Select() where it was once anonymous. I'm positive that is why the query failed b/c it couldn't be turn into a valid Uri due to LatestOrderDate not being a property of Customer at the Server level. FYI, upon seeing the first answer below, I had added that property to my client-side Customer class with simple { get; set; }. So given this, can I somehow still have a Customer collection with the only bringing back those 2 fields from 2 different entities? The solution below looked so promising and ingenious!

---- END UPDATE 1 ----

FYI, the technologies I'm using are OData (WCF), Silverlight, C#.

Any tips/links will be appreciated.

推荐答案

This will give you list of { OrderId, LatestDate } objects

var query = Context.Customers
                   .Where(c => c.CustomerId == SelectedCustomer.CustomerId)
                   .SelectMany(c => c.Orders)
                   .Select(o => new { 
                              OrderId = o.OrderId, 
                              LatestDate = o.Statuses.Max(s => s.StatusDate) });

               .

UPDATE construct objects in-memory

var query = Context.Customers
                   .Where(c => c.CustomerId == SelectedCustomer.CustomerId)
                   .SelectMany(c => c.Orders)
                   .AsEnumerable() // goes in-memory
                   .Select(o => new { 
                              OrderId = o.OrderId, 
                              LatestDate = o.Statuses.Max(s => s.StatusDate) });

Also grouping could help here.