如何在T-SQL或Linq to SQL的相关实体列表中选择其类型的最新实体?

我的桌子上摆满了动作。每个操作都是由某个用户在某个日期时间完成的。所以它有4个字段:id、userid、actionid和actiondate。

起初,我只是报告了最近十大类似的行动:

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

这很简单,而且很有效。但是这份报告没有我想象的那么有用。这是因为有些用户可能会连续执行10个操作,并霸占整个前10个列表。因此,我想报告最近十大活跃用户中每一个最近采取的行动。

从另一个问题来看,我已经走了大部分的路。看起来我需要“组”功能。如果我这样做:

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

在LinqPad运行,我得到一个 IOrderedQueryable<IGrouping<Int32,Action>> 结果集,每个用户一组。但是,它显示了每个用户所采取的所有操作,结果集是分层的,我希望它是平面的。

所以如果我的动作表是这样的话

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

我希望查询按顺序返回记录2、5和4。对于每个用户,这将显示该用户最近采取的操作,并且所有报告的操作都是有序的,最新的操作位于顶部。所以我想看看:

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

编辑:

我也很难用T-SQL来表达这一点。此查询获取用户及其最后操作日期:

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

但是,如何访问附加到找到max actiondate的记录的其他信息?

edit2:我一直在重构,现在的操作叫做read,但其他的都是一样的。我采用了弗兰克的解决方案,具体如下:

(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);

结果如下:

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

如果有人知道一些简单的事情(对体育运动来说),我想知道,但我认为这可能足够好了。