如何在C#控制台应用中限制Azure Email Communication的邮件发送量？

我正在开发一个控制台应用，从数据库的WorkExtended表读取记录，通过Azure Email Communication发送邮件。表结构如下：

业务规则 #

• 当ShouldEmailBeSent=1时，NextActionByUser分为两类：
  • ManagerUser：给对应经理发送单封邮件，每条SEID对应一封邮件
  • TechUser：按NextActionBy（团队）分组，每个团队发送一封邮件（不管该团队有多少条SEID）
• 设置maxEmailsToSend=4，要求严格限制发送邮件总数不超过该值。例如：发送SEID1（1封）后剩余3额度，发送CloudTeam团队邮件（1封）后已达4封限额，不应再发送AITeam的邮件。但当前代码仍会发送AITeam的邮件，且剩余额度不足时也会发送团队邮件。

当前代码 #

int maxEmailsToSend = 4;
using (SqlConnection connection = new SqlConnection(dbConnectionString))
{
    connection.Open();

    string query = "SELECT SEID, ShouldEmailBeSent,NextActionBy,NextActionByUser FROM WorkExtended " + "WHERE ShouldEmailBeSent = 'True' AND HasEmailBeenSent = 'False' AND EmailSentTime IS NULL ";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            Dictionary<string, List<int>> seidsByTeam = new Dictionary<string, List<int>>();
            int emailsSentCount = 0;
            while (reader.Read() && emailsSentCount < maxEmailsToSend)
            {

                int seid = Convert.ToInt32(reader["SEID"]);
                bool shouldemailbesent = Convert.ToBoolean(reader["ShouldEmailBeSent"]);
                string nextActionByUser = reader["NextActionByUser"].ToString();


                if (shouldemailbesent)
                {
                    if (nextActionByUser == "TechUser")
                    {
                        string teamName = GetTeamNameFromNextActionBy(connection, nextActionByUser, seid);
                        if (!seidsByTeam.ContainsKey(teamName))
                        {
                            seidsByTeam[teamName] = new List<int>();
                        }
                        seidsByTeam[teamName].Add(seid);
                    }
                    else
                    {
                        string managerId = GetManagerUserIdFromNextActionBy(connection, nextActionByUser, seid);
                        string managerEmail = GetEmailForManagerUser(connection, managerId);
                        if (emailsSentCount < maxEmailsToSend)
                        {
                            await SendEmailToRecipient(seid, managerEmail);
                            emailsSentCount++;
                            string updateQuery = "UPDATE WorkExtended SET HasEmailBeenSent = 'True', EmailSentTime = CURRENT_TIMESTAMP WHERE SEID = @SEID";

                            using (SqlCommand updateCommand = new SqlCommand(updateQuery, connection))
                            {
                                updateCommand.Parameters.AddWithValue("@SEID", seid);
                                updateCommand.ExecuteNonQuery();
                                Console.WriteLine("Updated SEID: " + seid);
                            }
                        }                                   
                    }
                }
            }
            foreach (var teamEntry in seidsByTeam)
            {
                string teamName = teamEntry.Key;
                List<int> seidsForTeam = teamEntry.Value;
                List<string> techUserEmails = GetEmailsForTechUsers(connection, teamName);
                if (emailsSentCount < maxEmailsToSend)
                {
                    int remainingEmails = maxEmailsToSend - emailsSentCount;
                    int emailsToSendForTeam = Math.Min(remainingEmails, seidsForTeam.Count);

                    if (emailsToSendForTeam > 0)
                    {
                        await SendGroupEmail(seidsForTeam.GetRange(0, emailsToSendForTeam), techUserEmails);
                        emailsSentCount += emailsToSendForTeam;
                    }
                    string updateQuery = "UPDATE WorkExtended SET HasEmailBeenSent = 'True', EmailSentTime = CURRENT_TIMESTAMP WHERE SEID = @SEIDs";

                    using (SqlCommand updateCommand = new SqlCommand(updateQuery, connection))
                    {
                        updateCommand.Parameters.AddWithValue("@SEIDs", string.Join(",", seidsForTeam.GetRange(0, emailsToSendForTeam)));
                        updateCommand.ExecuteNonQuery();
                        Console.WriteLine($"Updated SEIDs: {string.Join(", ", seidsForTeam.GetRange(0, emailsToSendForTeam))}");
                    }
                }
            }
        }
    }
    connection.Close();
}

