how do I write this SQL in LINQ Entity Framework 4

Question

select C.CenterID
from   dbo.Center C 
 inner join (select PersonID, max(EffectiveDate) as EffectiveDate
         from   Center
         where  EffectiveDate <= getdate()
         group by PersonID) as C2 
 on C.PersonID= C2.PersonID
  and C.EffectiveDate = C2.EffectiveDate

Center table has an PersonID and EffectiveDate, multiple records have the same PersonID, but different EffectiveDates, I'm trying to return the 1 most current record for each PersonID

ideally, I want to express this in linq as IQueryable so that I can use it to build larger queries.

I take it that "PPTID" is supposed to be "PersonID" in that inner query? Can there be records where EffectiveDate > getdate()? — Matt Hamilton, Jan 21 '11 at 02:24
query is corrected now, yes effectiveDates can be in the future, but they should be excluded from results — Tom, Jan 21 '11 at 04:09

KristoferA · Accepted Answer · 2011-01-21T05:40:16.003

2

var q = from c in oc.Center
join c2 in (
  from ci in oc.Center
  where ci.EffectiveDate <= DateTime.Now
  group ci by ci.PersonID into cig
  select new { PersonID = cig.Key, EffectiveDate = cig.Max(ed => ed.EffectiveDate) }
 ) on new { c.PersonID, c.EffectiveDate } equals { c2.PersonID, c2.EffectiveDate }
select c.CenterID

edited Jan 21 '11 at 05:40

answered Jan 21 '11 at 02:30

KristoferA

12,287
1
40
62

how do I write this SQL in LINQ Entity Framework 4

1 Answers1