Filtering records based on the corresponding record attribute

Question

I have a LINQ query, for example:

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 example from my result set (sorry for 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 

I need to analyze this data, so I want to delete some related records in order to trim everything. My rule is that I would like to delete all records that have the same fields of the transaction period, customer number and LOB, where none of them have a version date dated 1/6/2013 (June 1, 2013)

So, in my result set, I would like to keep all entries starting with 2013|10001|PL , as some entries have a version date of 07/17/2013

I would like to delete records starting with 2013|10001|SCF , because no records with these field values ​​have a version date dated 1/6/2013

I would like to save entries starting with 2013|10213|SCF because some of these entries have a version date of 11/07/2013.

EDIT

I tried the King King code, and I have some related entries that should not be there. Here are two groups that I would filter 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 

Answer 1

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

I believe this code should work, so I tried to create a sample list defined by the OP 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 

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

Answer 2

Just add a where clause with these conditions;

 from a in _context.tblTradeSpends where a.DealPeriod != 2013 && a.CutomerNumber != 10001 && a.LOB != SCF 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 } 

The where clause will filter out all records starting with 2013|10001|SCF .

Answer 3

First of all, you must create a common resolver for anonymous types:

 public class MyEqualityComparer<T> : IEqualityComparer<T> { Func<T, T, bool> _equalsFunction; Func<T, int> _hashCodeFunction; public MyEqualityComparer( Func<T, T, bool> equalsFunction, Func<T, int> hashCodeFunction) { if (equalsFunction == null) throw new ArgumentNullException(); if (hashCodeFunction == null) throw new ArgumentNullException(); _equalsFunction = equalsFunction; _hashCodeFunction = hashCodeFunction; } public bool Equals(T x, T y) { return _equalsFunction(x, y); } public int GetHashCode(T obj) { return _hashCodeFunction(obj); } } 

Then use the following query:

 var comp = new MyEqualityComparer<tblTradeSpend>( (x, y) => x.DealPeriod == y.DealPeriod && x.CustomerNumber == y.CustomerNumber && x.LOB == y.LOB, obj => obj.DealPeriod.GetHashCode() ^ obj.CustomerNumber.GetHashCode() ^ obj.LOB.GetHashCode() ); var q = (from a in _context.tblTradeSpends.AsEnumerable() where a.VersionDate < DateTime.Parse("6/1/2013") select new { DealPeriod = a.DealPeriod, CustomerNumber = a.CustomerNumber, LOB = a.LOB, PromoID = a.PromoID, VersionDate = a.VersionDate }).Distinct(comp).OrderBy(o => o.DealPeriod) .ThenByDescending(o => o.CustomerNumber) .ThenByDescending(o => o.LOB) .ThenByDescending(o => o.PromoID).ToList();