Why is my monthly logs not being generated?

ObedMarsh 0
Viewed 44

I have this piece of code:

if (DateTime.Now.Day == 1)
        {
            var customers = db.Customers.ToList();
            foreach (var customer in customers)
            {
                var logs = db.Logs.Where(x => x.CustomerId == customer.Id)
                    .Where(x => x.LogType == LogTypes.RecordLog)
                    .Where(x => DateTime.Compare(x.CreationDate, DateTime.Today.AddMonths(-1)) >= 0)
                    .Where(x => DateTime.Compare(x.CreationDate, DateTime.Today) < 0).ToList();

                var newLog = new Log()
                {
                    CustomerId = customer.Id,
                    LogType = LogTypes.MonthlyLog,
                    LogText = $"{customer.Title} har indsat {logs.Count} elementer imellem d.{DateTime.Now.AddMonths(-1).Date} og d.{DateTime.Now.AddDays(-1).Date}"
                };
                db.Logs.Add(newLog);
            }
            await db.SaveChangesAsync();
        }
        return true;
}

Basically it's running inside of a cron job that runs every day at midnight. It's supposed to work in the following way:

  1. Check if it's the first day of the month
  2. Get a list of all record logs dating one month back
  3. Make a monthly report for each customer and save it to the database

But for some reason no logs are being created? I've been looking at this piece of code endlessly and can't find my mistake, I need fresh eyes on it.

Thank in advance

asp.net entity-framework c#
1 Answers

I'd try running your query in a debugger, such as using a simple unit test to see if it is actually returning the information you are expecting. I would suspect there may be some issue with how you are reading your range. The other question to look into is how this code is being run and what happens in the event of an exception? Normally the first rule of logging is that the act of logging should not crash a system if something goes wrong, so are exceptions in this code being tracked/discoverable in any way?

I would recommend a few changes to improve performance considerably, and also update how you are filtering the last month's log entries:

Firstly, we don't need everything from the Customer table, just the ID and Title. Secondly, we don't need anything from the Logs table other than a Count. Optimizing these queries saves time and avoids EF having to materialize entities for no real purpose:

if (DateTime.Now.Day != 1) // avoid nesting, exit fast if we have nothing to do.
   return;

var customerDetails = db.Customers
    .Select(c => new 
    {
        c.Id,
        c.Title
    }).ToList();

var startDate = DateTime.Today.AddMonths(-1);
var endDate = DateTime.Today;

foreach (var customer in customerDetailss)
{
    var logCount = db.Logs
        .Where(x => x.CustomerId == customer.Id
            && x.LogType == LogTypes.RecordLog
            && x.CreationDate >= startDate
            && x.CreationDate < endDate)
       .Count();

    var newLog = new Log()
    {
        CustomerId = customer.Id,
        LogType = LogTypes.MonthlyLog,
        LogText = $"{customer.Title} har indsat {logCount} elementer imellem d.{startDate} og d.{endDate}"
    };
    db.Logs.Add(newLog);
}
await db.SaveChangesAsync();
return true;

This assumes you want a log entry for every customer even if they have 0 logs. If you don't need to bother writing 0 count entries, you can make this query process even more efficient and have a Customer navigation property available in your Log entity you can query the Logs table grouping by the Customer details (Id & Title) then get the counts that way all within a single query rather than a query + one query per customer.

Something like:

var logCountDetails = db.Logs
    .Where(x => x.LogType == LogTypes.RecordLog
            && x.CreationDate >= startDate
            && x.CreationDate < endDate)
    .GroupBy(x => new {x.Customer.Id, x.Customer.Title})
    .Select(g => new 
    {
        CustomerId = g.Key.Id,
        g.Key.Title,
        LogCount = g.Count()
    }).ToList();

Then you literally write your log record using the details in logCountDetails:

foreach(var logDetail in logCountDetails)
{
    var newLog = new Log()
    {
        CustomerId = logDetail.CustomerId,
        LogType = LogTypes.MonthlyLog,
        LogText = $"{logDetail.Title} har indsat {logDetail.LogCount} elementer imellem d.{startDate} og d.{endDate}"
    };
    db.Logs.Add(newLog);
}

Hopefully that gives you some ideas to try, and maybe a more efficient logging summary process.

Steve Py0
Related