Aggregating query results by C# MongoDB driver

Question

There are two collections in a MongoDB database. One collection is called Department per the example below:

{
    "_id": "Dept1",
    "Description": "Department 1",
    "ApplicationName": "Payroll",
    "Version": 1
}

{
    "_id": "Dept2",
    "Description": "Department 2",
    "ApplicationName": "Payroll",
    "Version": 1
}

{
    "_id": "Dept3",
    "Description": "Department 3",
    "ApplicationName": "Payroll",
    "Version": 1
}

The other collection is called UserAssociation and its data looks like the following example:

{
    "_id": "Associate1",
    "DepartmentIds": ["Dept1","Dept2"]
}

{
    "_id": "Associate2",
    "DepartmentIds": ["Dept2","Dept3"]
}

{
    "_id": "Associate3",
    "DepartmentIds": ["Dept1", "Dept2","Dept3"]
}

The C# models of these two documents are:

public class Department
{
    public string Id { get; set; }
    public string Description { get; set; }
    public string ApplicationName { get; set; }
    public int Version { get; set; }
}

public class Associate
{
    public string Id { get; set; }
    public string[] DepartmentIds { get; set; }
}

I'd like to get the following model populated by aggregation or "join" (anything that can help better) at the end of the day:

public class DepartmentAssociation
{
    public string AssociateId { get; set; }
    public Department[] Departments { get; set; }
}

How can I achieve this goal in C#?

you need to aggregate the collections into a single document, ideally in mongo this should be how you store the data as its not a relational database — MikeT
– MikeT, Commented May 18, 2021 at 13:18
Due to some reasons we cannot keep a merger of these two documents in another document. So, a code-level aggregation is required. — Arash
– Arash, Commented May 18, 2021 at 13:22

MikeT · Accepted Answer · 2021-05-18 14:20:39Z

you need to aggregate the data into a single document, ideally this should be at the point of storage, however if you can't do this mongo does include a lookup function

the syntax for it are

$lookup:
{
    from: "<collection to join>",
    localField: "<field from the input documents>",
    foreignField: "<field from the documents of the 'from' collection>",
    as: "<output array field>"
}

or if you compose it in c#

PipelineDefinition<BsonDocument, BsonDocument> pipeline = new BsonDocument[]
{
    new BsonDocument("$lookup", new BsonDocument(){
            {"from", "<collection to join>"},
            {"localField", "<field from the input documents>"},
            {"foreignField", "<field from the documents of the 'from' collection>"},
            {"as", "<output array field>"}
     }
};

using (var cursor = await collection.AggregateAsync(pipeline, options))
{
    while (await cursor.MoveNextAsync())
    {
        var batch = cursor.Current;
        foreach (BsonDocument document in batch)
        {
            Console.WriteLine(document.ToJson());
        }
    }
}

in more specific detail

PipelineDefinition<BsonDocument, BsonDocument> pipeline = new BsonDocument[]
{
    new BsonDocument("$lookup", new BsonDocument(){
            {"from", "Department"},
            {"localField", "DepartmentIds"},
            {"foreignField", "_id"},
            {"as", "Departments"}
     }
};

using (var cursor = await collection.AggregateAsync<DepartmentAssociation>(pipeline, options))
{
    while (await cursor.MoveNextAsync())
    {
        var batch = cursor.Current;
        foreach (DepartmentAssociation document in batch)
        {
            ...
        }
    }
}

How does it fit in the model classes or collections that I shared in the question? I am pretty much novice and I cannot map your answer to the model classes or collection names. Thanks.
you would call the Aggregate or AggregateAsync overload that include the class mapping ie Aggregate<DepartmentAssociation> or AggregateAsync<DepartmentAssociation>
How is this particular area populated? new BsonDocument("$lookup", new BsonDocument(){ {"from", "<collection to join>"}, {"localField", "<field from the input documents>"}, {"foreignField", "<field from the documents of the 'from' collection>"}, {"as", "<output array field>"} }
replace <collection to join> with the name of the collection you are joining to, <field from the input documents> with the name of the field that has the foreign key in, <field from the documents of the 'from' collection> with the key field in the collection you are joining to and <output array field> with the name of the field you want the results in

Collectives™ on Stack Overflow

Aggregating query results by C# MongoDB driver

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related