根据相关记录的属性过滤出记录 [英] Filtering out records based on a related record's attribute

问题描述

我有一个LINQ查询，如下所示：

I Have a LINQ query like so:

from a in _context.tblTradeSpends
orderby a.DealPeriod, a.CustomerNumber, a.LOB, a.VersionDate descending
select new
{
  DealPeriod = a.DealPeriod,
  CustomerNumber = a.CustomerNumber,
  LOB = a.LOB,
  PromoID = a.PromoID,
  VersionDate = a.VersionDate
}

这是我的结果集中的一个小样本（对于格式化感到遗憾）：

Here is a small sample from my resultset (sorry for the formatting):

DealPeriod  CustomerNumber   LOB    PromoID     VersionDate

2013        10001            PL     P0083312    12/04/2013 9:05
2013        10001            PL     P0083313    12/04/2013 9:05
2013        10001            PL     P0083314    12/04/2013 9:05
2013        10001            PL     P0085100    12/04/2013 9:05
2013        10001            PL     P0086169    12/04/2013 9:05
2013        10001            PL     P0083312    18/01/2013 10:51
2013        10001            PL     P0083313    18/01/2013 10:51
2013        10001            PL     P0083314    18/01/2013 10:51
2013        10001            PL     P0085100    18/01/2013 10:51
2013        10001            PL     P0083312    07/07/2013 15:41
2013        10001            PL     P0083313    07/07/2013 15:41
2013        10001            PL     P0083314    07/07/2013 15:41
2013        10001            SCF    P0083308    03/05/2013 11:27
2013        10001            SCF    P0083309    03/05/2013 11:27
2013        10001            SCF    P0085088    03/05/2013 11:27
2013        10001            SCF    P0085380    03/05/2013 11:27
2013        10001            SCF    P0085381    03/05/2013 11:27
2013        10213            SCF    P0086880    11/07/2013 20:23
2013        10213            SCF    P0086881    11/07/2013 20:23
2013        10213            SCF    P0086882    11/07/2013 20:23
2013        10213            SCF    P0086883    11/07/2013 20:23
2013        10213            SCF    P0083630    28/05/2013 13:38
2013        10213            SCF    P0083631    28/05/2013 13:38
2013        10213            SCF    P0083632    28/05/2013 13:38
2013        10213            SCF    P0083633    28/05/2013 13:38

我需要分析这些数据，所以我想删除某些相关的记录，以便修剪掉。我的规则是，我想删除所有具有相同交易期间，客户号码和LOB字段的所有记录，其中没有版本日期过去1/6/2013（2013年6月1日）

I need to analyze this data, so I'm looking to remove certain related records in order to trim things down. My rule is that I would like to remove all records that have the same Deal Period, Customer Number, and LOB fields where none of them have a version date past 1/6/2013 (June 1, 2013)

所以，在我的结果集中，我想保留以 2013 | 10001 | PL 开头的所有记录，因为某些记录的版本日期为07 / 17/2013

So, in my resultset, I would like to keep all records beginning with 2013|10001|PL as some records have a version date 07/17/2013

我将删除以 2013 | 10001 | SCF 开头的记录，因为没有记录字段值的版本日期早于1/6/2013

I would to remove the records beginning with 2013|10001|SCF because no records with those field values have a version date past 1/6/2013

我想保留以 2013 | 10213 | SCF 因为其中一些记录的版本日期为11/07/2013。

I would like to keep the records beginning with 2013|10213|SCF because some of those records have a version date of 11/07/2013.

编辑

我尝试了King King的代码，我收到了一些不应该在那里的相关记录。以下是我将被过滤出来的两组：

I attempted King King's code, and I got some related records which should not be in there. Here are two groups which I would have been filtered out:

2013    10039   SCF P0083145    05/02/2013 10:22
2013    10039   SCF P0083146    05/02/2013 10:22
2013    10039   SCF P0083147    05/02/2013 10:22
2013    10039   SCF P0085152    05/02/2013 10:22
2013    10039   SCF P0083145    1/22/2013 5:55:00 PM
2013    10039   SCF P0083146    1/22/2013 5:55:00 PM
2013    10039   SCF P0083147    1/22/2013 5:55:00 PM
2013    10039   SCF P0085152    1/22/2013 5:55:00 PM
2013    10039   SCF P0083145    12/05/2012 19:21
2013    10039   SCF P0083146    12/05/2012 19:21
2013    10039   SCF P0083147    12/05/2012 19:21
2013    10040   PL  P0084345    1/14/2013 10:27:00 AM
2013    10040   PL  P0084346    1/14/2013 10:27:00 AM
2013    10040   PL  P0084347    1/14/2013 10:27:00 AM
2013    10040   PL  P0084348    1/14/2013 10:27:00 AM
2013    10040   PL  P0084345    12/20/2012 10:15:00 PM
2013    10040   PL  P0084346    12/20/2012 10:15:00 PM
2013    10040   PL  P0084347    12/20/2012 10:15:00 PM
2013    10040   PL  P0084348    12/20/2012 10:15:00 PM

推荐答案

var fixedDate = DateTime.Parse("6/1/2013");
var result = _context.tblTradeSpends.GroupBy(x=>new {x.DealPeriod, x.CustomerNumber, x.LOB})     
                                    .Where(g=>g.All(x=>x.VersionDate <= fixedDate) || g.Count()==1)
                                    .SelectMany(g=>g);

我相信这段代码应该可以工作，所以我已经尝试创建一个示例列表，由OP的例子给出并测试了代码，结果如下：

I believe this code should work so I've tried creating a sample list as given by the OP's example and tested the code, here is the result:

2013 : 1001 : PL : P0083312 : 4/12/2013 12:00:00 AM
2013 : 1001 : PL : P0083313 : 4/12/2013 12:00:00 AM
2013 : 1001 : PL : P0083314 : 4/12/2013 12:00:00 AM
2013 : 1001 : PL : P0085100 : 4/12/2013 12:00:00 AM
2013 : 1001 : PL : P0086169 : 4/12/2013 12:00:00 AM
2013 : 1001 : PL : P0083312 : 1/18/2013 12:00:00 AM
2013 : 1001 : PL : P0083313 : 1/18/2013 12:00:00 AM
2013 : 1001 : PL : P0083314 : 1/18/2013 12:00:00 AM
2013 : 1001 : PL : P0085100 : 1/18/2013 12:00:00 AM
2013 : 1001 : PL : P0083312 : 12/7/2012 12:00:00 AM
2013 : 1001 : PL : P0083313 : 12/7/2012 12:00:00 AM
2013 : 1001 : PL : P0083314 : 12/7/2012 12:00:00 AM
2013 : 1001 : SCF : P0083308 : 5/3/2013 12:00:00 AM
2013 : 1001 : SCF : P0083309 : 5/3/2013 12:00:00 AM
2013 : 1001 : SCF : P0085088 : 5/3/2013 12:00:00 AM
2013 : 1001 : SCF : P0085380 : 5/3/2013 12:00:00 AM
2013 : 1001 : SCF : P0085381 : 5/3/2013 12:00:00 AM

据我所知OP的要求，这个列表应该是OP所要的（删除所有不需要的条目之后）。

As far as I understand the OP's requirement, this list should be what the OP wants (after removing all the unwanted entries).

这篇关于根据相关记录的属性过滤出记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！