Filtering with Let

Question

I inherited an application with the following tblTradeSpends table:

ID PromoID Customer LineOfBusiness DealYear VersionDate 1 p100 200 SCF 2013 2/23/2013 2 p101 200 SCF 2013 2/23/2013 3 p102 200 SCF 2013 2/23/2013 4 p103 200 SCF 2013 2/23/2013 5 p100 200 SCF 2013 3/23/2013 6 p101 200 SCF 2013 3/23/2013 7 p102 200 SCF 2013 3/23/2013 

The combination of Customer, LineOfBusiness, DealYear, and VersionDate makes up the concept of "Versions" for a block of transactions. The above data describes two versions with the following Promo identifiers:

 Version 1 (Created 2/23/2013): p100, p101, p102, p103 Version 2 (Created 3/23/2013): p100, p101, p102 

So, when this block of deals was updated, the deal with PromoID p103 was deleted.

I have a screen on which I want to display only the latest version for the block of deals. I came up with the following, which I thought works, but actually has an error:

 var records = from t in query let maxversion = (from v in _context.tblTradeSpends where v.PromoID == t.PromoID select v.VersionDate).Max() where t.PlanType == "Planner" && t.VersionDate == maxversion select t 

Now the transaction p103 been deleted by the seller in the latest version, but since it has a maximum date for any transaction with its promo identifier, it is displayed.

 ID PromoID Customer LineOfBusiness DealYear VersionDate 4 p103 200 SCF 2013 2/23/2013 5 p100 200 SCF 2013 3/23/2013 6 p101 200 SCF 2013 3/23/2013 7 p102 200 SCF 2013 3/23/2013 

I do not want p103 in this result set. How can I update my query so that I only display records with the latest version of VersionDate for a unique combination of Customer, LineOfBusiness and DealYear?

UPDATE

If this does not mutate too much, in SQL I would group Customer #, LineOfBusiness and DealYear, determining the maximum VersionDate for the group, and then use this date for my filter. I am not sure how to do the same here.

Answer 1

Something like that?

  var records = from t in query let maxversion = (from v in _context.tblTradeSpends where v.Customer == t.Customer && v.LineOfBusiness == t.LineOfBusiness && v.DealYear == t.DealYear select v.VersionDate).Max() where t.PlanType == "Planner" && t.VersionDate == maxversion select t 

Answer 2

Well, if I understood correctly, p103 is deleted because its VersionDate not the maximum in the table. So this means that when a table has a new VersionDate s, for example, 4/23/2013 , should all records that do not have this date be ignored?

 ID PromoID Customer LineOfBusiness DealYear VersionDate 1 p103 200 SCF 2013 2/23/2013 // Deprecated 2 p102 200 SCF 2013 2/23/2013 // Deprecated 3 p102 200 SCF 2013 3/23/2013 

It looks like you just need a max VersionDate table and all the records that it has. Right?

 var records = from t in query let maxversion = (from v in _context.tblTradeSpends where v.Customer == t.Customer && v.LineOfBusiness == t.LineOfBusiness && v.DealYear == t.DealYear select v.VersionDate).Max() where t.PlanType == "Planner" && t.VersionDate == maxversion select t 

Answer 3

This works for Linq for objects, not sure to use with Linq2SQL / EF

 var records = (from t in _context.tblTradeSpends where t.PlanType == "Planner" group t by new { t.Customer, t.LineOfBusiness, t.DealYear } into g let maxVersion = g.Max(promo => promo.VersionDate) select g.Where(p => p.VersionDate == maxVersion)).SelectMany(s => s) 

Answer 4

I think the problem is that since you use t in your subquery, there is maxversion for each row, not one for everything. You need to select the maximum version from the results you want. This might work:

 var query = query.Where(t => t.PlanType == "Planner"); var records = from t in query let maxversion = (from v in query select v.VersionDate).Max() where t.VersionDate == maxversion select t;