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

本文是小编为大家收集整理的关于LINQ查询-只从子集合中获取订单和MAX日期的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

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

我有 3 个实体:

  1. [客户]:客户 ID、姓名、地址……
  2. [订单]:OrderId, OrderDate, EmployeeId, Total, ...
  3. [OrderStatus]:OrderStatusId、StatusLevel、StatusDate、...

一个客户可以有很多订单,而一个订单又可以有很多OrderStatus,即[客户] 1--* [订单] 1--* [订单状态]

给定一个 CustomerId,我想获取该订单的所有订单(仅 OrderId)和最新的(MAX?)OrderStatus.StatusDate.

我已经尝试了几次,但似乎可以得到我想要的结果.

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

在用户:lazyberezovsky 的出色解决方案之后,我尝试了以下方法:

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,我需要它来返回类型为 T 的项目的集合,因为我必须通过一个严格的 API 中的约束,该 API 有一个 IQueryable 查询作为其参数之一.我知道我可以使用扩展方法 .Expand() 和/或 .Select() 添加其他实体/属性.人们会注意到我上面最新的更新查询在 .Select() 中添加了一个"新客户",它曾经是匿名的.我很肯定这就是为什么查询失败的原因 b/c 由于 LatestOrderDate 不是客户在服务器级别的属性,它不能变成有效的 Uri.仅供参考,在看到下面的第一个答案后,我使用简单的 { get; 将该属性添加到我的客户端 Customer 类中.放;}.因此,鉴于此,我能否以某种方式仍然拥有一个 Customer 集合,只从 2 个不同的实体中带回这 2 个字段?下面的解决方案看起来如此有前途和巧妙!

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

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

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

推荐答案

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

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

               .

更新在内存中构造对象

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

在这里分组也有帮助.

本文地址:https://www.itbaoku.cn/post/1557228.html

问题描述

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.