How to use "aggregate" with "project" in MongoDB query in C#?

Question

Environment:

MongoDB.Driver (2.8.1);

Microsoft.NETCore.App (3.0.0-preview7-27912-14);

Language: C#

I have the following storage structure in MongoDB, in a Colletion named: "Revendas":

...
{
    "_id" : ObjectId("5cd0825f92225e4f1179f3b8"),
    "Codigo" : NumberLong(0),
    "Nome" : "Revenda com dois clientes",
    "CPF" : "string",
    "CNPJ" : "string",        
    "Clientes" : [ 
        {
            "Codigo" : "1",
            "Nome" : "Primeiro cliente da revenda 5cd0825f92225e4f1179f3b8",
            "CPF" : "string",
            "CNPJ" : "string",
            "Tokens" : [ 
                {                        
                    "Token" : "bd2b0f0734b3977ebfd56b6965fbdf9bff17f1e6",
                    "Geracao" : ISODate("2019-05-03T17:27:30.678-03:00"),
                    "Revogacao" : ISODate("2019-05-03T17:28:58.858-03:00")
                }
            ]
        }, 
        {
            /*This is the block I need to return*/
            "Codigo" : "2",
            "Nome" : "Segundo cliente da revenda 5cd0825f92225e4f1179f3b8",
            "CPF" : "string",
            "CNPJ" : "string",
            "Tokens" : [ 
                {
                    /* This is the field where I will do the research */
                    "Token" : "671bcaf806405e5d55419746a1b320cc729558fb",
                    "Geracao" : ISODate("2019-05-06T09:29:07.928-03:00"),
                    "Revogacao" : ISODate("2022-05-06T09:29:07.928-03:00")
                }
            ]
        }
    ]
}
...

I would like to search the client token collection and return only the client who answers the query. I was able to execute the query directly in mongodb:

db.Revendas.aggregate([
    { $unwind :'$Clientes'},
    { $match : {'Clientes.Tokens.Token': '671bcaf806405e5d55419746a1b320cc729558fb' }},
    { 
        $project : {
            Codigo: "$Clientes.Codigo", 
            Nome : '$Clientes.Nome', 
            CNPJ : '$Clientes.CNPJ',
            CPF: '$Clientes.CPF',
            Tokens : '$Clientes.Tokens'       
        } 
    }
])

The previous query returns the data I need:

{
    "_id" : ObjectId("5cd0825f92225e4f1179f3b8"),
    "Codigo" : "2",
    "Nome" : "Segundo cliente da revenda 5cd0825f92225e4f1179f3b8",
    "CNPJ" : "string",
    "CPF" : "string",
    "Tokens" : [ 
        {
            "Token" : "671bcaf806405e5d55419746a1b320cc729558fb",
            "Geracao" : ISODate("2019-05-06T09:29:07.928-03:00"),
            "Revogacao" : ISODate("2022-05-06T09:29:07.928-03:00")
        }
    ]
}

The question is, how do you translate this query to the C# mongodb driver?

My attempt was:

string tokenpesquisa = "";

var agg = Database.GetCollection<RevendaCliente>("{'Clientes.Tokens.Token': '" + tokenpesquisa + "'}").Aggregate();

var project = agg.Project(o => new {
    Codigo = o.Codigo,
    Nome = o.Nome,
    CNPJ = o.CNPJ,
    CPF = o.CPF,
    Tokens = o.Tokens
});

var result = project.ToListAsync().Result;

But the data is not returned as expected.

---

The following is the code snippet suggested by user @mickl that solved my problem:

CollRevendas = Database.GetCollection<Revenda>("Revendas");

var query = CollRevendas.Aggregate()
 .Unwind("Clientes")
 .Match(new BsonDocument() { { "Clientes.Tokens.Token", "" + tokenpesquisa + "" } })
 .Project<RevendaCliente>(new BsonDocument()
     {
        { "_id", 0 },
        { "Codigo", "$Clientes.Codigo" },
        { "Nome", "$Clientes.Nome" },
        { "CNPJ", "$Clientes.CNPJ" },
        { "CPF", "$Clientes.CPF" },
        { "Tokens", "$Clientes.Tokens" }
     });

var doc = query.First();

return doc;

Answer 1

Generic method GetCollection<T> takes a collection name as it's first argument. Simplest approach here is to operate on BsonDocument type which represents MongoDB document and can be later deserialized into your type:

var col = database.GetCollection<BsonDocument>("Revendas");

var query =  col.Aggregate()
                .Unwind("Clientes")
                .Match(new BsonDocument(){ { "Clientes.Tokens.Token", "671bcaf806405e5d55419746a1b320cc729558fb" } })
                .Project(new BsonDocument()
                    {
                        { "Codigo", "$Clientes.Codigo" },
                        { "Nome", "$Clientes.Nome" },
                        { "CNPJ", "$Clientes.CNPJ" },
                        { "CPF", "$Clientes.CPF" },
                        { "Tokens", "$Clientes.Tokens" }
                    });

var doc = query.First();

You can also cast your aggregation result by introducing a type that represents your result:

public class Model
{
    public ObjectId Id { get; set; }
    public string Codigo { get; set; }
    public string Nome { get; set; }
    public string CNPJ { get; set; }
    public string CPF { get; set; }
    public TokenModel[] Tokens { get; set; }
}

public class TokenModel
{
    public string Token { get; set; }
    public DateTime Geracao { get; set; }
    public DateTime Revogacao { get; set; }
}

and running generic version of Project:

.Project<Model>(new BsonDocument()
    {
        { "Codigo", "$Clientes.Codigo" },
        { "Nome", "$Clientes.Nome" },
        { "CNPJ", "$Clientes.CNPJ" },
        { "CPF", "$Clientes.CPF" },
        { "Tokens", "$Clientes.Tokens" }
    });