如何编写一个 LINQ 查询来将子表过滤到特定时间段并对子表的结果求和？

Question

如果我必须执行以下操作，那么这是在 LINQ 中执行左外连接的最有效方法...

• 按开始日期和结束日期过滤表 2。
• 即使表 2 的过滤未返回任何行，表 1 中的所有行也必须保留。
• 必须对结果进行分组，以便对表 2 中的列进行求和。

例如（示例代码变量名称因专有原因更改），假设我有一个包含两个表的数据库。表 1 列出了带有建筑代码、门 ID 和当前状态（打开或关闭）的门 - 建筑代码和门 ID 是主键。表 2 列出了所有门的事件（事件是打开或关闭）以及时间戳。所以列是建筑代码、门 ID、时间戳、打开、关闭。开盘价和收盘价是整数，相应事件的列中带有 1。建筑代码和门 ID 两个表之间存在外键关系。

对于我的查询，我需要返回所有唯一门的列表以及当前门状态以及选定时间段内所有打开和关闭事件的总和。 即使在选定的时间段内没有发生任何事件，也必须为每个门返回一个条目。

下面是我能想到的最佳 LINQ 代码。它确实有效，但似乎效率很低且难以理解。您将如何使其更有效且更易于理解？

var query = 
    from doors in Context.Doors
    join fevents in
        (
        from events in db.Events
        where events.TimeStamp >= date1 && events.TimeStamp <= date2
        select new { events.BuildingCode, events.DoorID, events.TimeStamp, events.Opening, events.Closing }
        )
    on new { doors.BuildingCode, doors.DoorID } equals { fevents.BuildingCode, fevents.DoorID }
    into g1
    from c in g1.DefaultIfEmpty()
    group c by new
    {
        doors.BuildingCode,
        doors.DoorID,
        doors.DoorStatus
    } into g2
    select new
    {
        BuildingCode = g2.Key.BuildingCode,
        DoorID = g2.Key.DoorID,
        Status = g2.Key.DoorStatus
        NumOpenings = g2.Sum(i => (i == null ? 0 : i.Opening)),
        NumClosings = g2.Sum(i => (i == null ? 0 : i.Closing))
    };

Answer 1

我认为这更容易阅读

var query = 
    from doors in Context.Doors
    from c in db.Events
                .Where(events => doors.BuildingCode == events.BuildingCode)
                .Where(events => doors.DoorID == events.DoorID)
                .Where(events => events.TimeStamp >= date1 && events.TimeStamp <= date2)
                .Select(events => new { events.BuildingCode, events.DoorID, events.TimeStamp, events.Opening, events.Closing })
                .DefaultIfEmpty()
    group c by new
    {
        doors.BuildingCode,
        doors.DoorID,
        doors.DoorStatus
    } into g2
    select new
    {
        BuildingCode = g2.Key.BuildingCode,
        DoorID = g2.Key.DoorID,
        Status = g2.Key.DoorStatus
        NumOpenings = g2.Sum(i => (i == null ? 0 : i.Opening)),
        NumClosings = g2.Sum(i => (i == null ? 0 : i.Closing))
    };

Answer 2

@adducci 的答案帮助我想出了一个稍微不同的解决方案，我认为该解决方案更具可读性，尽管可能效率较低。

var query = 
    from doors in Context.Doors
    from events in doors.Events
                        .Where(i => i.TimeStamp >= date1 && i.TimeStamp <= date2)
                        .DefaultIfEmpty()
    group new { doors, events }
    by doors into g
    select new 
    { 
        BuildingCode = g.Key.BuildingCode,
        DoorID = g.Key.DoorID,
        Status = g.Key.DoorStatus,
        NumOpenings = g.Sum(i => (i.events == null ? 0 : i.events.Opening)),
        NumClosings = g.Sum(i => (i.events == null ? 0 : i.events.Closing))
    };

请注意，按日期过滤的另一种方法是直接在求和函数中使用，如下所示，但这效率低得多，因为所有记录都将从数据库中检索，并且然后在本地进行过滤。

...
//from events in doors.Events
//                    .Where(i => i.TimeStamp >= date1 && i.TimeStamp <= date2)
//                    .DefaultIfEmpty()
from events in doors.Events
                    .DefaultIfEmpty()
...
NumOpenings = g.Sum(i => (i.events == null ? 0 : (i.events.Timestamp >= date1 && i.events.TimeStamp <= date2) ? i.events.Opening : 0)),
NumClosings = g.Sum(i => (i.events == null ? 0 : (i.events.Timestamp >= date1 && i.events.TimeStamp <= date2) ? i.events.Closing : 0))
...