LINQ 2 SQL: top 1 post per member ordered by created data

Question

Okay so I have the sql to work this out as asked in the stackoverflow question here.

Does anyone know how to translate this to linq 2 sql? I'm guessing that the easiest way is to add a stored procedure, but I am curious to see if it can be linq-a-fied :P

select p.* 
from post p join
(
    select memberId, max(createdDate) as maxd 
    from post 
    group by memberId

) as p2 on p.memberid = p2.memberid and p.createdDate=p2.maxd
order by p.createdDate desc

Answer 1

I'm not totally sure this is the most efficient way to run this query (maybe it is, but I've got a feeling there's a better way. Haven't thought of it yet).

from
    post in Nt_Post
join
    memberdates in (
        from
                p_inner in Nt_Post
        group
                p_inner by p_inner.MemberId into grouped 
        select new {
                MemberId = grouped.Key,
                ActivationDate = grouped.Max(m => m.ActivationDate)
        })
on
    new { post.MemberId, post.ActivationDate }
    equals
    new { memberdates.MemberId, memberdates.ActivationDate }
orderby post.ActivationDate
select post;

Answer 2

Here is the query working within LinqPad on my database (not createdDate is actually activationDate and the Post table is Nt_Post. Thanks to Rex M for commming up with the solution :P

var q =
from
    post in Nt_Post
join
    memberdates in (
     from
      p_inner in Nt_Post
     group
      p_inner by p_inner.MemberId into grouped 
     select new {
      MemberId = grouped.Key,
      ActivationDate = grouped.Max(m => m.ActivationDate)
     })
on
    new { post.MemberId, post.ActivationDate }
    equals
    new { memberdates.MemberId, memberdates.ActivationDate }
orderby post.ActivationDate
select post;


q.Dump();

The sql generated is:

SELECT [t0].[Id], [t0].[Title], [t0].[Teaser], [t0].[Text], [t0].[ActivationDate], [t0].[CreatedDate], [t0].[LastModifiedDate], [t0].[IsActive], [t0].[Permalink], [t0].[MemberId], [t0].[HomePageVisibility], [t0].[Image], [t0].[ImageContentType], [t0].[HasNotifiedRTR]
FROM [nt_Post] AS [t0]
INNER JOIN (
    SELECT MAX([t1].[ActivationDate]) AS [value], [t1].[MemberId]
    FROM [nt_Post] AS [t1]
    GROUP BY [t1].[MemberId]
    ) AS [t2] ON ([t0].[MemberId] = [t2].[MemberId]) AND ([t0].[ActivationDate] = [t2].[value])
ORDER BY [t0].[ActivationDate]