Problem : after emails are sent(I check Mailhog inbox) using background job, they are sent in many times instead of once.

In DB, if an email record has 'IsSent' property set to false, it should be sent. Problem is, isSent is not marked as true even after it is sent.

I tried to manually set the 'IsSent' value to true but didn't work. 'EmailSentAt' is not set either.

When I run the function manually, it works. Values are successfully updated.

SELECT public.udf_UpdateEmailView(
    p_is_sent => TRUE,
    p_email_sent_at => NOW(),
    p_email_ids => ARRAY[1, 2]
);

Another problem is, for other reasons, I can only run this in Docker and can't debug locally in any IDE. So, I am playing sort of a guessing game here.

Repo method :

public async Task BulkUpdateEmailStatusAsync(List<int> emailIds, bool isSent, CancellationToken cancellationToken)
{
    var sql = @"
        SELECT public.udf_UpdateEmailView(
            p_is_sent => @p_is_sent,
            p_email_sent_at => @p_email_sent_at,
            p_email_ids => @p_email_ids
        );
    ";

    var emailSentAt = isSent ? (object)DateTimeOffset.UtcNow : DBNull.Value;

    await DbContext.Database.ExecuteSqlRawAsync(
        sql,
        new NpgsqlParameter("@p_is_sent", NpgsqlTypes.NpgsqlDbType.Boolean) { Value = isSent },
        new NpgsqlParameter("@p_email_sent_at", NpgsqlTypes.NpgsqlDbType.TimestampTz) { Value = emailSentAt }, //I set this to true. Still nothing
        new NpgsqlParameter("@p_email_ids", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Integer) { Value = emailIds.ToArray() }
    );
}

The EmailSendingJob runs in background and is sending these emails :

public async Task ExecuteAsync(CancellationToken cancellationToken)
{
const int batchSize = 50;
const int maxEmptyFetchCount = 5;

int emptyFetchCount = 0;

try
{
    while (true)
    {
        var pendingEmails = await _emailRepository.GetPendingEmailsAsync(batchSize, cancellationToken);

        if (!pendingEmails.Any())
        {
            emptyFetchCount++;

            if (emptyFetchCount >= maxEmptyFetchCount)
            {
                break;
            }
            continue;
        }

        emptyFetchCount = 0;

        var groupedEmails = pendingEmails
            .GroupBy(email => new { email.Subject, email.Body })
            .ToList();

        foreach (var group in groupedEmails)
        {
            var recipients = group
                .SelectMany(email => email.To.Split(';', StringSplitOptions.TrimEntries | StringSplitOptions.RemoveEmptyEntries))
                .Distinct()
                .ToArray();

            var validRecipients = new List<string>();
            foreach (var recipient in recipients)
            {
                if (string.IsNullOrEmpty(recipient))
                {
                    continue;
                }

                try
                {
                    var mailAddress = new System.Net.Mail.MailAddress(recipient);
                    validRecipients.Add(mailAddress.Address);
                }
                catch (FormatException)
                {
                }
            }

            if (!validRecipients.Any())
            {
                continue;
            }

            try
            {
                var successfullySentEmails = await _emailSenderService.SendEmailAsync(validRecipients.ToArray(), group.Key.Subject, group.Key.Body);

                if (successfullySentEmails.Any())
                {
                    var emailIdsToUpdate = group
                        .Where(email => successfullySentEmails.Contains(email.To))
                        .Select(email => email.Id)
                        .ToList();

                    await _emailRepository.BulkUpdateEmailStatusAsync(emailIdsToUpdate, true, cancellationToken);
                }
            }
            catch (Exception ex)
            {
            }
        }
    }
}

I have added while(true) because this is a bulk update and if batch size is 50 and there are 100 mails, all 100 should be sent.