LINQ SELECT with Max and Where in SUBQUERY

c#, linq

Solution

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

Problem

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.

Original source