在C＃中，如何使用MongoDB的聚合/查找来使其更有效？

Question

我们有一个微服务可以缓存 Salesforce 数据以减轻 Salesforce 的限制。虽然此缓存甚至没有尝试模仿 Salesforce 的细致/复杂的数据安全策略，但我们确实有一些逻辑来使用角色来保护字段。

我们希望连接来自多个 SObject（表）的记录，每个 SObject 进入 MongoDb 后都存储在单独的 Collection 中。事实上，我已经为此实现了一个解决方案，但是（毫不奇怪）性能还有很多不足之处，因为连接是在 C# 而不是 MongoDb 中进行的。

解决方案的相关部分如下所示：

    public async Task<(List<JObject> records, int totalCount)> FindRecords(GenericQueryParameters parameters, CancellationToken cancellationToken)
    {
        (List<BsonDocument> resultRecords, int count) = await FindMongoRecords(parameters, cancellationToken);
        List<BsonDocument> joinedResults = await Join(resultRecords, parameters.ExtractedJoinCriteria, cancellationToken);
        List<JObject> jObjectRecords = resultRecords.Select(ToJObject)
            .ToList();
        return (jObjectRecords, (int)count);
    }

    private async Task<(List<BsonDocument> records, int totalCount)> FindMongoRecords(GenericQueryParameters parameters, CancellationToken cancellationToken)
    {
        ISObjectConfigurable config = await _sObjectConfigurationManager.Get(parameters.SObjectName);

        IMongoCollection<BsonDocument> collection = GetReadCollection(config);
        FilterDefinition<BsonDocument> filters = BuildFilters(config, parameters);
        IFindFluent<BsonDocument, BsonDocument> filteredCollection = collection.Find(filters);

        IFindFluent<BsonDocument, BsonDocument> sortedCollection = (parameters.SortTransition == Transitions.Ascending)
            ? filteredCollection.SortBy(x => x[parameters.SortField])
            : filteredCollection.SortByDescending(x => x[parameters.SortField]);

        List<BsonDocument> resultRecords = await sortedCollection
            .Skip((parameters.PageNumber - 1) * parameters.PageSize)
            .Limit(parameters.PageSize)
            .Project(GetReadableFields(config))
            .ToListAsync(cancellationToken);
        long count = await collection.CountDocumentsAsync(filters, cancellationToken: cancellationToken);
        return (resultRecords, (int)count);
    }

    private async Task<List<BsonDocument>> Join(List<BsonDocument> resultRecords, List<JoinCriteria> joinCriteria, CancellationToken cancellationToken)
    {
        foreach (JoinCriteria joinCriterium in joinCriteria)
        {
            HashSet<string> targets = resultRecords.Select(x => x[joinCriterium.ParentFieldName])
                .Select(x => $"\"{x}\"")
                .ToHashSet();

            GenericQueryParameters childQueryParameters = new()
            {
                SObjectName = joinCriterium.ChildSObjectName,
                PageSize = 50,
                Filters = new List<string> {
                    $"{joinCriterium.ChildFieldName} IN {{{string.Join(",", targets)}}}"
                }
            };
            (List<BsonDocument> allChildRecords, int _) = await FindMongoRecords(childQueryParameters, cancellationToken);
            Dictionary<BsonValue, List<BsonDocument>> childRecordsByChildField = allChildRecords
                .GroupBy(x => x[joinCriterium.ChildFieldName], x => x)
                .ToDictionary(group => group.Key, group => group.ToList());

            foreach (BsonDocument resultRecord in resultRecords)
            {
                BsonValue parentFieldValue = resultRecord[joinCriterium.ParentFieldName];
                resultRecord[joinCriterium.Collection] = childRecordsByChildField.TryGetValue(parentFieldValue, out List<BsonDocument> childRecords)
                    ? ToBsonDocumentArray(childRecords)
                    : new BsonArray();
            }
        }

        return resultRecords;
    }

    private static BsonArray ToBsonDocumentArray(List<BsonDocument> childRecords)
    {
        BsonArray array = new();
        foreach (BsonDocument childRecord in childRecords)
        {
            _ = array.Add(childRecord);
        }

        return array;
    }

    private ProjectionDefinition<BsonDocument, BsonDocument> GetReadableFields(ISObjectConfigurable config)
    {
        ProjectionDefinitionBuilder<BsonDocument> projectionDefinitionBuilder = Builders<BsonDocument>.Projection;
        IEnumerable<ProjectionDefinition<BsonDocument>> projectionDefinitions = _oAuthRoleValidator.IsAdmin()
            ? new List<ProjectionDefinition<BsonDocument>>()
            : CreateSecureProjection(config, projectionDefinitionBuilder);

        return projectionDefinitionBuilder.Combine(projectionDefinitions);
    }

    private IEnumerable<ProjectionDefinition<BsonDocument>> CreateSecureProjection(
            ISObjectConfigurable config,
            ProjectionDefinitionBuilder<BsonDocument> projectionDefinitionBuilder
        )
    {
        IEnumerable<string> hiddenFields = config.SObjectFields.Where(field => !_oAuthRoleValidator.UserCanReadField(config.FieldConfigByName, field));
        IEnumerable<string> hiddenShadows = hiddenFields.Select(x => "_" + x);
        return hiddenFields.Concat(hiddenShadows)
            .Select(field => projectionDefinitionBuilder.Exclude(field));
    }

