在LINQ中使用聚合查询进行自联接

I have a model class in C# tied to a database with entity framework. In my model I record values for users, and the time when the entry is recorded. What I want is to return a set of entries that represents only the most recent record for a given UserID.

我将C#中的模型类绑定到具有实体框架的数据库。在我的模型中,我记录了用户的值以及记录条目的时间。我想要的是返回一组条目,这些条目仅代表给定UserID的最新记录。

I can accomplish this in SQL with an aggregate query (max date, grouped by user ID) and a self join to match those parameters up with the right record.

我可以在SQL中使用聚合查询(最大日期,按用户ID分组)和自联接来完成此操作,以使这些参数与正确的记录匹配。

DECLARE @UserValues
    TABLE
    (
        id INT IDENTITY(1,1) PRIMARY KEY, 
        UserID INT, 
        Value CHAR, 
        RecordedAt DATETIME
    )

INSERT INTO @UserValues (UserID, Value, RecordedAt) SELECT 3, 'a', '2014-11-06';
INSERT INTO @UserValues (UserID, Value, RecordedAt) SELECT 3, 'b', '2014-11-08';
INSERT INTO @UserValues (UserID, Value, RecordedAt) SELECT 3, 'c', '2014-11-04';
INSERT INTO @UserValues (UserID, Value, RecordedAt) SELECT 5, 'f', '2014-11-03';
INSERT INTO @UserValues (UserID, Value, RecordedAt) SELECT 5, 'h', '2014-11-23';
INSERT INTO @UserValues (UserID, Value, RecordedAt) SELECT 5, 'w', '2014-11-07';

select straight_data.*
from
(
    select UserID          as UserID,
           MAX(RecordedAt) as RecordedAt
      from @UserValues
     group by UserID
) as max_date
join @UserValues as straight_data
  on max_date.RecordedAt = straight_data.RecordedAt
 and max_date.UserID     = straight_data.UserID

The result is only the most recent record for each user:

结果只是每个用户的最新记录:

id  UserID  Value   RecordedAt
5   5       h       2014-11-23 00:00:00.000
2   3       b       2014-11-08 00:00:00.000

See it in action here: http://sqlfiddle.com/#!6/7c4dc/1

请点击此处查看:http://sqlfiddle.com/#!6/7c4dc/1

So the question is, how do I perform the below operation in LINQ on something like,

所以问题是,如何在LINQ中执行以下操作,例如,

ICollection<UserValues> MyUserValues;

1 个解决方案

#1

You can do this in linq with these steps :

您可以使用以下步骤在linq中执行此操作:

Group by userId

按userId分组

Order each group by RecordedAt (descending)

按RecordedAt(降序)对每个组进行排序

Select First from each "group".

从每个“组”中选择First。

var dataByUserAndMaxDate = 
    MyUserValues.GroupBy(m => m.UserId)
                .Select(m => m.OrderByDescending(x => x.RecordedAt).First());

There's also a MaxBy extension method if you use MoreLinq (for linq to objects only)

如果你使用MoreLinq还有一个MaxBy扩展方法(仅用于linq到对象)

#1