LINQ SELECT with Max and Where in SUBQUERY

Question

I'm trying to code the following SQL statement into LINQ, but struggling. Here is the SQL statement:

SELECT C.ITM_CD, C.BUY_QTY, C.COST
  FROM CST C
 WHERE C.eff_dt IN (SELECT MAX (D.eff_dt)
                      FROM CST D
                     WHERE D.itm_cd = C.itm_cd
                       AND D.eff_dt <= '22-APR-2014')
 ORDER BY C.itm_cd

And here is my LINQ attempt that brings nothing back, even though EVERY eff_dt has a date less than today's date and keep in mind, in my 'real' program this date will be changing.

var results = from c in Csts
              let MaxEffDate = (from d in Csts
                                where d.EffDt <= DateTime.Parse("04/22/2014").Date
                                   && d.ItmCd == c.ItmCd
                                select d.EffDt).Max()
              where c.EffDt.Equals(MaxEffDate)
              select new
              {
                  c.ItmCd,
                  c.BuyQty,
                  c.Content
              };

Lambda code would be great! So for each itm_cd row in the CST table, I want all the rows to come back that have the Max effective date for that itm_cd as long as the eff_dt <= a certain date. I hard-coded today's date so that I know every row should be coming back, but I get nothing.

I've seen a lot of sub-selects on this site but they always use the MAX function without a WHERE clause of <= for that MAX column.

Answer 1

I can't really test this at the moment, but something like this should get you close:

var results = Csts.Where(d => 
                         d.EffDt == Csts.Where(x => 
                                               x.ItmCd == d.ItmCd && 
                                               x.EffDt <= DateTime.Now)
                                        .Max(x => x.EffDt))
                  .OrderBy(d => d.ItmCd)
                  .Select(d => new { d.ItmCd, d.BuyQty, d.Content });