Querying a collection via a match in an ID-to-ID collection in MongoDB using C# Driver-CodePudding

I have the following collection document types:

Car
{
   Id
   Make
   Model
}

and

UserCar
{
   UserId
   CarId   (references Car.Id)
}

I'm having trouble finding the correct query using MongoDB C# Driver to:

Get all Cars where UserId = x

This is a very simple query in SQL, so I'm quite frustrated that this is proving so difficult with their SDK (for me, at least). It's the ID-to-ID collection containing the filter that seems to be the issue. So, starting with:

var db = MongoUtil.GetDb();
var cars = db.GetCollection<Car>("Cars");
var userCars = db.GetCollection<UserCar>("UserCars");

List<Car> carsJoined = ??? where UserId = 1234

Thanks for the help.

CodePudding user response：

Work with MongoDB .NET Driver .Aggregate() (Although the query below looks a bit complex).

var result = cars
    .Aggregate()
    .Lookup<UserCar, BsonDocument>(
        "UserCars", 
        "Id", 
        "CarId",
        "userCars")
    .Match(Builders<BsonDocument>.Filter
        .ElemMatch<BsonValue>("userCars", new BsonDocument("UserId", 1234)))
    .Project<masterDocument>(Builders<BsonDocument>.Projection
        .Exclude("userCars"))
    .ToList();

This is equivalent to the aggregate query

db.cars.aggregate([
  {
    $lookup: { 
        from: "UserCars",
        localField: "Id"
        foreignField: "CarId",
        as: "userCars"
    }
  },
  {
    $match: {
      "userCars": {
        $elemMatch: {
          "UserId": 1234
        }
      }
    }
  },
  {
    $project: {
      userCars: 0,
      // Other fields: 1
    }
  }
])