MongoDriver c# Filter by month Name from a Date Field-CodePudding

Requirement : Need to filter the mongo documents by month (Data is stored in mongo as date time)

Code used : var expenseMonthFilter = Builders<MyDocument>.Filter.Eq(i => i.ExpenseDate.Month, 12);

Exception Getting :Unable to determine the serialization information for i => i.ExpenseDate.Month.

CodePudding user response：

When selecting all the document of a month, the condition needs to retrieve all documents that have a ExpenseDate that is greather than or equal to the start date of the month and less than the start date of the following month, like so:

var startOfMonth = new DateTime(selectedYear, selectedMonth, 1, 0, 0, 0, DateTimeKind.Utc);
var startOfNextMonth = startOfMonth.AddMonths(1);
var bldr = Builders<MyDocument>.Filter;
var expenseMonthFilter = bldr.And(
    bldr.Gte(x => x.ExpenseDate, startOfMonth), 
    bldr.Lt(x => x.ExpenseDate, startOfNextMonth));

Please not that in the sample above, selectedYear stands for the year, selectedMonth for the month that the data should be retrieved for.

CodePudding user response：

the default mongo date is just a number not a date, you have to apply external transformations to convert it to a date, to do this you need to use an aggregation to transform the data before you query it

one option is to use a Grouping to preselect the data ie

PipelineDefinition<BsonDocument, BsonDocument> pipeline = new BsonDocument[]
{
    new BsonDocument("$group", new BsonDocument()
            .Add("_id", new BsonDocument()
                    .Add("month", new BsonDocument()
                            .Add("$month", "$yourDateField")
                    )
            )
            .Add("data", new BsonDocument()
                    .Add("$addToSet", "$$ROOT")
            )), 
    new BsonDocument("$match", new BsonDocument()
            .Add("_id.month", 6.0))
};

if you just want to group the data by month then you wouldn't need the match stage each group would be in the form of

_id.nonth:int,
data:<collection of base docs that are in that month>

another option would be to reproject each document before matching

PipelineDefinition<BsonDocument, BsonDocument> pipeline = new BsonDocument[]
{
    new BsonDocument("$project", new BsonDocument()
            .Add("month", new BsonDocument()
                    .Add("$month", "$yourDateField")
            )
            .Add("root", "$$ROOT")), 
    new BsonDocument("$match", new BsonDocument()
            .Add("month", 6.0))
};

either of these would then be called by

var cursor = await collection.AggregateAsync(pipeline, options)

if you want to include the years that would be a simple call to the $year function like i have the $month function