MongoDB Driver c# DateTime AddHours Query InvalidOperationException

user_10810229 0
Viewed 213

I have a collection of planning with a startDate and a duration in hours (totalDuration)

public class PlanningDTO
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public ObjectId Id { get; set; }
 
    [BsonElement("startDate")]
    public DateTime StartDate { get; set; }

    [BsonElement("totalDuration")]
    public int TotalDuration { get; set; }
}

I want to get the planning that are included in a date range.

I am using an API in .Net 3.1 with MongoDriver 2.12.3

I have tried things like:

GetPlanningsInDateRange(DateTime startDate, DateTime endDate){ 
var filter = Builders<PlanningDTO>
            .Filter
            .Where(pp => pp.StartDate.AddHours(pp.TotalDuration) >= startDate && pp.StartDate <= endDate);

var planningsInRange = await Db.GetCollection<PlanningDTO>("Plannings").Find(filter).ToListAsync();
return planningsInRange;
            }

or using Mongo.Driver.LINQ

var planningsInRange = await Db.GetCollection<PlanningDTO>("Plannings").AsQueryablew<PlanningDTO>()
.Where(pp => pp.StartDate.AddHours(pp.TotalDuration) >= startDate && pp.StartDate <= endDate).ToListAsync();

but what i get is en error:

System.InvalidOperationException: {document}{startDate}.AddHours(Convert({document}{totalDuration}, Double)) is not supported.

If i do this kind of query without AddHours(), it works fine (no error) but then it is not what i want.

Any idea?

mongodb linq c#
1 Answers

i believe you'd be able to do it with a projection like the following but it would be way inefficient than storing the EndDate in the database from the get go.

ProjectionDefinition<Plan, object> projection = @"
{
    _id: 1,
    startDate: 1,
    totalDuration: 1,
    ENDDATE: { $add: ['$startDate', { $multiply: ['$totalDuration', 3600000] }] }
}";

var filter = Builders<Plan>.Filter.Gte(p => p.StartDate, startDate);
    filter &= Builders<Plan>.Filter.Lte("ENDDATE", endDate);

var result = await collection.Aggregate()
    .Project(projection)
    .As<Plan>()
    .Match(filter)
    .ToListAsync();
user_43684850
Related