MongoDb C# Lookup to map value from an other collection

Question

I have to collection, let's say ColA and ColB:

public sealed class ColA : IDatabaseObject
{
  [BsonId]
  public Guid Id { get; set; }

  [BsonElement("colb_id")]
  public Guid ColB_Id { get; set; }
}

public sealed class ColB : IDatabaseObject
{
  [BsonId]
  public Guid Id { get; set; }

  [BsonElement("title")]
  public string Title { get; set; }
}

I need to get in a projection Proj the result of ColA and get the Title of ColB through the ColB_Id in ColA, but I'm not able to do it, there is a way without doing it in 2 separate query ?

public sealed class Proj
{
  public Guid Id { get; set; }
  public Guid Colb_Id { get; set; }
  public string OtherTitleName { get; set; }
}

this.db.GetCollection<ColA>()
  .Aggregate()
  .Lookup<ColA, ColB, Proj>(
    this.db.GetCollection<ColB>(),
    a => a.ColB_Id,
    b => b.Id,
    // I need something like...
    a => new Proj {
      Id = a.Id,
      ColB_Id = a.ColB_Id,
      OtherTitleName = b.Title
    }
  )

Is the lookup is the solution or I really need to do it in two steps ?

Answer 1

var pipeline = new[]
{
    // Step 1: Lookup to join ColA and ColB
    new BsonDocument("$lookup",
        new BsonDocument
        {
            { "from", "ColB" },     // The foreign collection name (ColB)
            { "localField", "colb_id" },  // Field in ColA to match
            { "foreignField", "_id" },   // Field in ColB to match
            { "as", "colBData" }   // Alias for the joined data
        }
    ),
    
    // Step 2: Project the result as needed
    new BsonDocument("$project",
        new BsonDocument
        {
            { "Id", "$_id" }, // Include the _id from ColA
            { "Colb_Id", "$colb_id" }, // Include the colb_id from ColA
            { "OtherTitleName", "$colBData.title" } // Include the title from ColB
        }
    )
};

var result = this.db.GetCollection<ColA>()
    .Aggregate<Proj>(pipeline)
    .ToList();