Add an aggregate pipeline stage changing to another model or type in MongoDB C# driver

Question

How to add an aggregate pipeline stage that will change the collection model output that the aggregation is being run on?

X/Y question: How to aggregate lookup a document referenced as ObjectId in another document and returns a nested document within a single query, without adding a redundant field in the collection's model?

Simple models:

public class Foo
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    [JsonPropertyName("id")]
    public string? Id { get; set; } = null!;

    [BsonElement("name")]
    [JsonPropertyName("name")]
    public string? Name { get; set; } = null!;
}

public class Bar
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    [JsonPropertyName("id")]
    public string? Id { get; set; } = null!;

    [BsonElement("name")]
    [JsonPropertyName("name")]
    public string? Name { get; set; } = null!;

    [BsonElement("foo")]
    [JsonPropertyName("foo")]
    public Foo? Foo { get; set; } = null!;
}

Since I want to store Foo in Bar as a reference, but is also able to get a populated object back:

public class BarDb
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string? Id { get; set; } = null!;

    [BsonElement("name")]
    public string? Name { get; set; } = null!;

    [BsonRepresentation(BsonType.ObjectId)]
    [BsonElement("foo")]
    public string? FooId { get; set; } = null!;
}

public class BarDTO
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    [JsonPropertyName("id")]
    public string? Id { get; set; } = null!;

    [BsonElement("name")]
    [JsonPropertyName("name")]
    public string? Name { get; set; } = null!;

    [BsonRepresentation(BsonType.ObjectId)]
    [BsonElement("foo")]
    [JsonPropertyName("foo")]
    public Foo? Foo { get; set; } = null!;
}

A simple fluent lookup aggregation

private IAggregateFluent<BarDTO> GetDefaultPipeline()
{
    return barDbCollection
        .Aggregate()
        .Lookup<BarDb, Foo, BarDTO>(
            fooCollection,
            localField => localField.FooId,
            foreignField => foreignField.Id,
            asField => asField.Foo
        )
        .Unwind(
            field => field.Foo,
            new AggregateUnwindOptions<BarDTO>
            {
                PreserveNullAndEmptyArrays = true,
            }
        );
}

MongoDB.Driver.Linq.ExpressionNotSupportedException: Expression not supported: asField.Foo, fair enough.

I tried to write aggregation with plain old BsonDocument:

public class BarDTO
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    [JsonPropertyName("id")]
    public string? Id { get; set; } = null!;

    [BsonElement("name")]
    [JsonPropertyName("name")]
    public string? Name { get; set; } = null!;

    [BsonElement("foo_object")] // let it accept an additional field from lookup
    [JsonPropertyName("foo")]
    public Foo? Foo { get; set; } = null!;
}

private IAggregateFluent<BarDTO> GetDefaultPipeline()
{
    var lookupStage = new BsonDocument(
        "$lookup",
        new BsonDocument
        {
            { "from", "foo" },
            { "localField", "foo" },
            { "foreignField", "_id" },
            { "as", "foo_object" },
        }
    );
    var unwindStage = new BsonDocument(
        "$unwind",
        new BsonDocument
        {
            { "path", "$foo_object" },
            { "preserveNullAndEmptyArrays", true },
        }
    );
    return barDbCollection
        .Aggregate()
        .AppendStage<BarDTO>(lookupStage) // attempt to change the output model
        .AppendStage<BarDTO>(unwindStage);
}

Element 'foo_object' does not match any field or property of class BarDb, so it's still stuck to the barDbCollection's model.

If I add an additional field foo_object to the BarDb model, it will work:

public class BarDb
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    [JsonPropertyName("id")]
    public string? Id { get; set; } = null!;

    [BsonElement("name")]
    [JsonPropertyName("name")]
    public string? Name { get; set; } = null!;

    [BsonRepresentation(BsonType.ObjectId)]
    [BsonElement("foo")]
    [JsonIgnore]
    public string? FooId { get; set; } = null!;

    [BsonElement("foo_object")] // let it accept an additional field from lookup
    [JsonPropertyName("foo")]
    public Foo? Foo { get; set; } = null!;
}

private IAggregateFluent<BarDTO> GetDefaultPipeline()
{
    // ...

    return barDbCollection
        .Aggregate()
        .AppendStage<BarDb>(lookupStage) // don't have to change the model anymore
        .AppendStage<BarDb>(unwindStage);
}

