Tutorial :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  


Solution: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;  


Solution: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]  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »