MongoDB repository and use of the aggregation framework for sorting and inclusion of navigation properties #829

• ABP Framework version: v4.1.0
• UI type: MVC
• DB provider: MongoDB
• Tiered (MVC) or Identity Server Seperated (Angular): yes
• Exception message and stack trace:
• Steps to reproduce the issue:

Do you think that it could be possible to extend the abp suite / cli to be able to specify if the generated MongoDB repositories should use either IQueryable or IAggregateFluent?

Or maybe after reviewing the details below, newly generated MongoDB repositories should be generated to use IAggregateFluent and not use IQueryable?

The automatically generated Entity Framework repositories include join statements to include navigation properties, so that the navigation properties can be used in the OrderBy calls and returned. However, the MongoDB repositories do not have this functionality and perform separate database calls to fetch the navigation properties. This means that the navigation properties cannot be used in sort or filters (this results in a bug where sorting does not work from the UI, when it appears to a user like it should).

The default generated MongoDB repositories currently internally use IQueryable to perform any filtering. However, assuming that a developer wants to enable similar functionality as the EntityFramework repositories or take advantage of the full capabilities of MongoDB, then they should use aggregation pipelines instead and work with an IAggregateFluent pipeline.

We have found ourselves doing exactly this, and have replaced all of the GetMongoQueryable() calls in the repositories with an GetMongoAggregated() extension method to return an IAggregateFluent.

public static IAggregateFluent<TEntity> GetMongoAggregated<TMongoDbContext, TEntity>(this MongoDbRepository<TMongoDbContext, TEntity> repository)
            where TMongoDbContext : IExtendedMongoDbContext
            where TEntity : class, IEntity
        {
            IAggregateFluent<TEntity> aggregate = repository.SessionHandle != null ? repository.Collection.Aggregate(repository.SessionHandle) : repository.Collection.Aggregate();

            return aggregate.ApplyDataFilters(repository);
        }

        private static IAggregateFluent<TEntity> ApplyDataFilters<TMongoDbContext, TEntity>(this IAggregateFluent<TEntity> aggregate, MongoDbRepository<TMongoDbContext, TEntity> repository)
            where TMongoDbContext : IExtendedMongoDbContext
            where TEntity : class, IEntity
        {
            if (typeof(ISoftDelete).IsAssignableFrom(typeof(TEntity)) && repository.DataFilter.IsEnabled<ISoftDelete>())
            {
                aggregate = aggregate.Match(e => ((ISoftDelete)e).IsDeleted == false);
            }

            if (typeof(IMultiTenant).IsAssignableFrom(typeof(TEntity)) && repository.DataFilter.IsEnabled<IMultiTenant>())
            {
                var tenantId = repository.CurrentTenant.Id;
                aggregate = aggregate.Match(e => ((IMultiTenant)e).TenantId == tenantId);
            }

            return aggregate;
        }

The main reason for us to do this was that we can now use the aggregation pipelines to easily "Include" any entities referenced by navigation properties, which can then be used within a single database call (e.g. for sorting) and/or returned within that same query (no additional database queries needed to get the navigation property entities).

Some example usages are:

public async Task<ChannelWithNavigationProperties> GetWithNavigationPropertiesAsync(Guid id, CancellationToken cancellationToken = default)
        {
            var channel = await this.GetMongoAggregated()
                .Match(e => e.Id == id)
                .Include<Channel, ProductVersion>(DbContext)
                .FirstOrDefaultAsync(GetCancellationToken(cancellationToken));
            
            return new ChannelWithNavigationProperties
            {
                Channel = channel,
                ProductVersion = channel.GetIncluded<ProductVersion>(),
            };
        }
        
public async Task<List<ChannelWithNavigationProperties>> GetListWithNavigationPropertiesAsync(
            string filterText = null,
            string sorting = null,
            int maxResultCount = int.MaxValue,
            int skipCount = 0,
            CancellationToken cancellationToken = default)
        {
            var query = ApplyFilter(this.GetMongoAggregated(), filterText);

            var channels = await query
                .Include<Channel, ProductVersion>(DbContext)
                .Sort(sorting, ChannelConsts.GetDefaultSorting(false))
                .Skip(skipCount).Limit(maxResultCount)
                .ToListAsync(GetCancellationToken(cancellationToken));

            List<ChannelWithNavigationProperties> result = new List<ChannelWithNavigationProperties>();

            foreach (var s in channels)
            {
                result.Add(new ChannelWithNavigationProperties
                {
                    Channel = s,
                    ProductVersion = s.GetIncluded<ProductVersion>()
                });
            }

            return result;
        }