That works, but when I insert a document with barDbCollection, the Bar document will always have an redundant foo_object field with the value null:

public async Task CreateFooInBar(string fooName, string barName)
{
    var foo = new Foo { Name = fooName };
    await fooCollection.InsertOneAsync(foo);
    await barDbCollection.InsertOneAsync(
        new BarDb { Name = barName, FooId = foo.Id }
    );
}

Result: the Bar document will always have an redundant foo_object field with the value null:

{
    "_id": {
        "$oid": "673def40112d1449afea9ddc"
    },
    "name": "I am Foo!"
}

{
    "_id": {
        "$oid": "673def40112d1449afea9ddd"
    },
    "name": "I am Bar!",
    "foo": {
        "$oid": "673def3f112d1449afea9ddc"
    },
    "foo_object": null
}

There are other ways that I know:

• Get the Db model first, then do more queries to populate the DTO model: this is what I am trying to avoid, so I used lookup aggregation to avoid multiple queries.

• Read the query result as BsonDocument: this requires manually assigning fields, which is also what I am trying to avoid.

• Use a ReadToDTO model with it's own collection, separately from the CRUD Db model:

public class BarReadToDTO
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    [JsonPropertyName("id")]
    public string? Id { get; set; } = null!;

    [BsonElement("name")]
    [JsonPropertyName("name")]
    public string? Name { get; set; } = null!;

    [BsonRepresentation(BsonType.ObjectId)]
    [BsonElement("foo")]
    [JsonIgnore] // do not include this in the deserialized json string
    public string? FooId { get; set; } = null!;

    [BsonElement("foo_object")] // let it accept an additional field from lookup
    [JsonPropertyName("foo")]
    public Foo? Foo { get; set; } = null!;
}

But there are complications: this is a school project, and I am required to have all repositories inherit from a generic CRUD repository, this is how it is done:

public class BaseDbModel
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    [JsonPropertyName("id")]
    public string? Id { get; set; } = null!;
}

public interface ICrudRepository<T> : IMongoDbRepository<T>
    where T : BaseDbModel
{
    Task<IEnumerable<T>> FindAllAsync();
    Task<T?> FindByIdAsync(string id);
    Task InsertAsync(T entity);
    Task ReplaceAsync(string id, T entity);
    Task DeleteAsync(string id);
}

By having another Read model, it may complicate the generic CRUD repository too much.

Packages installed:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="MongoDB.Driver" Version="2.29.0" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.4.0" />
  </ItemGroup>

</Project>

Answer 1

I tested your code and started with the first version of the DTOs. In order to solve this, you need to take two steps:

The first one is really quick: remove the BsonRepresenation attribute from the Foo property in BarDb. This removes the UnsupportedException.

In addition, you need to create a temporary DTO that takes an array of Foo objects instead of just a single one (though the query by id will one retrieve one). I call the temporary DTO class TempBarDTO in the following sample:

[BsonIgnoreExtraElements]
public class BarDTO
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    [JsonPropertyName("id")]
    public string? Id { get; set; } = null!;

    [BsonElement("name")]
    [JsonPropertyName("name")]
    public string? Name { get; set; } = null!;

    [BsonElement("foo")]
    [JsonPropertyName("foo")]
    public Foo? Foo { get; set; } = null!;
}

public class TempBarDTO : BarDTO
{
    [BsonElement("foos")]
    public IEnumerable<Foo>? Foos { get; set; } = null!;
}

As you can see above, TempBarDTO contains all the fields from BarDTO, but adds a property for the result of the lookup.

Also note the BsonIgnoreExtraElements attribute that has been applied to BarDTO.

After this, you can run the following code to retrieve the documents:

var result = barDbCollection.Aggregate()
    .Lookup<BarDb, Foo, TempBarDTO>(fooCollection, x => x.FooId, x => x.Id, x => x.Foos)
    .Set(x => new TempBarDTO()
    {
        Foo = x.Foos!.FirstOrDefault(),
    })
    .As<BarDTO>()
    .ToList();

First, the lookup retrieves the documents and stores the result in the Foos property; then the Set stage assigns the first of the documents to the Foo property.

Afterwards, the As stage converts the document to the BarDTO class so that the temporary Foos property is removed without a Project stage.