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).