In the example, a Channel has a "ProductVersionId" navigation property. Any ApplyFilter methods can also easily be changed to use the GetMongoAggregated extension method for consistency.

We have some extension methods which make this very easy to use.

        public static IAggregateFluent<TEntity> Include<TEntity, TLookupEntity>(this IAggregateFluent<TEntity> query, IExtendedMongoDbContext dbContext, string localFieldName = null)
            where TEntity : class
        {
            string entityName = typeof(TLookupEntity).Name;

            if (string.IsNullOrEmpty(localFieldName))
            {
                localFieldName = entityName + "Id";
            }

            var queryBson = query.As<BsonDocument>();

            // Perform a lookup. If it exists then it will be included in an array called entityName
            queryBson = queryBson.Lookup(dbContext.GetCollectionNamePublic<TLookupEntity>(), localFieldName, "_id", entityName);
            
            // convert the single value in the array to a property instead (use the options to handle the scenario that the foreign entity does not exist)
            queryBson = queryBson.Unwind(new StringFieldDefinition<BsonDocument>(entityName), new AggregateUnwindOptions<BsonDocument>() {PreserveNullAndEmptyArrays = true});
            
            return queryBson.As<TEntity>();
        }

        public static TEntity GetIncluded<TEntity>(this IHasExtraProperties entity, string fieldName = null) where TEntity : class
        {
            var properties = entity.ExtraProperties;

            if (string.IsNullOrEmpty(fieldName))
            {
                fieldName = typeof(TEntity).Name;
            }

            if (properties.TryGetValue(fieldName, out object entityObject) && entityObject is Dictionary<string, object> dictionary)
            {
                return BsonSerializer.Deserialize<TEntity>(dictionary.ToBsonDocument());
            }

            return null;
        }
        
        /// <summary>
        /// Support sort of the format "myColumn asc" or "myOtherColumn desc".
        /// The first letter of any sort field is automatically capitalized.
        /// Can also sort on multiple columns "myColumn asd, myOtherColumn desc".
        /// Can also sort by sub properties "myColumn.something desc"
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="query"></param>
        /// <param name="sorting"></param>
        /// <param name="defaultSorting"></param>
        /// <returns></returns>
        public static IAggregateFluent<TEntity> Sort<TEntity>(this IAggregateFluent<TEntity> query, string sorting, string defaultSorting)
        {
            sorting = string.IsNullOrWhiteSpace(sorting) ? defaultSorting : sorting;

            if (string.IsNullOrEmpty(sorting))
                return query;

            string[] sortParts = sorting.Split(',');

            List<SortDefinition<TEntity>> sortDefinitions = new List<SortDefinition<TEntity>>();
            
            // check if a sort definition is sorting using a property of the top level entity
            string topLevelEntitySortPrefix = typeof(TEntity).Name + ".";

            foreach (var sortPart in sortParts)
            {
                var parts = sortPart.Split(" ");

                if (parts.Length == 0)
                    continue;

                string fieldName = CorrectCase(parts[0]);
                
                if (fieldName.StartsWith(topLevelEntitySortPrefix))
                {
                    fieldName = fieldName.Substring(topLevelEntitySortPrefix.Length);
                }

                if (parts.Length == 1)
                {
                    sortDefinitions.Add(Builders<TEntity>.Sort.Ascending(fieldName));
                }

                if (parts.Length > 1)
                {
                    if (PartIsDescending(parts[1]))
                    {
                        sortDefinitions.Add(Builders<TEntity>.Sort.Descending(fieldName));
                    }
                    else
                    {
                        sortDefinitions.Add(Builders<TEntity>.Sort.Ascending(fieldName));
                    }
                }
            }

            if (sortDefinitions.Any())
            {
                query = query.Sort(Builders<TEntity>.Sort.Combine(sortDefinitions));
            }

            return query;
        }

        private static bool PartIsDescending(string part)
        {
            if (string.IsNullOrEmpty(part))
                return false;

            return part.ToLower().Contains("des");
        }

        public static string CorrectCase(string input)
        {
            var parts = input.Split('.');

            return String.Join(".", parts.Select(FirstCharToUpper));
        }

        private static string FirstCharToUpper(string input)
        {
            if (string.IsNullOrEmpty(input))
                throw new ArgumentNullException(nameof(input));

            return input.First().ToString().ToUpper() + input.Substring(1);
        }

The IExtendedMongoDbContext simply exposes the protected GetCollectionName<T>() method publicly as GetCollectionNamePublic<T>(). The Sort method supports being able to sort on foreign entity properties and also supports sorting on multiple columns (datatables.net => shift + click).

Whatever the outcome, I hope that this code that I am sharing might be useful for other people wanting to use MongoDB.