How can I filter child collection comparing datetime property with another one plus timespan value?

Question

For example this

var items = await database.GetCollection<CollectionItem>("collection").AsQueryable()
    .Where(r => r.SubCollection.Any(i =>
        i.DateTimeProp < i.OtherDateTimeProp + TimeSpan.FromMinutes(59))
    .ToListAsync();

gives me mongo driver error, unsupported filter:

({}{DateTimeProp} < {}{OtherDateTimeProp} + 00:59:00)

Is there any way to do this? Thanks!

Answer 1

datetime range support in mongo driver is quite weak. i use a custom class for storing ticks and isodate in seperate properties in mongodb so i have the ability to do range queries like so:

  var result = collection.AsQueryable()
                         .SelectMany(t =>
                             t.SubCollection,
                            (t, s) => new
                             {
                                isMatch = s.DateTimeProp.Ticks < s.OtherDateTimeProp.Ticks + TimeSpan.FromMinutes(59).Ticks,
                                t.ID
                              })
                         .Where(x => x.isMatch)
                         .Select(x => x.ID)
                         .ToList();

custom date class:

    public class Date
    {
        private long ticks = 0;
        private DateTime date = new DateTime();

        public long Ticks
        {
            get => ticks;
            set { date = new DateTime(value); ticks = value; }
        }

        public DateTime DateTime
        {
            get => date;
            set { date = value; ticks = value.Ticks; }
        }

        public static implicit operator Date(DateTime dt)
        {
            return new Date { DateTime = dt };
        }

        public static implicit operator DateTime(Date dt)
        {
            if (dt == null) throw new NullReferenceException("The [Date] instance is Null!");
            return new DateTime(dt.Ticks);
        }
    }

here's a full test program:

using MongoDB.Entities;
using MongoDB.Entities.Core;
using System;
using System.Linq;

namespace StackOverflow
{
    public class Test : Entity
    {
        public string Name { get; set; }
        public SubTest[] SubCollection { get; set; }
    }

    public class SubTest
    {
        public Date DateTimeProp { get; set; }
        public Date OtherDateTimeProp { get; set; }
    }

    public class Date
    {
        private long ticks = 0;
        private DateTime date = new DateTime();

        public long Ticks
        {
            get => ticks;
            set { date = new DateTime(value); ticks = value; }
        }

        public DateTime DateTime
        {
            get => date;
            set { date = value; ticks = value.Ticks; }
        }

        public static implicit operator Date(DateTime dt)
        {
            return new Date { DateTime = dt };
        }

        public static implicit operator DateTime(Date dt)
        {
            if (dt == null) throw new NullReferenceException("The [Date] instance is Null!");
            return new DateTime(dt.Ticks);
        }
    }

    public class Program
    {
        private static void Main(string[] args)
        {
            new DB("test", "localhost");

            (new[] {
                new Test{
                    Name = "one",
                    SubCollection = new[]{
                        new SubTest{ OtherDateTimeProp = DateTime.UtcNow, DateTimeProp = DateTime.UtcNow.AddMinutes(50)}, // should match
                        new SubTest{ OtherDateTimeProp = DateTime.UtcNow, DateTimeProp = DateTime.UtcNow.AddMinutes(60)}
                    }
                },
                new Test{
                    Name = "two",
                    SubCollection = new[]{
                        new SubTest{ OtherDateTimeProp = DateTime.UtcNow, DateTimeProp = DateTime.UtcNow.AddMinutes(60)},
                        new SubTest{ OtherDateTimeProp = DateTime.UtcNow, DateTimeProp = DateTime.UtcNow.AddMinutes(60)}
                    }
                }
            }).Save();

            var result = DB.Queryable<Test>() // use collection.AsQueryable() for official driver
                           .SelectMany(t =>
                                t.SubCollection,
                               (t, s) => new
                               {
                                   isMatch = s.DateTimeProp.Ticks < s.OtherDateTimeProp.Ticks + TimeSpan.FromMinutes(59).Ticks,
                                   t.ID,
                                   t.Name
                               })
                           .Where(x => x.isMatch)
                           .Select(x => x.ID)
                           .ToList();
        }
    }
}