Mongodb C# driver, simple filtering and then group result by property

Question

I am trying to filter mongo results by some properties and then group them by property Name. Any help would be greatly appreciated.

This what I have in my mongo collection:

[
  {
    Name: "test1",
    Type: "typeX"
    Date: "10-10-10",
    Age: 10,
    Value: 20
  },
  {
    Name: "test1",
    Type: "typeX"
    Date: "10-10-10",
    Age: 1000,
    Value: 2000
  },
  {
    Name: "test2",
    Type: "typeX"
    Date: "10-10-10",
    Age: 20,
    Value: 30
  },
  {
    Name: "test3",
    Type: "typeX"
    Date: "10-10-10",
    Age: 30,
    Value: 40
  }
]

Filter step: if Date = "10-10-10" and Type = "typeX" and Name $in ["test1", "test2"]. This step works correcly and I am able to filter the results.

[
  {
    Name: "test1",
    Type: "typeX"
    Date: "10-10-10",
    Age: 10,
    Value: 20
  },
  {
    Name: "test1",
    Type: "typeX"
    Date: "10-10-10",
    Age: 1000,
    Value: 2000
  },
  {
    Name: "test2",
    Type: "typeX"
    Date: "10-10-10",
    Age: 20,
    Value: 30
  }
]

Group the filtered result by Name: this step does not work.

{
  "test1": [
    {
      Name: "test1",
      Type: "typeX"
      Date: "10-10-10",
      Age: 10,
      Value: 20
    },
    {
      Name: "test1",
      Type: "typeX"
      Date: "10-10-10",
      Age: 1000,
      Value: 2000
    }
  ],
  "test2": [
    {
      Name: "test2",
      Type: "typeX"
      Date: "10-10-10",
      Age: 20,
      Value: 30
    }
  ]
}

The following is my code so far. I really want to avoid using lambda expression because I have seen substantial performance loss whenever I tried it.

public Dictionary<string, List<DummyClass>> FilterAndGroupBy(string type, string date, List<string> names)
{
    // Get the collection
    var collection = GetDummyCollection();

    var filter = new BsonDocument
    {
        {"Date", date},
        {"Type", type}
    };

    // Create the filter
    filter.Add("Name", new BsonDocument
    {
        {"$in", new BsonArray(indexNames)}
    });

    // Create the group, this does not work.
    var group = new BsonDocument
    {
        {"_id",
            new BsonDocument {
                {"Name", "$Name"},
                {"Result", new BsonDocument("$push", new BsonArray()) }
            }
        }
    };

    // Query the group with the filter
    var resultslambda = collection.Aggregate().Match(filter).Group(group).ToList();

    return new Dictionary<string, List<DummyClass>>();
}

Answer 1

Here is a valid group expression for your data:

{
    $group :
        {
            _id : "$Name"
            Result: { $push: {
                        Name: "$Name",
                        Type: "$Type",
                        Date: "$Date",
                        Age: "$Age",
                        Value: "$Value",
                        }
                    }
        },
}

As you see, you have mistakenly put $push accumulator to the expression for _id field.

Here is the correct statement for creating the groupping:

var group = new BsonDocument
{
    {
        "_id", new BsonDocument {
            {"Name", "$Name"},
        }
    },
    {
        "Result", new BsonDocument("$push", new BsonDocument
            {
                { "Name", "$Name" },
                { "Type", "$Type" },
                { "Date", "$Date" },
                { "Age", "$Age" },
                { "Value", "$Value" },
            }
        )
    }
};

I also have doubts regarding your statement about performance loss when using lambda expressions. In both cases the MongoDB driver will send the same queries to the server. So I don't know what could be the source of performance degradation in your case. On the other hand, using BsonDocument for building the queries as you currently do, could lead to the errors that are not easy to identify. Your question is the proof of it. So I suggest rechecking the performance metrics. If you still have degradation evidence, it's probably worth to post separate SO question about this issue.

Just in case, here is an analogue of the groupping based on lambdas:

var resultslambda = collection.Aggregate().Match(filter).Group(
    x => x.Name,
    g => new {
        Result = g.Select(x => new
        {
            x.Name,
            x.Type,
            x.Date,
            x.Age,
            x.Value
        })
    }
).ToList();