MongoDB: query date by year C#

Question

Having such a document:

var document = new BsonDocument
{
    { "address" , new BsonDocument
        {
            { "street", "2 Avenue" },
            { "zipcode", "10075" },
            { "building", "1480" },
            { "coord", new BsonArray { 73.9557413, 40.7720266 } }
        }
    },
    { "borough", "Manhattan" },
    { "cuisine", "Italian" },
    { "grades", new BsonArray
        {
            new BsonDocument
            {
                { "date", new DateTime(2014, 10, 1, 0, 0, 0, DateTimeKind.Utc) },
                { "grade", "A" },
                { "score", 11 }
            },
            new BsonDocument
            {
                { "date", new DateTime(2014, 1, 6, 0, 0, 0, DateTimeKind.Utc) },
                { "grade", "B" },
                { "score", 17 }
            }
        }
    },
    { "name", "Vella" },
    { "restaurant_id", "41704620" }
};

How would I query for grades.date.year = 2016?

Was trying:

var filter = Builders<BsonDocument>.Filter.Eq("grades.date.year", 2016);
var result = await collection.Find(filter).ToListAsync();

But I guess dot notation only works on the json doc, not the objects? Scoured the internet, but couldn't find a clean example.

EDIT: C# classes?

class Address
{
    public string street { get; set; }
    public string zipcode { get; set; }
    public string building { get; set; }
    public IEnumerable<double> coord { get; set; }
}

class Grade
{
    public DateTime date { get; set; }
    public string grade { get; set; }
    public int score { get; set; }
}

class TestX
{
    public ObjectId _id { get; set; }
    public Address address { get; set; }
    public string borough { get; set; }
    public string cuisine { get; set; }
    public IEnumerable<Grade> grades { get; set; }
    public string name { get; set; }
    public string restaurant_id { get; set; }
}

profesor79 · Accepted Answer · 2016-06-22T22:22:13.633

2

This requires aggregation framework. If you could post c# class then will update my answer to strongly typed c# , but now decided to project year inside project phase.

    public static void Main()
    {
        var client = new MongoClient("mongodb://localhost:27017");
        var database = client.GetDatabase("test");

        var collection = database.GetCollection<BsonDocument>("hammer");

        var project =
            BsonDocument.Parse(
                "{_id: 1,address: 1,borough: 1,cuisine: 1,grades: 1,name: 1,restaurant_id: 1,year: {$year: '$grades.date'}}");

        var aggregationDocument =
            collection.Aggregate()
                .Unwind("grades")
                .Project(project)
                .Match(BsonDocument.Parse("{'year' : {$in : [2013, 2015]}}"))
                .ToList();

        foreach (var result in aggregationDocument)
        {
            Console.WriteLine(result.ToString());
        }

        Console.ReadLine();
    }

edit

var aggregationDocument =
                collection.Aggregate<TestX>()
                    .Unwind<TestX>(x=>x.grades)
                    .Match(BsonDocument.Parse(
   "{$and:[{'grades.date':{$gte: ISODate('2012-01-01')}},{'grades.date':{$lt: ISODate('2013-01-01')}}]}"))
                    .ToList();

edited Jun 22 '16 at 22:22

answered Jun 22 '16 at 12:14

profesor79

9,213
3
31
52

Thanks for the answer, but this seems awfully complicated. It can't take 20 lines of code just to query by date??? – SledgeHammer Jun 22 '16 at 14:58
@SledgeHammer please post your c# class then using typed objects will be shorter (as written in answer) – profesor79 Jun 22 '16 at 15:04
Not sure if the class is is setup correctly (still learning), but edited original post. – SledgeHammer Jun 22 '16 at 17:58
Ok, fixed up the classes, but it says Year is not supported?? Trying var q = collection.AsQueryable().Where(x => x.grades.Any(g => g.date.Year == 2016)).ToList(); – SledgeHammer Jun 22 '16 at 18:11
@SledgeHammer yes - this is not supported, one question do you need to get only result passing year criteria, or array can have other results as well? – profesor79 Jun 22 '16 at 18:53
I might match other attributes... just testing out POCs now. – SledgeHammer Jun 22 '16 at 19:08
Is that any better / worse then doing: List lst = collection.AsQueryable().Where(x => x.grades.Any(g => g.date >= new DateTime(2016, 1, 1) && g.date < new DateTime(2017, 1, 1))).ToList(); in terms of performance? – SledgeHammer Jun 22 '16 at 22:23
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115349/discussion-between-profesor79-and-sledgehammer). – profesor79 Jun 22 '16 at 22:25
@profesor79 could you take a look at https://stackoverflow.com/questions/57584337 I'm having a hard time with my models and aggregations! thanks. – gdp Aug 21 '19 at 10:02

score -1 · Answer 2 · edited May 23 '17 at 12:16

-1

Couldn't another approach be to check if date is within that year.

I.e for 2016 using $gte: 2016-01-01 00:00:00 and $lt: 2017-01-01 00:00:00

Find objects between two dates MongoDB

There are probably functions for them in c# like Gte and Lt. There are probably some easy way to combine the filters like with an & or similar.

edited May 23 '17 at 12:16

Community

1
1

answered Jun 22 '16 at 01:05

mollwe

2,185
2
18
17

1

this will not filter inner array documents – profesor79 Jun 22 '16 at 12:04

MongoDB: query date by year C#

2 Answers2

edit