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.

其他推荐答案

If I read this correctly you want a Customer entity and then a single value computed from its Orders property. Currently this is not supported in OData. OData doesn't support computed values in the queries. So no expressions in the projections, no aggregates and so on.

Unfortunately even with two queries this is currently not possible since OData doesn't support any way of expressing the MAX functionality.

If you have control over the service, you could write a server side function/service operation to execute this kind of query.