[CSHARP-3988] Filtering does not work properly on Time-Series enabled collections Created: 08/Dec/21  Updated: 27/Oct/23  Resolved: 24/Dec/21

Status: Closed
Project: C# Driver
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Unknown
Reporter: Dennis Stuhr Assignee: Robert Stam
Resolution: Gone away Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File image-2021-12-09-14-06-27-296.png     PNG File image-2021-12-09-14-08-14-604.png     PNG File image-2021-12-09-14-21-52-328.png    

 Description   

I'm using the C# driver version 2.14.0 and I am unable to make filtering work properly on time-series collections.

A little about my setup: I'm using .NET 6.0, Visual Studio 2022, my app is running via docker-compose where MongoDB 5.0.4 is used as a datastore via an image definition in docker-compose. It works.

I have created several collections that all have the time-series feature enabled in the same way:

 

var collectionName = Context.GetCollectionName<ARandomClass>(); // gets collection name from attribute defined for class
var filter = new BsonDocument("name", collectionName);
var collectionCursor = Database.ListCollections(new ListCollectionsOptions { Filter = filter });
if (collectionCursor.Any()) return;
Database.CreateCollection(collectionName, new CreateCollectionOptions { TimeSeriesOptions = new TimeSeriesOptions(nameof(ARandomClass.Timestamp), nameof(ARandomClass.SessionId), Optional.Create<TimeSeriesGranularity?>(TimeSeriesGranularity.Minutes)
 });
Collection = Database.GetCollection<ARandomClass>(collectionName);

 

This works. The time series collection is created and I can verify that the collections are created as time series collection in the MongoDb container. All my other methods in my generic repositories also works as expected. I can create, delete and find single records denoted by their id, using Linq.

However, I've created some integration tests that I cannot get to work properly when using date filtering:

 

 

[Fact]
public async void Data_Is_Stored_As_TimeSeries_Data_And_Queryable_In_Time_Buckets()
{
    var ARandomClassRepository = _unitOfWorkFactory.CreateUnitOfWork().Repository<IARandomClassRepository>();
    await ARandomClassRepository.DeleteAll();
    var sessionIds = new [] { 111, 222, 333, 444 };
    var timestampStart = DateTime.UtcNow;
    var dataList = new List<ARandomClass>();
    for (var i = 0; i < 1000; i++)
    {
        dataList.Add(new ARandomClass
        {
            SessionId = sessionIds[RandomNo.Between(0, sessionIds.Length)],
            Timestamp = timestampStart.AddSeconds(i),
            ActivityLevel = RandomNo.Between(1, 100),
            Amplitude = RandomNo.Between(1, 1000),
            RrInterval = RandomNo.Between(1, 10000)
        });
    }
    await ARandomClassRepository.CreateMany(dataList);
    var minuteToGetDataFrom = timestampStart.WithoutSeconds().AddMinutes(1);
    var minuteToGetDataTo = minuteToGetDataFrom.AddMinutes(1);
    var randomClasses = await ARandomClassRepository.Find(x => x.Timestamp >= minuteToGetDataFrom && x.Timestamp <= minuteToGetDataTo).ToList();
    randomClasses.ShouldNotBeNull();
    randomClasses.Count.ShouldBeGreaterThan(0);
    await lifetouchHeartBeatRepository.DeleteAll();
 }

 

This does not work. I have tried a myriad of variations without success. The only way I have gotten it to work, is to create a new repository method to replace the generic Find method (which just takes a Linq expression) and practically spell out the  "native MongoDB" command to get the results:

 

 

public async Task<List<ARandomClass>> GetBetweenDateRanges(DateTime startDate, DateTime endDate)
{
    var filterStage = new StringBuilder()
        .Append("{$match:{$and:[ {$expr: {$gte:")
        .Append("[ \"$Timestamp\",")
        .Append($"ISODate('{startDate.ToString("yyyy-MM-ddTHH:mm:ss.fffZ")}'),")
        .Append("]}}, {$expr:{ $lte:")
        .Append("[ \"$Timestamp\",")
        .Append($"ISODate('{endDate.ToString("yyyy-MM-ddTHH:mm:ss.fffZ")}')")
        .Append("]}} ]}}");
    return await Collection.Aggregate().AppendStage<LifetouchHeartBeat>(filterStage.ToString()).ToListAsync();
}

 

This works!

But, it would be really nice if I could just use regular Linq expressions like I normally do to create basic filters for my data, instead of having to create specialized methods that incorporates native MongoDb commands to get it to work.

 

 

 



 Comments   
Comment by PM Bot [ 24/Dec/21 ]

There hasn't been any recent activity on this ticket, so we're resolving it. Thanks for reaching out! Please feel free to comment on this if you're able to provide more information.

Comment by Robert Stam [ 09/Dec/21 ]

Glad you figured out what was different between your code and my attempt to reproduce.

BSON DateTime values stored in the database don't specify which time zone they are in. Originally the server specified that all BSON DateTime values were in UTC (though recent versions of the server have some support for timezones).

Passing DateTimeKind.Utc to new DateTimeSerializer(DateTimeKind.Utc) only affects deserialization of BSON DateTime values. When serializing .NET DateTime values to BSON DateTime values (to send to the server) they are always converted to UTC.

The recommended best practice is to always use UTC with MongoDB. That means to also use UTC in your data model classes. If you consistently use UTC everywhere that interacts with MongoDB there won't be any local/UTC mismatches.

 

Comment by Dennis Stuhr [ 09/Dec/21 ]

Thanks a lot for the quick feedback Robert!

After looking at your code, it dawned on me that you utilize a different method of setting your time values with. So I tried to replicate it, and lo and behold, it worked!

 

I then inspected the difference in values between "first", "last", "minuteToGetDataFrom" and "minuteToGetDataTo":

As you can see, the variables "minuteToGetDataFrom" and "minuteToGetDataTo" are UTC values, whereas "first" and "last" are local time values (UTC + 1).

But, using UTC values should work, as I have used theBsonClassMap.RegisterClassMap method to configure the mapping of the properties in the class. And unless I'm misunderstanding something, the following piece of code should denote that MongoDb should interpret the "Timestamp" property (which is a DateTime? type) as being UTC:

 

BsonClassMap.RegisterClassMap<LifetouchHeartBeat>(c =>
{
    c.AutoMap();
    c.MapMember(e => e.Timestamp).SetSerializer(new NullableSerializer<DateTime>(new DateTimeSerializer(DateTimeKind.Utc)));
});

 

I have confirmed that the configuration is being used at the time of extracting the data, as you can see in this screenshot:

When I look in the MongoDb collection after the items have been stored into the database, the ISODate values are all in UTC. I would expect that the above mapping should tell MongoDb, that the "Timestamp" property passed to it, is also using UTC, but it doesn't seem to recognize it.

Comment by Robert Stam [ 09/Dec/21 ]

I am unable to reproduce the behavior you are seeing. In order to have the simplest possible scenario I have modified your partial code slightly:

  • remove custom code from your application that is not related to the problem reported
  • rename ARandomClass to SensorData and declare the class
  • use fewer test documents and no random values to make it easy to visualize the desired result

This is the test code I used:

namespace MongoDB.Driver.Tests.Jira
{
    public class CSharp3988Tests
    {
        [Fact]
        public void Find_with_filter_expression_using_timeseries_collection_should_work()
        {
            var client = DriverTestConfiguration.Client;
            var database = client.GetDatabase("test");
            var collection = database.GetCollection<SensorData>("sensordata");
 
            CreateCollection(database, "sensordata");
            CreateTestData(collection);
 
            var startTime = DateTime.Parse("2021-01-01T00:02:00Z");
            var endTime = DateTime.Parse("2021-01-01T00:03:00Z");
            var results = collection.Find(x => x.Timestamp >= startTime && x.Timestamp <= endTime).ToList();
 
            results.Should().NotBeNull();
            results.Count.Should().Be(6); // 3 at 00:02:00 and 3 at 00:03:00
 
            static void CreateCollection(IMongoDatabase database, string collectionName)
            {
                database.DropCollection(collectionName);
                var createCollectionOptions = new CreateCollectionOptions
                {
                    TimeSeriesOptions = new TimeSeriesOptions(
                        timeField: nameof(SensorData.Timestamp),
                        metaField: nameof(SensorData.SensorId),
                        granularity: TimeSeriesGranularity.Minutes)
                };
                database.CreateCollection(collectionName, createCollectionOptions);
            }
 
            static void CreateTestData(IMongoCollection<SensorData> collection)
            {
                // 10 minutes of data for 3 sensors every minute
                var baseTime = DateTime.Parse("2021-01-01T00:00:00Z");
                for (var sensorId = 1; sensorId <= 3; sensorId++)
                for (var minute = 1; minute <= 10; minute++)
                {
                    var timestamp = baseTime.AddMinutes(minute);
                    var value = 100.0 * sensorId + minute;
                    var document = new SensorData { SensorId = sensorId, Timestamp = timestamp, Value = value };
                    collection.InsertOne(document);
                }
            }
        }
 
        private class SensorData
        {
            public int SensorId { get; set; }
            public DateTime Timestamp { get; set; }
            public double Value { get; set; }
        }
    }
}

The test passes, so it doesn't look like there is a problem in the driver.

If you see how my test does not correctly reproduce the problem you are seeing please follow up.

 

Comment by Dennis Stuhr [ 08/Dec/21 ]

I forgot to add the Find function in my repository method for reference:

public virtual async Task<IEnumerable<TEntity>> Find(Expression<Func<TEntity, bool>> predicate)
{
    try
    {
        var filter = Builders<TEntity>.Filter.Where(predicate);
        return (await Collection.FindAsync(filter)).ToList();
     }
     catch (Exception e)
     {
         Logger.LogCritical(e, $"Could not find entity using predicate for entity '{nameof(TEntity)}' in generic repository");
         throw;
     }
}

I've tested this with other Linq expressions and they all seem to work. Just not for filtering on the timestamp / datetime constraint on time-series enabled collections...

Generated at Wed Feb 07 21:46:52 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.