MongoDB - How do I join the second collection to a child document using LINQ

Question

I have two collections - accounts and users. Logically, they have a many-to-many relationship. In MongoDB, they look like this:

users

[
  {
    "userId": "3Nv6yHTC6Eiq0SaMyBcDlA",
    "emailAddress": "foo@bar.com",
    "userAccounts":
    [
      {
        "accountId": "tgvANZWSZkWl0bAOM00IBw"
      }
    ]
  }
]

accounts

[
  {
    "accountId": "tgvANZWSZkWl0bAOM00IBw",
    "accountCode": "foo",
    "userIds":
    [
      "3Nv6yHTC6Eiq0SaMyBcDlA"
    ]
  }
]

Can I use a single LINQ operation using the MongoDB LINQ driver to join the account collection to the user's userAccounts child documents, such that I return a user (or list of users) with the accountCode included within each userAccount (the ExtendedUserAccount within the ExtendedUser in the example below)? Or do I need to forget LINQ and use the Aggregate method instead?

The query below results in an ExpressionNotSupportedExpression from the MongoDB LINQ driver. If I split the query to get the user first and then join the accounts collection, it works.

Here is some code:

using MongoDB.Bson.Serialization.Conventions;
using MongoDB.Driver;
using MongoDB.Driver.Linq;

var settings = MongoClientSettings.FromConnectionString("yourconnectionstring");
settings.LinqProvider = MongoDB.Driver.Linq.LinqProvider.V3;
var client = new MongoClient(settings);
var conventionPack = new ConventionPack { new CamelCaseElementNameConvention(),  new IgnoreExtraElementsConvention(true) };
ConventionRegistry.Register("camelCase", conventionPack, t => true);
var db = client.GetDatabase("Test");

var accountCollection = db.GetCollection<Account>("accounts");
var userCollection = db.GetCollection<User>("users");
var queryableAccounts = accountCollection.AsQueryable();

var extendedUser = userCollection.AsQueryable()
    .Where(u => u.EmailAddress == "foo@bar.com")
    .Select(u => new ExtendedUser(
        u.UserId,
        u.EmailAddress,
        u.UserAccounts.Join(
            queryableAccounts,
            ua => ua.AccountId,
            a => a.AccountId,
            (ua, a) => new ExtendedUserAccount(a.AccountCode, ua.AccountId)))
    )
    .FirstOrDefault();


Console.WriteLine(extendedUser);

public record class User(string UserId, string EmailAddress, IEnumerable<UserAccount> UserAccounts);

public record class UserAccount(string AccountId);

public record class Account(string AccountId, string AccountCode, IEnumerable<string> UserIds);

public record class ExtendedUser(string UserId, string EmailAddress, IEnumerable<ExtendedUserAccount> UserAccounts);

public record class ExtendedUserAccount(string AccountId, string AccountCode);

Answer 1

I tested even with LinqProviderV3 setting on MongoClientSettings, but sadly to say that the MongoDB .Net Driver seems doesn't support it (sub-query join).

You should look for .Aggregate().

MongoDB query

db.users.aggregate([
  {
    $match: {
      emailAddress: "foo@bar.com"
    }
  },
  {
    $lookup: {
      from: "accounts",
      localField: "userAccounts.accountId",
      foreignField: "accountId",
      pipeline: [
        {
          $project: {
            _id: 0,
            accountId: 1,
            accountCode: 1
          }
        }
      ],
      as: "userAccounts"
    }
  }
])

Approach 1: Pass the query as BsonDocument

Either you can translate the above query (the array part) to BsonDocument[] with the help of MongoDB Compass. It should be as below:

var pipeline = new BsonDocument[]
{
    new BsonDocument("$match", 
    new BsonDocument("emailAddress", "foo@bar.com")),
    new BsonDocument("$lookup", 
    new BsonDocument
        {
            { "from", "accounts" }, 
            { "localField", "userAccounts.accountId" }, 
            { "foreignField", "accountId" }, 
            { "pipeline", 
                new BsonArray
                {
                    new BsonDocument("$project", 
                    new BsonDocument
                        {
                            { "_id", 0 }, 
                            { "accountId", 1 }, 
                            { "accountCode", 1 }
                        })
            } }, 
            { "as", "userAccounts" }
        })
};

var extendedUser = userCollection.Aggregate<ExtendedUser>(pipeline)
    .FirstOrDefault();

Approach 2: Working with Aggregate Fluent

var lookupPipeline = new EmptyPipelineDefinition<Account>()
    .Match(new BsonDocument("$expr",
        new BsonDocument("$in", new BsonArray { "$accountId", "$$accountId" })
        ))
    .Project<Account, Account, ExtendedUserAccount>(
        Builders<Account>.Projection
            .Include(x => x.AccountId)
            .Include(x => x.AccountCode));

var extendedUser = userCollection.Aggregate()
    .Match(u => u.EmailAddress == "foo@bar.com")
    .Lookup(accountCollection,
        new BsonDocument { { "accountId", "$userAccounts.accountId" } },
        lookupPipeline,
        new ExpressionFieldDefinition<ExtendedUser, IEnumerable<ExtendedUserAccount>>(x => x.UserAccounts)
    )
    .FirstOrDefault();

Similar usage for MongoDB.Driver.Tests AggregateFluentTest

Demo