CODEWITHSUNDEEP
c#.netmongodbmongodb-querymongodb-.net-driver
bertenvdb
bertenvdb

Reputation: 97

Get flattened array of documents embedded in array with filter on document property in MongoDB

I have a MongoDb collection of documents each containing an array of embedded documents. I'd like to retrieve a flattened list of those embedded documents of which the date property is before a given date.

Let's assume we have the following classes:

public class RootItem
{
    public string Id { get; set; }
    public string Name{ get; set; }
    public string PictureUrl { get; set; }
    public List<Reservation> Reservations { get; set; }
}

public class Reservation
{
    public string Name { get; set; }
    public DateTime Date{ get; set; }
    public int NrOfSeats { get; set; }
}

So the collection would look something like this:

{
  "_id": "5be2bb2fdfd6174938518af2",
  "name": "John Doe",
  "pictureUrl": "http://example.com/abc.jpg",
  "reservations": [
    {
      "table": "abc",
      "date": "1/1/2019",
      "nrOfSeats": 5
    },
    {
      "name": "xyz",
      "date": "7/1/2019",
      "nrOfSeats": 5
    }
  ]
}

I've read the docs, I've read alot here at SO but the closest I got this far was this:

var reservations = Collection
            .Aggregate()
            .Unwind<RootItem, Reservation>(x => x.Reservations)
            .ToList()
            .FindAll(r => r.Date > thresholdDate);

Running this code I got this error:

System.FormatException: 'Element '_id' does not match any field or property of class 'Reservation'

So I add a projection. If I only exclude the _id field I get:

'Element 'Created' does not match any field or property of class 'Reservation'

So I manually include the other fields (should be unnecessary):

var reservations = Collection
            .Aggregate()
            .Unwind<RootItem, Reservation>(x => x.Reservations)
            .Project<Reservation>(Builders<Reservation>.Projection
                 .Exclude("_id")
                 .Include(r => r.Name)
                 .Include(r => r.Date)
                 .Include(r => r.NrOfSeats))
            .ToList()
            .FindAll(r => r.Date > thresholdDate);

But now I get a List with NrOfSeats and Name set to null and Date to 1/1/0001.

How can I retrieve a flattened list of all reservations in my collection of which the date property is before/smaller than a given date?

Upvotes: 1

Views: 743

Answers (1)

JavierFromMadrid
JavierFromMadrid

Reputation: 621

I think if you use

collection.AsQueryable().SelectMany(s => s.Reservations).Where(r => r.Date > thresholdDate).ToList();

It should return what you expect.

Upvotes: 1

Related Questions