[CSHARP-2670] Sorting on DateTimeOffset field fails when default (Array) serialization is used Created: 24/Jul/19  Updated: 27/Oct/23  Resolved: 24/Sep/19

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

Type: Bug Priority: Major - P3
Reporter: Michał Gajek Assignee: Robert Stam
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File image-2019-07-24-11-55-48-090.png    

 Description   

When sorting on DateTimeOffset field (ascending), the sort fails when default serialization (Array) is used. 

 

Please find the tests attached below

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
using FluentAssertions;
using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Driver;
using MongoDB.Driver.Core.Events;
using Xunit;
using Xunit.Abstractions;namespace MongoDbDateTimeOffset
{
    public interface IFoo
    {
        Guid Id { get; set; }
        DateTimeOffset DateTimeOffset { get; set; }    }    public class FooString : IFoo
    {
        public Guid Id { get; set; }
        [BsonRepresentation(BsonType.String)]
        public DateTimeOffset DateTimeOffset { get; set; }
    }    public class FooArray : IFoo
    {
        public Guid Id { get; set; }
        [BsonRepresentation(BsonType.Array)]
        public DateTimeOffset DateTimeOffset { get; set; }
    }    public class FooDocument : IFoo
    {
        public Guid Id { get; set; }
        [BsonRepresentation(BsonType.Document)]
        public DateTimeOffset DateTimeOffset { get; set; }
    }    public class MongoDbTests
    {
        private readonly ITestOutputHelper _helper;
        private readonly MongoClient client;        public MongoDbTests(ITestOutputHelper helper)
        {
            _helper = helper;            client = new MongoClient(new MongoClientSettings()
            {
                Server = new MongoServerAddress("localhost"),
                Credential = MongoCredential.CreateCredential("admin", "mongoadmin", "secret"),
                ClusterConfigurator = cb =>
                {
                    cb.Subscribe<CommandStartedEvent>(e =>
                        _helper.WriteLine($"{e.CommandName}: {e.Command.ToJson()}"));
                }
            });
        }        private async Task Can_Sort_By_DateTimeOffset<T>(OrderDirection direction) where T : IFoo
        {
            var collection = client.GetDatabase("foobar").GetCollection<T>(typeof(T).Name.ToLowerInvariant() + "_" + direction.ToString());
            collection.DeleteMany(FilterDefinition<T>.Empty);            var foos = new[]
                {
                    new DateTimeOffset(2019, 07, 20, 12, 00, 00, TimeSpan.FromHours(3)),
                    new DateTimeOffset(2019, 07, 21, 12, 00, 00, TimeSpan.FromHours(3)),
                    new DateTimeOffset(2019, 07, 22, 12, 00, 00, TimeSpan.FromHours(3))
                }
                .Select(x =>
                {
                    var foo = Activator.CreateInstance(typeof(T)) as IFoo;
                    foo.DateTimeOffset = x;
                    foo.Id = Guid.NewGuid();
                    return foo;
                })
                .Cast<T>()
                .OrderBy(x => x.Id)
                .ToArray();            await collection.InsertManyAsync(foos);            // run
            var query = collection.AsQueryable().OrderBy(x => x.DateTimeOffset, direction)
                .Select(x => x.DateTimeOffset);
            var result = query.ToArray();            var expected = foos.AsQueryable().OrderBy(x => x.DateTimeOffset, direction).Select(x => x.DateTimeOffset)
                .ToArray();            // assert
            result.Should().Equal(expected);
        }
        [Theory]
        [InlineData(OrderDirection.Ascending)]
        [InlineData(OrderDirection.Descending)]
        public async Task Can_Sort_AsString(OrderDirection direction) =>
            await Can_Sort_By_DateTimeOffset<FooString>(direction);        [Theory]
        [InlineData(OrderDirection.Ascending)]
        [InlineData(OrderDirection.Descending)]
        public async Task Can_Sort_AsArray(OrderDirection direction) =>
            await Can_Sort_By_DateTimeOffset<FooArray>(direction);        [Theory]
        [InlineData(OrderDirection.Ascending)]
        [InlineData(OrderDirection.Descending)]
        public async Task Can_Sort_AsDocument(OrderDirection direction) =>
            await Can_Sort_By_DateTimeOffset<FooDocument>(direction);    }    public enum OrderDirection
    {
        Ascending,
        Descending
    };
    public static class OrderByExtensions
    {
        public static IQueryable<T> OrderBy<T>(this IQueryable<T> queryable, Expression<Func<T, object>> keySelector, OrderDirection direction)
        {
            switch (direction)
            {
                case OrderDirection.Ascending:
                    return queryable.OrderBy(keySelector);                case OrderDirection.Descending:
                    return queryable.OrderByDescending(keySelector);                default:
                    throw new ArgumentOutOfRangeException(nameof(direction), direction, null);
            }
        }
    }}

 



 Comments   
Comment by Robert Stam [ 24/Sep/19 ]

The server documents how it implements sort order here:

https://docs.mongodb.com/manual/reference/bson-type-comparison-order/#bson-types-comparison-order

Note the section on arrays, where it states:

> With arrays, a less-than comparison or an ascending sort compares the smallest element of arrays, and a greater-than comparison or a descending sort compares the largest element of the arrays.

In the sample data in this test the smallest element of the arrays is always 180.

Comment by Robert Stam [ 24/Sep/19 ]

Closing as Works as Designed since this seems to be server related and not a driver issue.

Comment by Robert Stam [ 24/Sep/19 ]

This appears to be related to the fact that the server is not sorting as expected when sorting on an array field.

The collection contents during this test are:

MongoDB Enterprise > db.fooarray_Ascending.find()
{ "_id" : BinData(3,"6baHQyaMxkSk04r0Oc7vVA=="), "DateTimeOffset" : [ NumberLong("636993936000000000"), 180 ] }
{ "_id" : BinData(3,"k8mCX2wrS0ipgTIaecCfkQ=="), "DateTimeOffset" : [ NumberLong("636992208000000000"), 180 ] }
{ "_id" : BinData(3,"eMctohTJDkmzFV67pADDww=="), "DateTimeOffset" : [ NumberLong("636993072000000000"), 180 ] }
MongoDB Enterprise >

The LINQ query you are using translates (in part) to the following aggregation framework pipeline:

MongoDB Enterprise > var pipeline = [{ $sort : { "DateTimeOffset" : 1 } }]
MongoDB Enterprise > db.fooarray_Ascending.aggregate(pipeline)
{ "_id" : BinData(3,"6baHQyaMxkSk04r0Oc7vVA=="), "DateTimeOffset" : [ NumberLong("636993936000000000"), 180 ] }
{ "_id" : BinData(3,"k8mCX2wrS0ipgTIaecCfkQ=="), "DateTimeOffset" : [ NumberLong("636992208000000000"), 180 ] }
{ "_id" : BinData(3,"eMctohTJDkmzFV67pADDww=="), "DateTimeOffset" : [ NumberLong("636993072000000000"), 180 ] }
MongoDB Enterprise >

Note that the server has NOT sorted the documents as you might have expected.

The bottom line is that I don't think we can successfully sort `DateTimeOffset` data when the representation on the server is an array.

 

 

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