MongoDB C# Driver - lookup (aggregation) with an Array not joining

459 Views Asked by At

I'm trying to join two collections by the Id utilizing the Lookup(aggregation). Two classes:

public class Accountant
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public ObjectId Id { get; set; }

    [BsonElement("bestClientsIds")]
    [BsonRepresentation(BsonType.ObjectId)]
    public string[] BestClientsIds { get; set; }

    public List<Client> MyClients { get; set; }
}
public class Client
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)] 
    public ObjectId Id { get; set; }

    [BsonElement("clientName")]
    public string Name { get; set; }
}

The query:

IMongoCollection<Accountant> collection = mongoDatabase.GetCollection<Accountant>("accountants");
IMongoCollection<Client> foreignCollection = mongoDatabase.GetCollection<Client>("clients");

                var results = collection.Aggregate()
                    .Lookup<Accountant, Client, Accountant>(
                        foreignCollection: foreignCollection,
                        localField: ac => ac.BestClientsIds,
                        foreignField: c => c.Id,
                        @as: ac => ac.MyClients
                    ).ToList().AsQueryable();

Documents:

accountants:
_id:ObjectId("5deea64bfb49b60019a4ac97")
active:true
bestClientsIds:Array
    0:"5de95d449f4b820413c89e11"
    1:"5d2c1cfb8f4b810011c89e14"
    2:"5d3c1e5a5f4b81e045c89e52"
__v:11


clients:
_id:ObjectId("5d3c1e5a5f4b81e045c89e52")
active:true
clientName:"John Doe"
__v:6

_id:ObjectId("5de95d449f4b820413c89e11")
active:true
clientName:"Mike Davis"
__v:6

I can't figure out why this is not working. The join doesn't happen since ac.MyClients has no elements even though ac.BestClientsIds array does. From what I understand $lookup with an Array is supported. I would really appreciate some assistance. Thx. (MongoDB v4.2.3, .Net Driver v2.10.1, data types can not be modified).

0

There are 0 best solutions below