MongoDB事件表高效查询：指定日期前后状态获取方案咨询

Alright, let's break this down step by step—first fixing your immediate exception, then building an efficient bulk query solution for your two state lookup needs.

1. Fixing the Original SelectMany Exception #

Your current code throws an error because you're accessing DateOff.Value even when DateOff is null (like in rows where the event is still active). Here's the corrected in-memory approach that skips creating a ShortEvent for null DateOff:

return _Db.Query
    .SelectMany(a => 
    {
        var eventsList = new List<ShortEvent>
        {
            // Always add the event creation (State = true)
            new ShortEvent { Date = a.DateOn, State = true, EventId = a.Id }
        };
        
        // Only add the event end (State = false) if DateOff exists
        if (a.DateOff.HasValue)
        {
            eventsList.Add(new ShortEvent { Date = a.DateOff.Value, State = false, EventId = a.Id });
        }
        
        return eventsList;
    })
    .ToList();

This avoids the InvalidOperationException from accessing Value on a null nullable type. However, pulling all event data into memory isn't ideal for bulk queries (180 events) with large historical data—so let's move to a database-side aggregation solution.

2. Efficient Bulk Queries with MongoDB Aggregation #

Instead of processing data in your application, use MongoDB's aggregation pipeline to compute results directly in the database. This reduces network transfer and leverages database indexes for speed.

First, Add Indexes (Critical for Performance) #

Create a compound index to speed up filtering and sorting for your queries:

db.yourCollection.createIndex({ Id: 1, DateOn: 1, DateOff: 1 })

Query 1: Last State Before Date X (Bulk) #

This pipeline returns the final state of each target event before or on your specified date X:

// Replace with your 180 event IDs
var targetEventIds = new List<int> { 12, 13, 14 };
// Your target date (adjust to your actual date/time, use UTC for consistency)
DateTime dateX = new DateTime(2022, 12, 1, 11, 03, 00, DateTimeKind.Utc);

var lastStatePipeline = new BsonDocument[]
{
    // Filter to only your target events
    new BsonDocument("$match", new BsonDocument("Id", new BsonDocument("$in", new BsonArray(targetEventIds)))),
    // Create an array of state change events (handle null DateOff)
    new BsonDocument("$addFields", new BsonDocument(
        "stateChanges", new BsonDocument("$cond", new BsonArray
        {
            new BsonDocument("$ne", new BsonArray { "$DateOff", BsonNull.Value }),
            new BsonArray
            {
                new BsonDocument("Date", "$DateOn"), "State", true
            },
            new BsonArray
            {
                new BsonDocument("Date", "$DateOn"), "State", true
            }
        })
    )),
    // Expand the state changes into individual documents
    new BsonDocument("$unwind", "$stateChanges"),
    // Keep only events on or before date X
    new BsonDocument("$match", new BsonDocument("stateChanges.Date", new BsonDocument("$lte", dateX))),
    // Group by event ID to find the latest state change
    new BsonDocument("$group", new BsonDocument
    {
        "_id", "$Id",
        "latestDate", new BsonDocument("$max", "$stateChanges.Date"),
        "latestState", new BsonDocument("$first", new BsonDocument("$cond", new BsonArray
        {
            new BsonDocument("$eq", new BsonArray { "$stateChanges.Date", "$latestDate" }),
            "$stateChanges.State",
            BsonNull.Value
        }))
    }),
    // Format the output to your desired structure
    new BsonDocument("$project", new BsonDocument
    {
        "_id", 0,
        "EventId", "$_id",
        "LastStateDate", "$latestDate",
        "LastState", "$latestState"
    })
};

// Execute the aggregation and map to a C# class
var lastStateResults = _Db.Query
    .Aggregate<BsonDocument>(lastStatePipeline)
    .Select(b => new 
    {
        EventId = b["EventId"].AsInt32,
        LastStateDate = b["LastStateDate"].ToUniversalTime(),
        LastState = b["LastState"].AsBoolean
    })
    .ToList();

Query 2: First State After Date Y (Bulk) #

This pipeline returns the first state change for each target event on or after your specified date Y:

// Your target date (adjust to your actual date/time, use UTC for consistency)
DateTime dateY = new DateTime(2022, 12, 1, 11, 07, 00, DateTimeKind.Utc);

var firstStatePipeline = new BsonDocument[]
{
    new BsonDocument("$match", new BsonDocument("Id", new BsonDocument("$in", new BsonArray(targetEventIds)))),
    new BsonDocument("$addFields", new BsonDocument(
        "stateChanges", new BsonDocument("$cond", new BsonArray
        {
            new BsonDocument("$ne", new BsonArray { "$DateOff", BsonNull.Value }),
            new BsonArray
            {
                new BsonDocument("Date", "$DateOn"), "State", true
            },
            new BsonArray
            {
                new BsonDocument("Date", "$DateOn"), "State", true
            }
        })
    )),
    new BsonDocument("$unwind", "$stateChanges"),
    // Keep only events on or after date Y
    new BsonDocument("$match", new BsonDocument("stateChanges.Date", new BsonDocument("$gte", dateY))),
    // Group by event ID to find the earliest state change
    new BsonDocument("$group", new BsonDocument
    {
        "_id", "$Id",
        "earliestDate", new BsonDocument("$min", "$stateChanges.Date"),
        "earliestState", new BsonDocument("$first", new BsonDocument("$cond", new BsonArray
        {
            new BsonDocument("$eq", new BsonArray { "$stateChanges.Date", "$earliestDate" }),
            "$stateChanges.State",
            BsonNull.Value
        }))
    }),
    // Format the output to your desired structure
    new BsonDocument("$project", new BsonDocument
    {
        "_id", 0,
        "EventId", "$_id",
        "FirstStateDate", "$earliestDate",
        "FirstState", "$earliestState"
    })
};

var firstStateResults = _Db.Query
    .Aggregate<BsonDocument>(firstStatePipeline)
    .Select(b => new 
    {
        EventId = b["EventId"].AsInt32,
        FirstStateDate = b["FirstStateDate"].ToUniversalTime(),
        FirstState = b["FirstState"].AsBoolean
    })
    .ToList();

Key Notes #

• Aggregation vs In-Memory: Aggregation is far more efficient for large datasets because it processes data in the database, reducing the amount of data sent to your application.
• Handling Active Events: For events where DateOff is null, the pipeline only includes the creation event (State = true), which correctly reflects that the event is still active.
• Bulk Support: The $in operator in the $match stage lets you query up to 180 events in a single pipeline call.

内容的提问来源于stack exchange，提问作者Liron