[CSHARP-2788] Compare two fields with where throw exception "Unsupported filter" Created: 13/Oct/19  Updated: 27/Oct/23  Resolved: 14/Oct/19

Status: Closed
Project: C# Driver
Component/s: API, Command Operations
Affects Version/s: 2.9.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Vorobjev Valery Assignee: Dmitry Lukyanov (Inactive)
Resolution: Works as Designed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

I want find data where field1 (DateTime) < field2 (DateTime). This is my query:

 

FilterDefinition<Measurement> filter = fb.And(
 fb.Eq(m => m.DataProvider, new ObjectId(dataProviderId)),
 fb.Where(m => m.RcvTime < m.ObsTime));

В результате я получаю следующую ошибку

Unhandled Exception: System.ArgumentException: Unsupported filter: ({document}{RcvTime} < {document}{ObsTime}).
   at MongoDB.Driver.Linq.Translators.PredicateTranslator.Translate(Expression node)
   at MongoDB.Driver.Linq.Translators.PredicateTranslator.Translate(Expression node, IBsonSerializerRegistry serializerRegistry)
   at MongoDB.Driver.AndFilterDefinition`1.Render(IBsonSerializer`1 documentSerializer, IBsonSerializerRegistry serializerRegistry)
   at MongoDB.Driver.MongoCollectionImpl`1.CreateFindOperation[TProjection](FilterDefinition`1 filter, FindOptions`2 options)
   at MongoDB.Driver.MongoCollectionImpl`1.FindAsync[TProjection](IClientSessionHandle session, FilterDefinition`1 filter, FindOptions`2 options, CancellationToken cancellationToken)
   at MongoDB.Driver.MongoCollectionImpl`1.UsingImplicitSessionAsync[TResult](Func`2 funcAsync, CancellationToken cancellationToken)
   at Csdn.TimeCheck.Program.Main(String[] args) in /home/valera/RiderProjects/Csdn.TimeCheck/Csdn.TimeCheck/Program.cs:line 30
   at Csdn.TimeCheck.Program.<Main>(String[] args)



 Comments   
Comment by Christos Sakellarios [ 01/May/20 ]

Is there any plan to support the $expr operaton without BsonDocument???

Comment by Dmitry Lukyanov (Inactive) [ 14/Oct/19 ]

Hello boniface,
currently, we don't support comparison for operations where both operands are not constant in `Linq` expressions or builders.
But you can reach the same target if you use `BsonDocumentFilterDefinition` together with the `$expr` operator. See my repro below:

        public class Measurement
        {
            public ObjectId Id { get; set; }
            public DateTime RcvTime { get; set; }
            public DateTime ObsTime { get; set; }
            public string Value { get; set; }
        }
 
        [Fact]
        public void POC()
        {
            var coll = database.GetCollection<Measurement>("Measurement");
            var now = DateTime.Now;
            coll.InsertOne(new Measurement() { ObsTime = now, RcvTime = now, Value = "Will be matched" });
            coll.InsertOne(new Measurement() { ObsTime = now, RcvTime = DateTime.Now.AddHours(1), Value = "Won't be matched" });
 
            FilterDefinition<Measurement> filter = new BsonDocumentFilterDefinition<Measurement>(
                new BsonDocument(
                    "$expr", new BsonDocument(
                        "$eq", new BsonArray
                        {
                            "$ObsTime", "$RcvTime"
                        })));
            var result = coll.FindSync(filter).ToList();
            // result will contain only the "Will be matched" record
        }

NOTE: the $expr operator is supported for the server versions higher or equal to `3.6`. See the doc: https://docs.mongodb.com/manual/reference/operator/query/expr/#examples

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