如何用MongoDB LINQ驱动单操作关联accounts集合到用户的userAccounts子文档？

我在MongoDB中有accounts和users两个集合，二者是逻辑上的多对多关系，文档结构如下：

users集合

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

accounts集合

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

我想返回包含每个userAccount关联accountCode的用户数据（即示例中的ExtendedUser结构，其中ExtendedUserAccount包含accountId和accountCode）。请问能否通过MongoDB LINQ驱动的单个LINQ操作完成这个关联？还是必须放弃LINQ改用Aggregate方法？

我尝试了以下LINQ查询，但触发了ExpressionNotSupportedException错误；但如果先查询用户数据，再在客户端关联accounts集合就能正常运行：

报错的C#代码示例

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

MongoDB LINQ驱动（包括V3版本）不支持将嵌套数组（如User.UserAccounts）与另一个集合进行Join操作。这种跨集合的嵌套数组Join无法被LINQ驱动转换为MongoDB支持的服务器端查询（比如$lookup），因此会抛出ExpressionNotSupportedException。

方案1：客户端内存关联（简单直接） #

先查询目标用户，再查询所有相关的账户，最后在客户端内存中完成关联。这种方式逻辑简单，适合数据量不大的场景：

// 先查询目标用户
var user = userCollection.AsQueryable()
    .Where(u => u.EmailAddress == "foo@bar.com")
    .FirstOrDefault();

if (user == null) return;

// 获取用户关联的所有accountId
var accountIds = user.UserAccounts.Select(ua => ua.AccountId).ToList();
// 查询对应的账户
var accounts = accountCollection.AsQueryable()
    .Where(a => accountIds.Contains(a.AccountId))
    .ToList();

// 在客户端关联构造ExtendedUser
var extendedUser = new ExtendedUser(
    user.UserId,
    user.EmailAddress,
    user.UserAccounts.Join(
        accounts,
        ua => ua.AccountId,
        a => a.AccountId,
        (ua, a) => new ExtendedUserAccount(ua.AccountId, a.AccountCode)
    )
);

Console.WriteLine(extendedUser);

方案2：使用Aggregate管道（服务器端关联） #

如果希望在服务器端完成所有操作，避免客户端内存处理，可以使用MongoDB的Aggregate API，通过$lookup实现跨集合关联，再构造目标结构。

方式A：LINQ Aggregate语法 #

// 需要给User类临时添加MatchedAccounts属性，用于接收关联结果
public record class User(string UserId, string EmailAddress, IEnumerable<UserAccount> UserAccounts, IEnumerable<Account> MatchedAccounts);

var extendedUser = userCollection.Aggregate()
    .Match(u => u.EmailAddress == "foo@bar.com")
    // 关联accounts集合，匹配userAccounts中的accountId
    .Lookup(
        foreignCollection: accountCollection,
        localField: "userAccounts.accountId",
        foreignField: "accountId",
        @as: "MatchedAccounts"
    )
    // 构造ExtendedUser结构
    .Project(u => new ExtendedUser(
        u.UserId,
        u.EmailAddress,
        u.UserAccounts.Join(
            u.MatchedAccounts,
            ua => ua.AccountId,
            a => a.AccountId,
            (ua, a) => new ExtendedUserAccount(ua.AccountId, a.AccountCode)
        )
    ))
    .FirstOrDefault();

Console.WriteLine(extendedUser);

方式B：BsonDocument管道（无需修改实体类） #

如果不想修改原有实体类，可以直接用BsonDocument定义Aggregate管道：

var pipeline = new BsonDocument[]
{
    // 匹配目标用户
    new BsonDocument("$match", new BsonDocument("emailAddress", "foo@bar.com")),
    // 关联accounts集合
    new BsonDocument("$lookup", new BsonDocument
    {
        { "from", "accounts" },
        { "localField", "userAccounts.accountId" },
        { "foreignField", "accountId" },
        { "as", "matchedAccounts" }
    }),
    // 构造最终结构：合并userAccounts与matchedAccounts，补充accountCode
    new BsonDocument("$project", new BsonDocument
    {
        { "userId", 1 },
        { "emailAddress", 1 },
        { "userAccounts", new BsonDocument("$map", new BsonDocument
            {
                { "input", "$userAccounts" },
                { "as", "ua" },
                { "in", new BsonDocument("$mergeObjects", new BsonArray
                    {
                        "$$ua",
                        new BsonDocument("$arrayElemAt", new BsonArray
                            {
                                new BsonDocument("$filter", new BsonDocument
                                    {
                                        { "input", "$matchedAccounts" },
                                        { "cond", new BsonDocument("$eq", new BsonArray { "$$this.accountId", "$$ua.accountId" }) }
                                    }),
                                0
                            })
                    })
            })
        },
        { "_id", 0 }
    })
};

var result = userCollection.Aggregate<ExtendedUser>(pipeline).FirstOrDefault();
Console.WriteLine(result);

内容的提问来源于stack exchange，提问作者darasd