Returning a list of income for a month

Question

My current code takes 2.7-4.5 seconds to complete, is there any way to make it faster?

I need to return a list of the income in all days in a month. My idea was to loop through the days of one month, but this takes so long should not be a hard task.

What can I improve? I was thinking on GroupBy a date range.

    public IEnumerable<GetSellInfo> GetSellInvoices(int month, int year, int offset)
{
    DateTime startDate = new DateTime(year, month, 1, 0, 0, 0).AddMinutes(offset);
    DateTime endDate = new DateTime(year, month, DateTime.DaysInMonth(year, month) , 23, 59, 59, 999).AddMinutes(offset);
    DateTime utc = DateTime.UtcNow.AddMinutes(-offset);
    DateTime maxDate = new DateTime(utc.Year, utc.Month, utc.Day, 23, 59, 59, 999).AddMinutes(offset);

    if (endDate > maxDate) endDate = maxDate;

    var invoices =
        db.SellInvoices.Where(invoice => invoice.Date >= startDate && invoice.Date <= endDate);

    List<GetSellInfo> result = new List<GetSellInfo>();
    DateTime counterDate = startDate;

    while (counterDate <= endDate)
    {
        DateTime iDate = new DateTime(counterDate.Year, counterDate.Month, counterDate.Day, 0, 0, 0).AddMinutes(offset);
        DateTime jDate = new DateTime(counterDate.Year, counterDate.Month, counterDate.Day, 23, 59, 59, 99).AddMinutes(offset);
        var dayInvoices = invoices.Where(invoice => invoice.Date >= iDate && invoice.Date <= jDate);

        decimal cfCard = dayInvoices.Select(x => x.CardSettlementSum).DefaultIfEmpty(0).Sum();
        decimal cfCash = dayInvoices.Select(x => x.CashSettlementSum).DefaultIfEmpty(0).Sum();
        decimal depositsCard = dayInvoices.Select(x => x.CardDepositSum).DefaultIfEmpty(0).Sum();
        decimal depositsCash = dayInvoices.Select(x => x.CashDepositSum).DefaultIfEmpty(0).Sum();
        decimal notSyledsDecimal = dayInvoices.Select(x => x.NotStyledsSum).DefaultIfEmpty(0).Sum();
        decimal discountsDecimal = dayInvoices.Select(x => x.DiscountsSum).DefaultIfEmpty(0).Sum();

        result.Add(new GetSellInfo
        {
            Date = counterDate,
            TotalCard = cfCard + depositsCard,
            TotalCash = cfCash + depositsCash,
            DepositsInCard = depositsCard,
            DepositsInCash = depositsCash,
            NotStyleds = notSyledsDecimal,
            Discounts = discountsDecimal,
            Returns = cfCard - depositsCard + cfCash + depositsCash,
            Total = cfCard + depositsCard + cfCash + depositsCash,
        });
        counterDate = counterDate.AddDays(1);
    }

    return result;
}

Answer 1

I'm going to elaborate on what I mentioned in comments.

Assuming you're using LINQ to Entities, this line:

var invoices =
        db.SellInvoices
          .Where(invoice => invoice.Date >= startDate && invoice.Date <= endDate);

does not actually execute the query. It just builds the query, execution is deferred until you call one of the methods that force the materialization of the result set. One of those methods is Sum.

That means you're calling the database 6 times per loop.

If you add .ToList():

var invoices =
        db.SellInvoices
          .Where(invoice => invoice.Date >= startDate && invoice.Date <= endDate)
          .ToList();

You'll just hit the database once, grab the full result set and the rest of the processing will be done in-memory.

In case of not-too-big datasets, that is definitely a viable way to go. Otherwise, you'd have to write a stored procedure to do the processing.