Linq to SQL latest date

Question

I have a linq to sql query

var items = from p in ctx.bam_Zending_AllInstances
    join q in ctx.bam_Zending_CompletedRelationships 
        on p.ActivityID equals q.ActivityID
    join r in ctx.bam_Prestatie_AllInstances 
        on q.ReferenceData equals r.ActivityID
   where q.ReferenceType == "Activity" && 
         p.Zendingnummer == zenderNr
   orderby r.PrestatieCode
   select new Data.BAMPrestatieInstance
        {
            Aanvaard = r.PrestatieAanvaard,
            Contactnummer = r.ContactNr,
            Foutmelding = r.Foutmelding,
            Identificatie = r.Identificatie,
            Ontvangen = r.PrestatieZendingOntvangen,
            Uitvoerdatum = r.Uitvoerdatum,
            ZendingsNr = p.Zendingnummer,
            PrestatieCode = r.PrestatieCode
        };

This returns a list of classes of multiple "r.PrestatieCode"s. "Uitvoerdatum" is a date however, and I need to only have the latest date for each r.PrestatieCode. How should I go about with this? Because I know how to do this in sql, but I can't seem to find the way to do it in linq.

Need this a lot atm actually,

any help is usefull,

thanks a bunch!

Gr

Answer 1

If you know how to do it in SQL, you could write it like:

yourDataContext.ExecuteQuery<Data.BAMPrestatieInstance>(
    "<sql query>");

Answer 2

There is probably a more optimal way, but this will do what you want. With items defined as above:

var whatYouWant = items
                      .GroupBy(r => r.PrestatieCode)
                      .Select(
                          g => g.Single(
                              r => r.Uitvoerdatum == g.Max(s => s.Uitvoerdatum)
                          )
                       );

What this does: First it groups your result set by Data.BAMPrestatieInstance.PrestatieCode. Then, from each group with the same Data.BAMPrestatieInstance.PrestatieCode, it extracts the maximum Data.BAMPrestatieInstance.Uitvoerdatum and then finds the unique item with Data.BAMPrestatieInstance.Uitvoerdatum equal to that maximum date. If there is not a unique item with Data.BAMPrestatieInstance.Uitvoerdatum equal to that maximum date you can alter accordingly (use Where instead of Single, for example).

Let me know if I misunderstood your requirements.