LINQ SELECT with Max and Where in SUBQUERY

Question

I'm trying to code the next statement SQLin LINQ, but I'm afraid. Here is the instruction SQL:

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 so my attempt LINQreturns nothing, even if EVERYONE eff_dthas a date less than today's date, and remember that in my "real" program this date will change.

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

The lambda code will be great! Therefore, for each row itm_cdin the table, CSTI want all rows to be returned with the maximum trigger date for this itm_cduntil eff_dt <= a certain date. I hard coded the date today so that I know that every line should be returned, but I get nothing.

, MAX WHERE <= MAX.

Answer 1

, - :

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