问题根源 #

1. 数据读取逻辑错误：while (reader.Read() && emailsSentCount < maxEmailsToSend)的判断仅限制了ManagerUser的处理循环，但TechUser的记录会被全部读取并加入字典，即使已经达到邮件限额。
2. 团队邮件计数错误：代码中把团队对应的SEID数量当成邮件数累加，但实际每个团队只发送1封邮件，不是按SEID数量计数。

修复方案 #

修复后的代码

int maxEmailsToSend = 4;
using (SqlConnection connection = new SqlConnection(dbConnectionString))
{
    connection.Open();

    string query = @"SELECT SEID, ShouldEmailBeSent, NextActionBy, NextActionByUser 
                     FROM WorkExtended 
                     WHERE ShouldEmailBeSent = 'True' 
                       AND HasEmailBeenSent = 'False' 
                       AND EmailSentTime IS NULL";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // 先收集所有待处理记录
            List<int> managerSeids = new List<int>();
            Dictionary<string, List<int>> seidsByTeam = new Dictionary<string, List<int>>();

            while (reader.Read())
            {
                int seid = Convert.ToInt32(reader["SEID"]);
                bool shouldEmailBeSent = Convert.ToBoolean(reader["ShouldEmailBeSent"]);
                string nextActionByUser = reader["NextActionByUser"].ToString();

                if (shouldEmailBeSent)
                {
                    if (nextActionByUser == "TechUser")
                    {
                        // 直接从查询结果获取团队名称，减少额外查询
                        string teamName = reader["NextActionBy"].ToString();
                        if (!seidsByTeam.ContainsKey(teamName))
                        {
                            seidsByTeam[teamName] = new List<int>();
                        }
                        seidsByTeam[teamName].Add(seid);
                    }
                    else
                    {
                        managerSeids.Add(seid);
                    }
                }
            }

            int emailsSentCount = 0;

            // 处理ManagerUser邮件，严格控制限额
            foreach (int seid in managerSeids)
            {
                if (emailsSentCount >= maxEmailsToSend)
                    break;

                string managerId = GetManagerUserIdFromNextActionBy(connection, "ManagerUser", seid);
                string managerEmail = GetEmailForManagerUser(connection, managerId);
                
                await SendEmailToRecipient(seid, managerEmail);
                emailsSentCount++;

                // 更新数据库状态
                string updateQuery = "UPDATE WorkExtended SET HasEmailBeenSent = 'True', EmailSentTime = CURRENT_TIMESTAMP WHERE SEID = @SEID";
                using (SqlCommand updateCommand = new SqlCommand(updateQuery, connection))
                {
                    updateCommand.Parameters.AddWithValue("@SEID", seid);
                    updateCommand.ExecuteNonQuery();
                    Console.WriteLine("Updated SEID: " + seid);
                }
            }

            // 处理TechUser团队邮件，每个团队计1封邮件
            foreach (var teamEntry in seidsByTeam)
            {
                if (emailsSentCount >= maxEmailsToSend)
                    break;

                string teamName = teamEntry.Key;
                List<int> seidsForTeam = teamEntry.Value;
                List<string> techUserEmails = GetEmailsForTechUsers(connection, teamName);
                
                await SendGroupEmail(seidsForTeam, techUserEmails);
                emailsSentCount++;

                // 更新该团队所有SEID的状态
                string updateQuery = @"UPDATE WorkExtended 
                                      SET HasEmailBeenSent = 'True', EmailSentTime = CURRENT_TIMESTAMP 
                                      WHERE SEID IN (" + string.Join(",", seidsForTeam) + ")";
                using (SqlCommand updateCommand = new SqlCommand(updateQuery, connection))
                {
                    updateCommand.ExecuteNonQuery();
                    Console.WriteLine($"Updated SEIDs: {string.Join(", ", seidsForTeam)}");
                }
            }
        }
    }
    connection.Close();
}

关键修复点 #

1. 先收集数据再处理：避免读取时的计数判断导致数据遗漏，同时能按顺序严格控制邮件发送数量。
2. 修正团队邮件计数逻辑：每个团队发送1封邮件，计数加1，符合业务规则。
3. 提前终止循环：处理两类邮件时，一旦达到限额立即终止循环，不再处理后续内容。
4. 优化团队名称获取：直接从查询结果获取，减少不必要的数据库查询。

内容的提问来源于stack exchange，提问作者ani H