LINQ Group and Distinct -

i still working through understanding linq. below sql trying convert. distinct ident's max seq equals class.

select es.ident   eqpseq es inner join eqpseq esmax on esmax.ident= es.ident    es.class = 4 group es.ident, es.seq having   es.seq= max(esmax.seq); 

the data looks like

ident     seq     class 10        1       4 10        2       5 10        3       4 

the result of when class = 4 should be

ident 10 

the result of when class = 5 should null.

i thought linq query might work returning 2 rows of ident 10.

from es in eqpseqs join esmax in eqpseqs on es.ident equals esmax.ident es.class == 4 group es new {    es.ident, es.seq } g g.key.seq == g.max(p => p.seq) select new {    ident = (int?)g.key.ident } 

any thoughts appreciated. in interested see linq fluent style also.

yeah, given sample data not surprising same value twice. joining table on ident value of ident same in every row. result of join is, essentially, cartesian product of rows:

es.ident  es.seq  es.class  esmax.ident  esmax.seq  esmax.class 10        1       4         10           1          4 10        1       4         10           2          5 10        1       4         10           3          4 10        2       5         10           1          4 10        2       5         10           2          5 10        2       5         10           3          4 10        3       4         10           1          4  // row matches 10        3       4         10           2          5 10        3       4         10           3          4  // row 

as suggested, adding distinct query should solve this:

(from es in eqpseqs join esmax in eqpseqs on es.ident equals esmax.ident es.class == 4 group es new {    es.ident, es.seq } g g.key.seq == g.max(p => p.seq) select new {    ident = (int?)g.key.ident }) .distinct(); 

there couple of different queries try, well.

i use ix-main nuget package often, myself. package comes handy maxby function. if don't mind adding package this:

eqpseq  .groupby(x => new { x.ident, x.seq }) .maxby(x => x.key.seq) .selectmany(x => x) .where(x => x.class == 4) .select(x => x.ident) .distinct(); 

you same thing without ix-main this:

eqpseq  .where(x => x.seq == eqpseq.max(y => y.seq) && x.class == 4) .select(x => x.ident) .distinct(); 

off top of head not sure of 3 queries performant. if seq column indexed bet third query winner.