How do I select the most recent entity of its type in a related entity list in T-SQL or Linq to Sql?

Question

I have a table full of Actions. Each Action is done by a certain User at a certain DateTime. So it has 4 fields: Id, UserId, ActionId, and ActionDate.

At first, I was just reporting the top 10 most recent Actions like this:

(from a in db.Action
orderby a.ActionDate descending
select a).Take(10);

That is simple and it works. But the report is less useful than I thought. This is because some user might take 10 actions in a row and hog the whole top 10 list. So I would like to report the single most recent action taken for each of the top 10 most recently active users.

From another question on SO, I have gotten myself most of the way there. It looks like I need the "group" feature. If I do this:

from a in db.Action
orderby a.ActionDate descending
group a by a.UserId into g
select g;

And run it in linqpad, I get an IOrderedQueryable<IGrouping<Int32,Action>> result set with one group for each user. However, it is showing ALL the actions taken by each user and the result set is hierarchical and I would like it to be flat.

So if my Action table looks like this

Id UserId ActionId ActionDate
1  1      1        2010/01/09
2  1      63       2010/01/10
3  2      1        2010/01/03
4  2      7        2010/01/06
5  3      11       2010/01/07

I want the query to return records 2, 5, and 4, in that order. This shows me, for each user, the most recent action taken by that user, and all reported actions are in order, with the most recent at the top. So I would like to see:

Id UserId ActionId ActionDate
2  1      63       2010/01/10
5  3      11       2010/01/07
4  2      7        2010/01/06

EDIT:

I am having a hard time expressing this in T-SQL, as well. This query gets me the users and their last action date:

select
    a.UserId,
    max(a.ActionDate) as LastAction
from
    Action as a
group by
    a.UserId
order by
    LastAction desc

But how do I access the other information that is attached to the record where the max ActionDate was found?

EDIT2: I have been refactoring and Action is now called Read, but everything else is the same. I have adopted Frank's solution and it is as follows:

(from u in db.User
join r in db.Read on u.Id equals r.UserId into allRead
where allRead.Count() > 0
let lastRead = allRead.OrderByDescending(r => r.ReadDate).First()
orderby lastRead.ReadDate descending
select new ReadSummary
{
    Id = u.Id,
    UserId = u.Id,
    UserNameFirstLast = u.NameFirstLast,
    ProductId = lastRead.ProductId,
    ProductName = lastRead.Product.Name,
    SegmentCode = lastRead.SegmentCode,
    SectionCode = lastRead.SectionCode,
    ReadDate = lastRead.ReadDate
}).Take(10);

This turns into the following:

exec sp_executesql N'SELECT TOP (10) [t12].[Id], [t12].[ExternalId], [t12].[FirstName], [t12].[LastName], [t12].[Email], [t12].[DateCreated], [t12].[DateLastModified], [t12].[DateLastLogin], [t12].[value] AS [ProductId], [t12].[value2] AS [ProductName], [t12].[value3] AS [SegmentCode], [t12].[value4] AS [SectionCode], [t12].[value5] AS [ReadDate2]
FROM (
    SELECT [t0].[Id], [t0].[ExternalId], [t0].[FirstName], [t0].[LastName], [t0].[Email], [t0].[DateCreated], [t0].[DateLastModified], [t0].[DateLastLogin], (
        SELECT [t2].[ProductId]
        FROM (
            SELECT TOP (1) [t1].[ProductId]
            FROM [dbo].[Read] AS [t1]
            WHERE [t0].[Id] = [t1].[UserId]
            ORDER BY [t1].[ReadDate] DESC
            ) AS [t2]
        ) AS [value], (
        SELECT [t5].[Name]
        FROM (
            SELECT TOP (1) [t3].[ProductId]
            FROM [dbo].[Read] AS [t3]
            WHERE [t0].[Id] = [t3].[UserId]
            ORDER BY [t3].[ReadDate] DESC
            ) AS [t4]
        INNER JOIN [dbo].[Product] AS [t5] ON [t5].[Id] = [t4].[ProductId]
        ) AS [value2], (
        SELECT [t7].[SegmentCode]
        FROM (
            SELECT TOP (1) [t6].[SegmentCode]
            FROM [dbo].[Read] AS [t6]
            WHERE [t0].[Id] = [t6].[UserId]
            ORDER BY [t6].[ReadDate] DESC
            ) AS [t7]
        ) AS [value3], (
        SELECT [t9].[SectionCode]
        FROM (
            SELECT TOP (1) [t8].[SectionCode]
            FROM [dbo].[Read] AS [t8]
            WHERE [t0].[Id] = [t8].[UserId]
            ORDER BY [t8].[ReadDate] DESC
            ) AS [t9]
        ) AS [value4], (
        SELECT [t11].[ReadDate]
        FROM (
            SELECT TOP (1) [t10].[ReadDate]
            FROM [dbo].[Read] AS [t10]
            WHERE [t0].[Id] = [t10].[UserId]
            ORDER BY [t10].[ReadDate] DESC
            ) AS [t11]
        ) AS [value5]
    FROM [dbo].[User] AS [t0]
    ) AS [t12]
WHERE ((
    SELECT COUNT(*)
    FROM [dbo].[Read] AS [t13]
    WHERE [t12].[Id] = [t13].[UserId]
    )) > @p0
ORDER BY (
    SELECT [t15].[ReadDate]
    FROM (
        SELECT TOP (1) [t14].[ReadDate]
        FROM [dbo].[Read] AS [t14]
        WHERE [t12].[Id] = [t14].[UserId]
        ORDER BY [t14].[ReadDate] DESC
        ) AS [t15]
    ) DESC',N'@p0 int',@p0=0

If anyone knows something simpler (for the sport of it) I would like to know, but I think this is probably good enough.

Answer 1

Probably have some errors in this, but I think you want to do a join into a collection, then use 'let' to choose a member of that collection:

(
from u in db.Users
join a in db.Actions on u.UserID equals a.UserID into allActions
where allActions.Count() > 0
let firstAction = allActions.OrderByDescending(a => a.ActionDate).First()
orderby firstAction.ActionDate descending
select (u,firstAction)
).Take(10)