代码之家 › 专栏 › 技术社区 › Raas Masood

需要为图形数据构造一个干净的Linq查询

linq c#

Raas Masood · 技术社区 · 6 年前

我正在尝试填充过去两年的余额图表。例如,它的2020年,我想返回一个类似于

如果今天是2021年,我需要返回2019年和2020年的数据。

 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
}

我填充了这个结构,认为它会很简单。我有

var transactions = new ICollection<Transaction>

以下是2018年1月的种子数据示例

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

正如你在2018年1月看到的,增加了3334.38,减去了相同的金额,增加了1000.23,所以我应该在1月1000.23下得到2018

       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();

但在2018年1月

我试着按年分组,在这些分组中,我试着得到月和总余额。

1 回复 | 直到 6 年前

AliReza Sabouri 6 年前

    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