How to join collections by id with MongoDb C# Driver

Question

I'm trying to join two collections by Id. Although I can see rows in the database, the query results are empty.

I have two collections: userGames and games

Here is my Code to join collections:

    var userGamesQuery = userGames.AsQueryable()
        .Where(x => x.UserId == userId);

    var query = from userGame in userGamesQuery
                join game in games.AsQueryable() on userGame.GameId equals game.Id
                select game;

the first part returns 13 elements but the second part return nothing. I think the line userGame.GameId equals game.Id has some problems. tried to use ObjectId.Parse() on both userGame.GameId and game.Id but no luck.

Here are my Models

Game Class :

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

UserGames :

public class UserGames
    {
        [BsonId]
        [BsonRepresentation(BsonType.ObjectId)]
        public string Id { get; set; }
        public string UserId { get; set; }
        public string GameId { get; set; }

    }

have a look at the library [MongoDB.Entities](https://github.com/dj-nitehawk/MongoDB.Entities). it has built-in support for relationships which makes creating and querying relationships between entities/documents quite effortless. there's no need to create join-entities like the `UserGames` like above or manually write join queries. — Dĵ ΝιΓΞΗΛψΚ, Jul 03 '19 at 04:32

score 3 · Accepted Answer · answered Jul 01 '19 at 17:51

The issue is that when you run "join" it tries to run equality comparison on both fields on the database side. To consider two values equal MongoDB checks types first and ten values. Since GameId is of type string and Id is stored as BsonId there will be no match between those collections.

The easiest way to fix that would be to change your class into:

public class UserGames
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }
    public string UserId { get; set; }
    [BsonRepresentation(BsonType.ObjectId)]
    public string GameId { get; set; }
}

and run a script on your database to convert existing data. It can be something like:

db.UserGames.aggregate([
    { $addFields: { GameId: { $toObjectId: "$GameId" } } },
    { $out: "UserGames" }
])

You can also try to convert types using either $toObjectId or $toString directly in your aggregation but there's no easy and convenient way to do that using strongly typed driver API.

How to join collections by id with MongoDb C# Driver

1 Answers1