c# - EF Query Help - Grouping and Sub queries -

i have data table (docs, dbset in context) below data

id   code   rev 1    a1     1 2    a1     2 3    a1     3 4    a3     1 5    a2     1 6    a2     2 

i need select records has records each code has highest rev. expected result

id   code   rev 3    a1     3 6    a2     2 4    a3     1 

the id column pk of table , code+rev unique.
note: there other fields in table need result. ideal iqueryable (doc model class), think of selecting id within inner query , use iqueryable of docs.

thanks in advance

try this:

var res = r in docs           (from c in docs                  group c c.code g                  select new {                     localcode = g.key,                     localrev = g.max(t => t.rev)                  }).any(x => x.localcode == r.code && x.localrev == r.rev)           select r; 

res iqueryable.