GenericQueryParameters 如下所示：

public class GenericQueryParameters
{
    private static readonly Regex GenericJoinRegex = new(@"(\w+)\s*:(\w+).(\w+)\s*==\s*(\w+)", RegexOptions.Compiled);

    [JsonProperty(PropertyName = "sObjectName")]
    public string SObjectName { get; set; }

    [JsonProperty(PropertyName = "filters")]
    public List<string> Filters { get; set; } = new List<string>();

    [JsonProperty(PropertyName = "joins")]
    public List<string> JoinCriteria { get; set; } = new List<string>();

    [JsonIgnore]
    [SuppressMessage("Style", "IDE1006:Naming Styles", Justification = "This is a hidden backing field.")]
    private List<JoinCriteria> _extractedJoinCriteria { get; set; }

    [JsonIgnore]
    public List<JoinCriteria> ExtractedJoinCriteria
    {
        get
        {
            if (_extractedJoinCriteria == null)
            {
                _extractedJoinCriteria = JoinCriteria.Select(x => ExtractCriterium(x))
                    .ToList();
            }

            return _extractedJoinCriteria;
        }
    }

    [JsonProperty(PropertyName = "pageNumber")]
    public int PageNumber { get; set; } = 1;

    private const int MaxPageSize = 50;
    private int _pageSize = 10;

    [JsonProperty(PropertyName = "pageSize")]
    public int PageSize
    {
        get => _pageSize;
        set => _pageSize = (value > MaxPageSize)
            ? MaxPageSize
            : value;
    }

    [JsonProperty(PropertyName = "sortField")]
    public string SortField { get; set; } = "_CreatedDate";

    [JsonProperty(PropertyName = "sortTransition")]
    public Transitions SortTransition { get; set; } = Transitions.Ascending;

    [JsonProperty(PropertyName = "includeDeleted")]
    public bool IncludeDeleted { get; set; } = false;

    [JsonProperty(PropertyName = "syncNow")]
    public bool SynchronizeFirst { get; set; } = false;

    [JsonProperty(PropertyName = "transformationTemplateName")]
    public string TransformationTemplateName { get; set; }

    private static JoinCriteria ExtractCriterium(string joinCriteriumString)
    {
        Match match = GenericJoinRegex.Match(joinCriteriumString);
        return match.Success
            ? new()
            {
                Collection = match.Groups[1].Value,
                ChildSObjectName = match.Groups[2].Value,
                ChildFieldName = match.Groups[3].Value,
                ParentFieldName = match.Groups[4].Value
            }
            : throw new MalformedDatabaseJoinException($"The filter '{joinCriteriumString}' could not be parsed.");
    }

和 JoinCriteria 如下所示：

public class JoinCriteria
{
    public string Collection { get; init; }
    public string ChildSObjectName { get; init; }
    public string ChildFieldName { get; init; }
    public string ParentFieldName { get; init; }
}

如您所见，在本解决方案中，每个 SObject/Collection 都会被查询然后分别对数据进行编辑以符合消费者的权限，最后将数据组合起来返回给消费者。

如何重构此解决方案以在 MongoDb 中执行联接？