/// <summary> /// Appends an ascending sort stage to the pipeline. /// </summary> /// <typeparam name="TResult">The type of the result.</typeparam> /// <param name="aggregate">The aggregate.</param> /// <param name="field">The field to sort by.</param> /// <returns> /// The fluent aggregate interface. /// </returns> public static IOrderedAggregateFluent <TResult> SortBy <TResult>(this IAggregateFluent <TResult> aggregate, Expression <Func <TResult, object> > field) { Ensure.IsNotNull(aggregate, nameof(aggregate)); Ensure.IsNotNull(field, nameof(field)); var sort = Builders <TResult> .Sort.Ascending(field); return((IOrderedAggregateFluent <TResult>)aggregate.AppendStage(PipelineStageDefinitionBuilder.Sort(sort))); }
public async Task <PaginationData <Post> > GetPostsByBlogIdAndPaginationAsync( string blogId, Pagination pagination, bool ascending = false) { var filter = Builders <Post> .Filter.Eq(a => a.BlogId, blogId); var aggregateFluent = Collection.Aggregate(); var totalFacet = AggregateFacet.Create("total", PipelineDefinition <Post, AggregateCountResult> .Create(new[] { PipelineStageDefinitionBuilder.Count <Post>() })); AggregateFacet <Post, Post> dataFacet; if (ascending) { dataFacet = AggregateFacet.Create("data", PipelineDefinition <Post, Post> .Create(new[] { PipelineStageDefinitionBuilder.Sort(Builders <Post> .Sort.Ascending(x => x.CreatedDate)), PipelineStageDefinitionBuilder.Skip <Post>(pagination.NumberPerPage * (pagination.CurrentPage - 1)), PipelineStageDefinitionBuilder.Limit <Post>(pagination.NumberPerPage), })); } else { dataFacet = AggregateFacet.Create("data", PipelineDefinition <Post, Post> .Create(new[] { PipelineStageDefinitionBuilder.Sort(Builders <Post> .Sort.Descending(x => x.CreatedDate)), PipelineStageDefinitionBuilder.Skip <Post>(pagination.NumberPerPage * (pagination.CurrentPage - 1)), PipelineStageDefinitionBuilder.Limit <Post>(pagination.NumberPerPage), })); } var aggregation = await aggregateFluent .Match(filter) .Facet(totalFacet, dataFacet) .ToListAsync(); var total = aggregation .First() .Facets.First(a => a.Name == "total") .Output <AggregateCountResult>() .First() .Count; var data = aggregation .First() .Facets.First(a => a.Name == "data") .Output <Post>(); return(new PaginationData <Post>(data, total, pagination.CurrentPage, pagination.NumberPerPage, pagination.MaximumPage)); }
public override IOrderedAggregateFluent <TResult> ThenBy(SortDefinition <TResult> newSort) { Ensure.IsNotNull(newSort, nameof(newSort)); var stages = _pipeline.Stages.ToList(); var oldSortStage = (SortPipelineStageDefinition <TResult>)stages[stages.Count - 1]; var oldSort = oldSortStage.Sort; var combinedSort = Builders <TResult> .Sort.Combine(oldSort, newSort); var combinedSortStage = PipelineStageDefinitionBuilder.Sort(combinedSort); stages[stages.Count - 1] = combinedSortStage; var newPipeline = new PipelineStagePipelineDefinition <TDocument, TResult>(stages); return((IOrderedAggregateFluent <TResult>)WithPipeline(newPipeline)); }
private static IEnumerable <PipelineStageDefinition <TDocument, TDocument> > GetPipelineDefinitions <TDocument>( FilterDefinition <TDocument> filterQuery, SortDefinition <TDocument> sortQuery, int pageIndex, int pageSize) { yield return(PipelineStageDefinitionBuilder.Match(filterQuery)); if (sortQuery != null) { yield return(PipelineStageDefinitionBuilder.Sort(sortQuery)); } yield return(PipelineStageDefinitionBuilder.Skip <TDocument>((pageIndex - 1) * pageSize)); yield return(PipelineStageDefinitionBuilder.Limit <TDocument>(pageSize)); }
public async Task <Envelope <T> > GetEnvelopeAsync <T>(IMongoCollection <T> collection, FilterDefinition <T> filter, PageParameters pageParams) { var countFacet = AggregateFacet.Create("count", PipelineDefinition <T, AggregateCountResult> .Create(new[] { PipelineStageDefinitionBuilder.Count <T> () })); var dataFacet = AggregateFacet.Create("data", PipelineDefinition <T, T> .Create(new[] { (pageParams.IsAscend) ? PipelineStageDefinitionBuilder.Sort(Builders <T> .Sort.Ascending(pageParams.SortBy)): PipelineStageDefinitionBuilder.Sort(Builders <T> .Sort.Descending(pageParams.SortBy)), PipelineStageDefinitionBuilder.Skip <T> ((pageParams.PageNumber - 1) * pageParams.PageSize), PipelineStageDefinitionBuilder.Limit <T> (pageParams.PageSize) })); var aggregation = await collection.Aggregate() .Match(filter) .Facet(countFacet, dataFacet) .ToListAsync(); var count = (aggregation.FirstOrDefault() .Facets.FirstOrDefault(x => x.Name == "count") .Output <AggregateCountResult>() .FirstOrDefault() ?? new AggregateCountResult(0)).Count; var data = aggregation.FirstOrDefault() .Facets.FirstOrDefault(x => x.Name == "data") .Output <T>() .ToList(); var totalPages = (int)Match.Ceiling(count / (double)pageParams.PageSize); var hasPreviousPage = pageParams.PageNumber > 1; var hasNextPage = pageParams.PageNumber < totalPages; var envelope = new Envelope <T>() { Items = data, TotalSize = count TotalPages = totalPages HasPreviousPage = HasPreviousPage HasNextPage = hasNextPage }; return(envelope); } }
public async Task <Page <TemplateKind> > GetTemplateKindsPage(int page, int pageSize, CancellationToken cancellationToken = default) { var countFacetName = "count"; var entitiesFacetName = "entities"; var countFacet = AggregateFacet.Create(countFacetName, PipelineDefinition <TemplateKind, AggregateCountResult> .Create(new[] { PipelineStageDefinitionBuilder.Count <TemplateKind>(), })); var sortDefinition = Builders <TemplateKind> .Sort.Ascending(x => x.TemplateKindKey); var entitiesFacet = AggregateFacet.Create(entitiesFacetName, PipelineDefinition <TemplateKind, TemplateKind> .Create(new[] { PipelineStageDefinitionBuilder.Sort(sortDefinition), PipelineStageDefinitionBuilder.Skip <TemplateKind>((page - 1) * pageSize), PipelineStageDefinitionBuilder.Limit <TemplateKind>(pageSize), })); var aggregation = await _collection.Aggregate() .Facet(countFacet, entitiesFacet) .ToListAsync(cancellationToken); var data = aggregation.First() .Facets.First(x => x.Name == entitiesFacetName) .Output <TemplateKind>(); var countOutput = aggregation.First() .Facets.First(x => x.Name == countFacetName) .Output <AggregateCountResult>(); long count = 0; if (countOutput.Any()) { count = countOutput.First() .Count; } return(new Page <TemplateKind>(count, data)); }
public static async Task <PagedCollection <T> > ApplyOData <T>(this IMongoCollection <T> collection, IODataQuery query) { var page = 1; var skip = query.Skip.Normalize(0); var limit = query.Limit.Normalize(10, 1000); var countFacet = AggregateFacet.Create("count", PipelineDefinition <T, AggregateCountResult> .Create(new[] { PipelineStageDefinitionBuilder.Count <T>() })); var sort = CreateODataSort <T>(query, false); var filter = CreateODataFilter <T>(query, false); var dataFacet = AggregateFacet.Create("data", PipelineDefinition <T, T> .Create(new [] { PipelineStageDefinitionBuilder.Sort(sort), PipelineStageDefinitionBuilder.Skip <T>(skip), PipelineStageDefinitionBuilder.Limit <T>(limit), })); var aggregation = await collection.Aggregate() .Match(filter) .Facet(countFacet, dataFacet) .ToListAsync(); var count = aggregation.First() .Facets.First(x => x.Name == "count") .Output <AggregateCountResult>() .First() .Count; int totalPages = (int)(count / limit); var data = aggregation.First() .Facets.First(x => x.Name == "data") .Output <T>(); return(PagedCollection <T> .Create(data, page, limit, totalPages, count)); }
public static async Task <IReadOnlyList <TDocument> > AggregateByPage <TDocument>( this IMongoCollection <TDocument> collection, FilterDefinition <TDocument> filterDefinition, SortDefinition <TDocument> sortDefinition, int page, int pageSize) { var countFacet = AggregateFacet.Create("count", PipelineDefinition <TDocument, AggregateCountResult> .Create(new[] { PipelineStageDefinitionBuilder.Count <TDocument>() })); var dataFacet = AggregateFacet.Create("data", PipelineDefinition <TDocument, TDocument> .Create(new[] { PipelineStageDefinitionBuilder.Sort(sortDefinition), PipelineStageDefinitionBuilder.Skip <TDocument>((page - 1) * pageSize), PipelineStageDefinitionBuilder.Limit <TDocument>(pageSize), })); var aggregation = await collection.Aggregate() .Match(filterDefinition) .Facet(countFacet, dataFacet) .ToListAsync(); var count = aggregation.First() .Facets.First(x => x.Name == "count") .Output <AggregateCountResult>() .First() .Count; var totalPages = (int)Math.Ceiling((double)count / pageSize); var data = aggregation.First() .Facets.First(x => x.Name == "data") .Output <TDocument>(); return(data); }
public async Task <ServiceResult <SongPagination> > RetreiveSongOfArtist(string artistName, int pageSize, int page) { var filter = Builders <Song> .Filter.Eq(s => s.ArtistName, artistName); var countFacet = AggregateFacet.Create("count", PipelineDefinition <Song, AggregateCountResult> .Create(new[] { PipelineStageDefinitionBuilder.Count <Song>() })); var paginationFacet = AggregateFacet.Create("pagination", PipelineDefinition <Song, Song> .Create(new[] { PipelineStageDefinitionBuilder.Sort(Builders <Song> .Sort.Ascending(x => x.Title)), PipelineStageDefinitionBuilder.Skip <Song>(pageSize * (page - 1)), PipelineStageDefinitionBuilder.Limit <Song>(pageSize) })); var aggregation = await this.songCollection.Aggregate() .Match(filter) .Facet(countFacet, paginationFacet) .ToListAsync(); long count = aggregation.First() .Facets.First(x => x.Name == "count") .Output <AggregateCountResult>() .First() .Count; var totalPages = Math.Ceiling((decimal)(count / pageSize)); IEnumerable <Song> items = aggregation.First() .Facets.First(f => f.Name == "pagination") .Output <Song>() .ToList(); return(ServiceResult.Succes(new SongPagination { ToTalPages = totalPages, Songs = items })); }
public async Task <DynamicListResponseDataModel> Query(DatabaseConnection databaseConnection, DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel) { dynamicList.GenerateFilters(); var dynamicListResponseDataModel = new DynamicListResponseDataModel(); // Because this flow is very complicated. We MUST UPDATE this flow fequently // 1. Extract collection name for executing // 2. Prepare execution query with some parts: // 2.1 Params // 2.2 Combine with Filters // 2.3 Sort // 3. Execute query with pagination // 4. Response data // 5. Note: in case entity, we still use the same way with EntityName field // 1. Extract collection name for executing, either Entity mode or Custom mode, EntityName is the same var executingQuery = dynamicList.ListDatasource.DatabaseConnectionOptions.Query; var parsingObject = JObject.Parse(executingQuery); var executingCollectionName = (parsingObject[Constants.QUERY_KEY].First as JProperty).Name; // 2. Prepare execution query // 2.2 Combine with Text Search, Filters and Sort var mongoCollection = new MongoClient(databaseConnection.ConnectionString).GetDatabase(databaseConnection.DataSource).GetCollection <BsonDocument>(executingCollectionName); var filterDefinitionOptions = FilterDefinition <BsonDocument> .Empty; var collectionQuery = parsingObject[Constants.QUERY_KEY][executingCollectionName].ToString(Newtonsoft.Json.Formatting.Indented); foreach (var filledParam in fetchDataModel.FilledParameterOptions.FilledParameters) { collectionQuery = collectionQuery.Replace("{{" + filledParam.Name + "}}", filledParam.Value); } collectionQuery = _mongoOptions.CurrentValue.EliminateDoubleQuotes(collectionQuery); var aggregatePipes = BsonSerializer.Deserialize <BsonDocument[]>(collectionQuery).Select(a => (PipelineStageDefinition <BsonDocument, BsonDocument>)a).ToList(); var aggregateFluent = mongoCollection.Aggregate(); foreach (var pipe in aggregatePipes) { aggregateFluent = aggregateFluent.AppendStage(pipe); } // Add Text search first if had if (!string.IsNullOrEmpty(fetchDataModel.TextSearch)) { aggregateFluent = aggregateFluent.Match(CombineTextSearch(fetchDataModel.TextSearch, dynamicList.FiltersList)); } // Add Filter Options if had if (fetchDataModel.FilterGroupOptions != null && fetchDataModel.FilterGroupOptions.FilterGroups != null && fetchDataModel.FilterGroupOptions.FilterGroups.Count > 0 && (fetchDataModel.FilterGroupOptions.FilterGroups[0].FilterOptions.Count > 0)) { aggregateFluent = aggregateFluent.AppendStage(PipelineStageDefinitionBuilder.Match(BuildFilters(fetchDataModel.FilterGroupOptions.FilterGroups))); } // Projection only columns var projectDoc = new BsonDocument(); foreach (var column in dynamicList.ColumnsList.ColumnDefs) { projectDoc.Add(new BsonElement(column.Name, 1)); } var projection = new BsonDocument { { "$project", projectDoc } }; aggregateFluent = aggregateFluent.AppendStage((PipelineStageDefinition <BsonDocument, BsonDocument>)projection); // Add Sort if had if (fetchDataModel.SortOptions.SortableFields != null && fetchDataModel.SortOptions.SortableFields.Count > 0) { var sortField = fetchDataModel.SortOptions.SortableFields[0]; FieldDefinition <BsonDocument, string> field = sortField.FieldName; var sortDefinition = sortField.SortType == SortType.Asc ? Builders <BsonDocument> .Sort.Ascending(field) : Builders <BsonDocument> .Sort.Descending(field); aggregateFluent = aggregateFluent.AppendStage(PipelineStageDefinitionBuilder.Sort(sortDefinition)); } if (fetchDataModel.PaginationOptions.NeedTotalItems) { var aggregateFluentForCountTotal = aggregateFluent.Count(); var totalItems = await aggregateFluentForCountTotal.FirstOrDefaultAsync(); dynamicListResponseDataModel.TotalItems = totalItems != null ? totalItems.Count : 0; } if (fetchDataModel.PaginationOptions.NeedTotalItems && dynamicListResponseDataModel.TotalItems > 0) { // Add Pagination aggregateFluent = aggregateFluent .Skip(fetchDataModel.PaginationOptions.PageNumber * fetchDataModel.PaginationOptions.PageSize) .Limit(fetchDataModel.PaginationOptions.PageSize); using (var executingCursor = await aggregateFluent.ToCursorAsync()) { while (executingCursor.MoveNext()) { var currentBson = executingCursor.Current; foreach (var item in currentBson) { var addedFields = new List <BsonElement>(); var removedFields = new List <string>(); foreach (var elem in item) { if (elem.Value.IsObjectId) { addedFields.Add(new BsonElement(elem.Name == "_id" ? "id" : elem.Name, BsonValue.Create(elem.Value.AsObjectId.ToString()))); removedFields.Add(elem.Name); } } foreach (var removedField in removedFields) { item.Remove(removedField); } foreach (var addedField in addedFields) { item.Add(addedField); } } // Important note: this query must have one row result for extracting params and filters dynamicListResponseDataModel.Data = currentBson .Select(a => a.ToJson( new MongoDB.Bson.IO.JsonWriterSettings { OutputMode = MongoDB.Bson.IO.JsonOutputMode.Strict })).Select(b => JsonConvert.DeserializeObject <dynamic>(b, new BsonConverter(GetFormatFields(dynamicList.ColumnsList.ColumnDefs)))).ToList(); } } } else if (!fetchDataModel.PaginationOptions.NeedTotalItems) { // Add Pagination aggregateFluent = aggregateFluent .Skip(fetchDataModel.PaginationOptions.PageNumber * fetchDataModel.PaginationOptions.PageSize) .Limit(fetchDataModel.PaginationOptions.PageSize); using (var executingCursor = await aggregateFluent.ToCursorAsync()) { while (executingCursor.MoveNext()) { var currentBson = executingCursor.Current; foreach (var item in currentBson) { var addedFields = new List <BsonElement>(); var removedFields = new List <string>(); foreach (var elem in item) { if (elem.Value.IsObjectId) { addedFields.Add(new BsonElement(elem.Name == "_id" ? "id" : elem.Name, BsonValue.Create(elem.Value.AsObjectId.ToString()))); removedFields.Add(elem.Name); } } foreach (var removedField in removedFields) { item.Remove(removedField); } foreach (var addedField in addedFields) { item.Add(addedField); } } // Important note: this query must have one row result for extracting params and filters dynamicListResponseDataModel.Data = currentBson .Select(a => a.ToJson( new MongoDB.Bson.IO.JsonWriterSettings { OutputMode = MongoDB.Bson.IO.JsonOutputMode.Strict })).Select(b => JsonConvert.DeserializeObject <dynamic>(b, new BsonConverter(GetFormatFields(dynamicList.ColumnsList.ColumnDefs)))).ToList(); } } } return(dynamicListResponseDataModel); }