CODEWITHSUNDEEP
c#mongodbaggregation-frameworkmongodb-.net-driver
Mark Poliatskin
Mark Poliatskin

Reputation: 53

MongoDB .NET Driver - Aggregation query with calculation based on category

I want to calculate monthly income and monthly spending. My DB collection "transactions" consists of documents, here is TransactionModel:

public record TransactionModel  
{
     public ObjectId Id { get; set; }
     public double Price { get; set; }
     public string Category { get; set; }
     public DateTime Date { get; set; }
     ....
}

I am using myCollection.Aggregate().Match(filter) and it filters my list by date (and it works). Still, I couldn't find the correct syntax to group all the filtered transactions into two groups: "Income" which is Category == "Income" and "MonthlySpent" which is all rest categories and then calculate the sum of each group.

I am trying to find a solution the "C# way". Thanks!

Here is what I came up with so far: After I filtered the transactions by date, I tried to use projection to add a property which will tell me for each transaction if its category is income or not. Im kinda stuck from here.

var monthlyTransactions = await transactionCollection.Aggregate()
    .Match(monthlyNonIncomefilter)
    .Project(priceProjection)
    .ToListAsync();

This is the monthly filter & the projection:

var monthlyNonIncomefilter = filterBuilder.Gte(x => x.Date, fromDate) &
    filterBuilder.Lte(x => x.Date, toDate);

var priceProjection = projectionBuilder.Expression(u => new
{
    Price = u.Price,
    Type = u.Category == "Income" ? "Income" : "MonthlySpent"
});

Upvotes: 0

Views: 462

Answers (1)

Yong Shun
Yong Shun

Reputation: 51125

Late to the answer.

You need a $group stage to group the documents by Category and perform the sum for the Price.

For MongoDB .NET driver, you can achieve with IAggregateFluentExtensions.Group<TResult, TKey, TNewResult> Method (IAggregateFluent, Expression<Func<TResult, TKey>>, Expression<Func<IGrouping<TKey, TResult>, TNewResult>>).

var monthlyTransactions = await transactionCollection.Aggregate()
    .Match(monthlyNonIncomefilter)
    .Group(
        x => x.Category == "Income" ? "Income" : "MonthlySpent",
        group => new
        {
            Type = group.Key,
            Price = group.Sum(x => x.Price)
        })
    .ToListAsync();

Demo

enter image description here

Upvotes: 2

Related Questions