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

问题描述

我正在尝试从父子(1- *)实体集合模型中获取一个在标签中显示2个值的列表.

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

我有3个实体:

1. [客户]:客户编号，名称，地址...
2. [订单]:OrderId，OrderDate，EmployeeId，总计...
3. [ OrderStatus ]:OrderStatusId，StatusLevel，StatusDate等...

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

客户可以拥有许多 Order ，而该订单又可以具有许多 OrderStatus ，即 [客户] 1-* [订单] 1-* [ OrderStatus ]

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

鉴于有一个CustomerId，我想获取该订单的所有订单(仅是OrderId)和最新(最大?)OrderStatus.StatusDate.

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 ;
}

----更新1 ----

---- UPDATE 1 ----

在用户提供了很好的解决方案之后: lazyberezovsky ，我尝试了以下操作:

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) 
            });

由于我在最初发布时的仓促，我没有正确地粘贴所有内容，因为它主要来自内存，并且当时没有确切的代码可供参考.我的方法是强类型IQueryabled，由于刚性API的约束(需要将IQueryable作为其参数之一)，因此我需要它返回类型T的项目的集合.我知道我可以使用扩展方法.Expand()和/或.Select()添加其他实体/属性.可能会注意到，我上面的最新UPDATED查询在.Select()中曾经是匿名的位置添加了一个新客户".我很肯定这就是为什么查询失败的原因是B/c无法将其转换为有效的Uri，原因是LatestOrderDate不是服务器级别上Customer的属性.仅供参考，在看到下面的第一个答案后，我已使用简单的{get;放; }.因此，鉴于此，我还能以某种方式仍然拥有一个Customer集合，并且仅从两个不同实体中带回这2个字段吗?下面的解决方案看起来如此有前途和精巧！

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!

----结束更新1 ----

---- END UPDATE 1 ----

仅供参考，我使用的技术是OData(WCF)，Silverlight，C#.

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

任何提示/链接将不胜感激.

Any tips/links will be appreciated.

推荐答案

这将为您提供{ OrderId, LatestDate }对象的列表

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 在内存中构造对象

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.

这篇关于LINQ查询-仅从子集合中获取订单和最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！