How do I join to a second mongo collection to a child document using Linq-CodePudding

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

users

[
  {
    "userId": "3Nv6yHTC6Eiq0SaMyBcDlA",
    "emailAddress": "[email protected]",
    "userAccounts":
    [
      {
        "accountId": "tgvANZWSZkWl0bAOM00IBw"
      }
    ]
  }
]

accounts

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

Can I use a single Linq operation using the mongo 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 class instead?

The query below results in an ExpressionNotSupportedExpression from the mongo Linq driver. If I split the query to get the user first and then join to 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 == "[email protected]")
    .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);

CodePudding user response：

I tested even with