Transform mongoquery to fluent MongoDriver c#

Question

I have one query on mongodb like that:

    db = db.getSiblingDB("his");
db.getCollection("Account").aggregate(
    [
        {
            "$lookup" : {
                "from" : "LinkedEntities",
                "let" : {
                    "account_id" : "$AccountId",
                    "institutionGroup_id" : "$InstitutionGroupId"
                },
                "pipeline" : [
                    {
                        "$match" : {
                            "$expr" : {
                                "$and" : [
                                    {
                                        "$eq" : [
                                            "$InstitutionGroupId",
                                            "$$institutionGroup_id"
                                        ]
                                    },
                                    {
                                        "$eq" : [
                                            "$AccountId",
                                            "$$account_id"
                                        ]
                                    }
                                ]
                            }
                        }
                    },
                    {
                        "$project" : {
                            "AccountId" : NumberInt(0),
                            "_id" : NumberInt(0)
                        }
                    }
                ],
                "as" : "AccountwithLinkedEntities"
            }
        }
    ]

I need to migrate it to mongdb driver with the fluentHelper I try like this but is not matching my mongodb query:

var filter = Builders<Account>.Filter.Eq(a => a.InstitutionGroupId, institutionGroupId)
             & Builders<Account>.Filter.Eq(a => a.AccountId, accountId);

    var accountwithLinkedEntities = await _mongoDbContext.Accounts.Aggregate().Match(filter)
               .Lookup<Account, LinkedEntities, AccountwithLinkedEntities>(_mongoDbContext.LinkedEntities,
                   x => x.AccountId, 
                   y => y.AccountId,
                   x => x.InnerLinkedEntities)
               .ToListAsync();
     return accountwithLinkedEntities;

how can i transform the first query to c# with the fluent syntax.

Best regards

Answer 1

Writing it as answer since it's quite big:

Your example is wrong. You put the $match aggregate stage outside lookup and in general use a different form of Lookup. See below points:

1. You can always provide a raw MQL query (string based) into typed helpers since all driver's helpers (including $lookup) allow implicit creating from string. One example can be found here. Another example can be found here (see Unsupported LINQ or Builder expressions). See also my answer here where I provide a lookup in a raw form.

2. However, your case seems supporting a more c# like approach. You should use this overload that allows passing an aggregate pipeline. To create aggregate pipeline you can use this code snippet:

   var pipeline = new Driver.EmptyPipelineDefinition<..YOUR_TYPE..>()
       .Match(..match condition in typed or string based (raw) forms..)
       .Project(..projection condition in typed or string based (raw) forms..);
   

Pay attention that let argument can be provided only as BsonDocument which means that you can't use any typed form for it. So it will be:

  ...
  foreignCollection: ...,
  let: BsonDocument.Parse(@$"
            {{ 
                ""account_id"" : ""$AccountId"",
                ""institutionGroup_id"" : ""$InstitutionGroupId""
            }}");
  lookupPipeline: pipeline,
  ...
  

You can find example of this approach in the link in my answer here.