c# - 如何协调 LINQ 中的第一条和最后一条记录

Question

我的 SQL 数据库中有一个表，用于跟踪员工的上类时间和下类时间。一个典型的记录看起来像这样

Id           Device               DateTime                    EmployeeId    
-------------------------------------------------------------------------
1            InReader             2013/05/05 08:00:00         1
2            InReader             2013/05/05 08:00:05         1
3            InReader             2013/05/05 08:01:00         2
4            InReader             2013/05/05 08:02:00         3
5            InReader             2013/05/05 08:03:00         4
6            OutReader            2013/05/05 17:00:00         1
7            OutReader            2013/05/05 17:05:05         2
8            OutReader            2013/05/05 17:05:10         2
9            OutReader            2013/05/05 17:10:00         3
10           OutReader            2013/05/05 17:30:00         4

Id只是一个自增列
设备是他们在其上刷员工卡以 checkin / checkout 的设备
DateTime 是他们刷员工卡的时间

我想知道，在一天结束时，当我生成报告时，我如何才能协调他们的入时和出时，这样输出可能如下所示:

Employee Id            In time                    Out time
-----------------------------------------------------------------------
1                      2013/05/05 08:00:00        2013/05/05 17:00:00
2                      2013/05/05 08:01:00        2013/05/05 17:05:10
3                      2013/05/05 08:02:00        2013/05/05 17:10:00
4                      2013/05/05 08:03:00        2013/05/05 17:30:00

注意事项:
- 请注意，员工 1 有 2 条“InReader”记录，我想获取较早的记录
- 员工 2 有 2 条“OutReader”的记录，我只想获取他的最新记录

如何使用 LINQ 协调 IN 和 OUT 记录？ (如果在 LINQ 中不可能，则为 TSQL)

Answer 1

我向您提出了这个查询，并在 LinqPad 中进行了测试。我会给你完整的代码，这样你就可以自己尝试了。

查询本身:

tracks.GroupBy(x => x.EmployeeId)
      .Select(x => new 
            {
                EmployeeId = x.Key,
                InTime = x.FirstOrDefault(y => y.Device.Equals("InReader")).DateTime,
                OutTime = x.LastOrDefault(y => y.Device.Equals("OutReader")).DateTime
            })

完整代码示例:

void Main()
{
    var tracks = new[]
    {
        new Track{Id = 1, Device = "InReader", DateTime = new DateTime(2013,5,5,8,0,0), EmployeeId = 1},
        new Track{Id = 2, Device = "InReader", DateTime = new DateTime(2013,5,5,8,0,5), EmployeeId = 1},
        new Track{Id = 3, Device = "InReader", DateTime = new DateTime(2013,5,5,8,1,0), EmployeeId = 2},
        new Track{Id = 4, Device = "InReader", DateTime = new DateTime(2013,5,5,8,2,0), EmployeeId = 3},
        new Track{Id = 5, Device = "InReader", DateTime = new DateTime(2013,5,5,8,3,0), EmployeeId = 4},

        new Track{Id = 6, Device = "OutReader", DateTime = new DateTime(2013,5,5,17,0,0), EmployeeId = 1},
        new Track{Id = 7, Device = "OutReader", DateTime = new DateTime(2013,5,5,17,5,5), EmployeeId = 2},
        new Track{Id = 8, Device = "OutReader", DateTime = new DateTime(2013,5,5,17,5,10), EmployeeId = 2},
        new Track{Id = 9, Device = "OutReader", DateTime = new DateTime(2013,5,5,17,10,0), EmployeeId = 3},
        new Track{Id = 10, Device = "OutReader", DateTime = new DateTime(2013,5,5,17,30,0), EmployeeId = 4},
    };

        // the Query
        tracks
    .GroupBy(x => x.EmployeeId)
    .Select(x => new 
        {
            EmployeeId = x.Key,
            InTime = x.FirstOrDefault(y => y.Device.Equals("InReader")).DateTime,
            OutTime = x.LastOrDefault(y => y.Device.Equals("OutReader")).DateTime
        })
}

public class Track
{
    public int Id { get; set; }

    public string Device { get; set; }

    public DateTime DateTime { get; set; }

    public int EmployeeId { get; set; }
}