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

Question

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

Yong Shun · Accepted Answer · 2022-11-27 13:40:26Z

2

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

answered Nov 27, 2022 at 13:40

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jwags Over a year ago

Thank you! I needed the count so my group function was a little different. It was this .Group(x => x.MyField, group => new { MyField = group.Key, Count = group.Count() })

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related