问题描述
我正在尝试从父子 (1-*) 实体集合模型中获取一个在标签中显示 2 个值的列表.
我有 3 个实体:
- [客户]:客户 ID、姓名、地址……
- [订单]:OrderId, OrderDate, EmployeeId, Total, ...
- [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) });
在这里分组也有帮助.
问题描述
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:
- [Customer]: CustomerId, Name, Address, ...
- [Order]: OrderId, OrderDate, EmployeeId, Total, ...
- [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.