EF Core 5 with Sum

Question

Repost model class:

public class TransactionDayReport
{
    public Decimal TotalAmount { get; set; }
    public ICollection<TransactionResponse> TransactionResponses { get; set; } = null!;
}

This is my repository method

public async Task<IEnumerable<Transaction>> SearchTransactionAsync(SearchTransaction search)
{
    var query = _context.Transactions
                        .Include(t => t.Branch)
                        .Include(t => t.Customer)
                        .AsQueryable();    

    if (search.CIN != null)               
        query = query.Where(t => t.Customer.CIN.Contains(search.CIN));

    if (search.ValueDate != null)
        query = query.Where(t => t.ValueDate.Date == search.ValueDate.Date);

    return await query.ToListAsync();
}

Service method:

public async Task<TransactionResponse> SearchTransactions(SearchTransaction search)
{
    var transactionList = await _unitOfWork.Transactions.SearchTransactionAsync(search);
    var mapped = ObjectMapper.Mapper.Map<TransactionDayReport>(transactionList);
    return mapped;
}

I need to send total amount in service class..

am looking similar to this

But I am confused how to get addition sum added to my dto. Please can anyone suggest a solution? Thanks

Answer 1

The only way to also calculate the sum on the DB is to run another query on the DB, unless the DB query can be re-written to return both the sum and the data.

If you're ok with calculating the Sum on the client, it's more straightforward. Here are some options I could think of:

1. You could configure the mapper to set the sum.
2. You could set the sum after performing the mapping in SearchTransactions().
3. You could change the DTO to calculate the Sum (in which case it wouldn't be a POCO anymore, but I think this is a reasonable approach too):

   public class TransactionDayReport
   {
       private decimal? _totalAmount = null;
       public decimal TotalAmount { get {
           if(_totalAmount is not null) return _totalAmount; // new C# 9 feature :)
           _totalAmount = // calculate total amount here
           return _totalAmount;
       }
       public ICollection<TransactionResponse> TransactionResponses { get; set; } = null!;
   }