CODEWITHSUNDEEP
c#linq
Raas Masood
Raas Masood

Reputation: 1575

need to construct a clean Linq query for Graph Data

i am trying to populate a graph of balances over the last 2 years. For example its 2020 i want to return a structure like

enter image description here

if it would have been 2021 today i need to returns data of 2019 and 2020.

my class structure looks like this

 public class Transaction : BaseEntity
{
    public TransactionType TransactionType { get; set; }
    public DateTime TransactionDate { get; set; }
    public double TransactionAmount { get; set; }
}
public enum TransactionType{
    Deposit = 0,
    Withdraw = 1
}

i populated this structure and thought it will be simple. i have

var transactions = new ICollection<Transaction>

here is an example seed data of January of 2018

 modelBuilder.Entity<Transaction>(b =>
        {
            b.HasData(new
            {
                Id = Guid.NewGuid().ToString(),
                AccountId = "37846734-172e-4149-8cec-6f43d1eb3f60",
                TransactionAmount = 3334.38,
                TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 1, 20),
                TransactionType = TransactionType.Deposit
            });
            b.HasData(new
            {
                Id = Guid.NewGuid().ToString(),
                AccountId = "37846734-172e-4149-8cec-6f43d1eb3f60",
                TransactionAmount = -3334.38,
                TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 1, 21),
                TransactionType = TransactionType.Withdraw

            });
            b.HasData(new
            {
                Id = Guid.NewGuid().ToString(),
                AccountId = "37846734-172e-4149-8cec-6f43d1eb3f60",
                TransactionAmount = 1000.23,
                TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 1, 25),
                TransactionType = TransactionType.Deposit
            });

As you can see in January of 2018 3334.38 was added and The same amount was subtracted and 1000.23 was added so i should get 2018 under that January 1000.23

       var transactions = await _unitOfWork.TransactionRepositoy.GetAllAsync();
        transactions.GroupBy(x => x.TransactionDate.Year);

        var data = transactions.Select(k => new { k.TransactionDate.Year, k.TransactionDate.Month, k.TransactionAmount }).GroupBy(x => new { x.Year, x.Month }, (key, group) => new
        {
            yr = key.Year,
            mnth = key.Month,
            tBalance = group.Sum(k => k.TransactionAmount)
        }).ToList();

but in january of 2018 i am getting

enter image description here

I am trying to group by year and in those group i am trying to get Month and Total Balance.

i have a group on month and group.Sum(k => k.TransactionAmount) seems to be not working.

Upvotes: 0

Views: 118

Answers (1)

AliReza Sabouri
AliReza Sabouri

Reputation: 5265

    var transactions = new List<Transaction>()
    {
        new Transaction() {
            TransactionAmount = 3334.38,
            TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 1, 20),
            TransactionType = TransactionType.Deposit
        },
        new Transaction() {
            TransactionAmount = -3334.38,
            TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 1, 21),
            TransactionType = TransactionType.Withdraw
        },
        new Transaction() {
            TransactionAmount = 1000.23,
            TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 1, 25),
            TransactionType = TransactionType.Deposit
        },
    };

    var data = from t in transactions
               group t by new {t.TransactionDate.Year , t.TransactionDate.Month} into g
               select new {
                 tBalance = g.Sum(x => x.TransactionAmount),
                 g.First().TransactionDate.Month,
                 g.First().TransactionDate.Year
               };
// ----------------------
// result :
// tBalance 1000.23
// Month 1
// Year 2018

Upvotes: 1

Related Questions