MongoDb 3.4 C# Driver - How to create equivalent aggregate query with C# driver

Question

I need some help with converting a MongoDB query into a C# query. This is my Schema:

public class Planner  
{
    public ObjectId Id { get; set; }
    public string PlannerName { get; set; }
    public string EditorName { get; set; }
    public DateTime DateModified { get; set; }
    public List<PlannerDayModel> PlannerDays { get; set; }
}

public class PlannerDayModel 
{
    public int WeekDay { get; set; }
    public string WeekDayStr { get; set; }
    public int Month { get; set; }
    public int Year { get; set; }
    public int MonthDay { get; set; }
    public DateTime TimeStamp { get; set; }
    public bool IsWeekend { get; set; }
    public bool IsHoliday { get; set; }
    public PlannerFreeTextModel FreeTextBox1 { get; set; }
    public PlannerFreeTextModel FreeTextBox2 { get; set; }
    public List<PlannerEventModel> Events { get; set; }
}

public class PlannerEventModel
{
    public int EventRowId { get; set; }
    public string EventName { get; set; }
    public bool IsSeries { get; set; }
    public int? Episode { get; set; }
    public int? Season { get; set; }
    public bool? IsCustom{ get; set; } 
}

public class PlannerFreeTextModel
{
    public int EventRowId { get; set; }
    public string Text { get; set; }
}

I am trying to get all Planner docs where a nested collection called Events has an event with the event name of x.

This MongoDB query works just fine:

db.Planner.aggregate([
  { "$match": { "planners.events.eventName": "X" } },
  { "$project": 
  {"dateModified":1, "editorName":1,"plannerName":1,
      "planners": {
      "$filter": {
        "input": {
          "$map": {
            "input": "$planners",
            "as": "planner",
            "in": {
              "weekDay": "$$planner.weekDay",
              "weekDayStr":"$$planner.weekDay",
              "events": {
                "$filter": {
                  "input": "$$planner.events",
                  "as": "event",
                  "cond": {
                    "$eq":  ["$$event.eventName", "X" ]
                  }
                }
              }
            }
          }
        },
        "as": "planner",
        "cond": { "$ne": [ "$$planner.events", []]}
      },
    }
  }}
])

But I just cant find the equivalent C# driver query to work.

I tried this one, and it threw the error "Unable to determine the serialization information for the expression"

var projection = Builders<Planner>.Projection
    .Include(x => x.PlannerDays
        .Find(p => p.Events.FirstOrDefault(e => e.EventName == eventName) != null))
    .Include(x => x.DateModified)
    .Include(x => x.EditorName)
    .Include(x => x.Id)
    .Include(x => x.PlannerName);

var res = collection
    .Aggregate()
    .Match(Builders<Planner>.Filter.Eq("planners.events.eventName", eventName))
    .Project<Planner>(projection)
    .ToList();

Answer 1

I think you are making it complicated. First of all you can check your query by doing writing ToString() at the end of your c# query. Eg:

collection.Aggregate()
    .Match(Builders<Planner>.Filter.Eq("planners.events.eventName", eventName))
    .Project<Planner>(projection)
    .ToString();

Second thing lets get back to your question, so you want all the Planners with a condition. You can do this. First your filter would be like:

var filter = Builders<BsonDocument>.Filter.Eq("Planner.PlannerDayModel.PlannerEventModel.eventName", "X");

Now comes your aggregate query:

 var aggregate = collection.Aggregate(new AggregateOptions { AllowDiskUse = true })
                    .Unwind("Planner")
                    .Unwind("Planner.PlannerDayModel")
                    .Unwind("Planner.PlannerDayModel.PlannerEventModel")
                    .Unwind("Planner.PlannerDayModel.PlannerEventModel.eventName")
                    .Match(filter)
                    ;

Now this will be your c# query. You can check your schema by doing:

aggregate.ToString();

Now lets execute the query.

List<JObject> objList = new List<JObject>();
 foreach (var agg in aggregate.ToList())
                {
                    JObject obj = new JObject();
                    var str = agg.ToString();
                    objList.Add(obj);
                }

You'll get all the data of planner in objList. Apart from this you can use project according to your requirement.

Please feel free to correct me.

Edit: Adding project

var pro = new BsonDocument {
                    {"PlannerName", true },
                    {"EditorName", true },
                    {"DateModified", true },
                    {"PlannerDays", true }
                };

Now aggregate query would look like this:

var aggregate = collection.Aggregate(new AggregateOptions { AllowDiskUse = true })
                        .Unwind("Planner")
                        .Unwind("Planner.PlannerDayModel")
                        .Unwind("Planner.PlannerDayModel.PlannerEventModel")
                        .Unwind("Planner.PlannerDayModel.PlannerEventModel.eventName")
                        .Match(filter)
                        .Project(pro)
                        ;

Hope it helps. :)