LINQ to Entities find top records from ordered groupings

I have a problem that I know how to solve in SQL but not with Linq to Entities.

My data looks like this:

ID    GROUP   TIMESTAMP
--    -----   ---------
1     A       2011-06-20
2     A       2011-06-21
3     B       2011-06-21
4     B       2011-06-22
5     B       2011-06-23
6     C       2011-06-30

I want to retrieve all the Entity objects (not just the ID) such that I am only getting the most recent record from each group. (ie. the records with ids 2, 5, 6)

In SQL I would do something like this:

SELECT * FROM my_table a
WHERE a.timestamp = 
   (SELECT MAX(timestamp) FROM my开发者_StackOverflow社区_table b
   WHERE a.group = b.group)

(For the sake of this question you can assume that timestamp is unique within each group).

I'd like to do this query against a WCF Data Service using Linq to Entities but I can't seem to have a nested query that references the outside query like this. Can anyone help?

Possibly not as clean and efficient as the hand written version but here's what I came up with

    var q = from a in db.MyEntities
            where a.Timestamp == (from b in db.MyEntities
                                  where b.Group == a.Group
                                  select b.Timestamp).Max()
            select a;

which translates into this SQL

SELECT
[Project1].[Id] AS [Id],
[Project1].[Group] AS [Group],
[Project1].1679022251 AS 1679022251
FROM ( SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[Group] AS [Group],
        [Extent1].1679022251 AS 1679022251,
        [SSQTAB1].[A1] AS [C1]
        FROM [MyEntities] AS [Extent1]
         OUTER APPLY
        (SELECT
                MAX([Extent2].1679022251) AS [A1]
                FROM [MyEntities] AS [Extent2]
                WHERE [Extent2].[Group] = [Extent1].[Group]) AS [SSQTAB1]
)  AS [Project1]
WHERE [Project1].1679022251 = [Project1].[C1]

Hi try to use linqer that will convert your sql statements to linq query.

Linqer

Best Regards

This should work:

var query = db.my_table
   .GroupBy(p=>p.group)
   .Select(p=>p.OrderByDescending(q=>q.timestamp).First());

Here you go.A simple way to do.

      var result = (from x in my_table
                      group x by x.Group into g
                      select new
                      {
                          g.Key,
                          timestamp = g.Max(x => x.TimeStamp),
                          g //This will return everything in g
                      });