0

I have a table 

Table1
PKID    FID     DATE            Col1        Col2
=================================================
1       1       01.01.2014      ABC     QWE
2       1       02.02.2014      BCD     QWE
3       1       03.03.2014      CDE     ASD
4       1       01.01.2014      DEF     AAS
4       2       02.02.2014      EFG     ASD
4       2       03.03.2014      FGH     ASD

I'm trying to group this table by PKID and FID and get the row corresponding to the latest date using LINQ. I tried this:

Dim T1= From t In Table1 _
        Group By t.PKID, t.FID Into Group _
        Select PKID, FID, LastDate = Group.Max(Function(p) p.DATE)

now I have a table with PKID, FID and LastDate that looks like this: 

T1
PKID    FID     LastDate            
==========================
1       1       01.01.2014
2       1       02.02.2014
3       1       03.03.2014
4       2       03.03.2014

To this table I need to add the last 2 columns from Table1 like this

Result
PKID    FID     LastDATE        Col1    Col2
=================================================
1       1       01.01.2014      ABC     QWE
2       1       02.02.2014      BCD     QWE
3       1       03.03.2014      CDE     ASD
4       2       03.03.2014      FGH     ASD

In SQL it would be left joining on the three columns from T1. I know this isn't the best solution but this is all I got. Don't know how to do this in LINQ, or if there is a better solution.

PS: Table1 has a Unique INDEX on (PKID, FID, DATE)

DoNotArrestMe
  • 1,285
  • 1
  • 9
  • 20
Alin I
  • 580
  • 1
  • 7
  • 24
  • This is just a pointer: After the query that you wrote, you can loop through the group by using foreach construct and take the select first record of each group after doing a OrderByDescending. – Dipendu Paul Feb 03 '14 at 08:15

1 Answers1

0

Found this

Dim ResultTable = From x In Table1 _
                  Group x By x.PKID, x.FID Into grp = Group _
                  Select New With {PKID, FID , .Record = (From x In grp Order By x.DATA Descending).FirstOrDefault()})

I have to test it, but it seems to work...

Community
  • 1
  • 1
Alin I
  • 580
  • 1
  • 7
  • 24