本文是小编为大家收集整理的关于LINQ查询-只从子集合中获取订单和MAX日期的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。
问题描述
我正在尝试获取一个列表,该列表来自父母(1-*)实体收集模型中的标签中2个值.
我有3个实体:
- [客户]:客户ID,名称,地址,...
- [ order ]:OrderID,OrderDate,雇员,总计,...
- [ orderstatus ]:orderstatusid,statuslevel,statusdate,...
a 客户可以拥有许多订单,从而依次可以拥有许多 orderstatus ,即. [客户] 1 - * [ order ] 1 - * [ orderstatus ]
给出了一个客户ID,我想获得所有订单(仅订购)和该订单的最新(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中的约束,我必须通过该限制,该刚度必须将其作为其参数之一.我知道我可以使用扩展方法.expand()和/或.select()添加其他实体/属性.有人会注意到,我上面的最新更新的查询在.select()中曾经是匿名的.我很肯定,这就是为什么查询失败的b/c的原因,由于最新订单不在服务器级别上是客户的属性,因此无法将其变成有效的URI.仅供参考,在下面看到第一个答案后,我将该属性添加到了我的客户端客户类中,简单{get;放; }.因此,鉴于此,我是否可以以某种方式拥有一个客户收集,而从两个不同实体中又带回了这两个字段?下面的解决方案看起来很有希望和巧妙!
----结束更新1 ----
fyi,我正在使用的技术是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) }); .
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) });
也可以在这里进行分组.
其他推荐答案
如果我正确阅读了此信息,则需要一个客户实体,然后从其订单属性中计算出的单个值.目前,ODATA不支持这. ODATA不支持查询中的计算值.因此,预测中没有表达式,没有聚合等.
不幸的是,即使有两个查询,目前也无法进行,因为ODATA不支持表达最大功能的任何方法.
如果您控制了服务,则可以编写服务器端功能/服务操作以执行此类查询.
问题描述
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.
其他推荐答案
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.