Grouping not working as expected returning more emails than should

Question

I have a sales order table with 23 transaction they are assigned to either department 1 or 2 I have looked at the data and its has the right assigns to it based on the cateory name however the problem is in my exucuation of the loop as I am getting 23 emails instead of just 5 sales order emails which is what it should be

Say for Example The table is

SalesOrder Number  Depart
 1111              1
 1111              2
 2222              2
 2222              2 

I should be getting one email for sales order 1111 sent to department 1 and one sent to department 2 but in the 2222 case I should get one email including all 2222

I think the issue is the group by does not no how to batch and I am asking what is the best way of doing that.

public void ProcessTransactions(string csvFileName)
{
        var engine = new FileHelperEngine<SalesOrderHeader>();
        var SalesOrders = engine.ReadFile(csvFileName);
        var engine2 = new FileHelperEngine<SalesOrdersLines>();

        var OrderLines = engine2.ReadFile(csvFileName);

        GetSalesOrdersForImport();
        ImportTransActions(SalesOrders.ToList());


        CreateSalesOrder(_salesOrders.ToList(), _salesOrders.ToList());


        var groupedSalesOrders = SalesOrders.OrderBy(x => x.SalesOrderNumber)
       .GroupBy(x => x.SalesOrderNumber);

        foreach(var group in groupedSalesOrders)
        {

            foreach (var item in group)
            {

                GetEmailsFromDepartment(item.DepartmentId);
                GetSalesOrdersByDepartment(item.DepartmentId);
                SendEmailNotificationPerDepartments(item.SalesOrderNumber.ToString());
            }        
        }
}

My Get Emails for Department function is as below

public List<EmailDepartMents> _emailListsByDepartment { get; set; }
public void GetEmailsFromDepartment(string departmentId )
{            

                string connectionString = ConfigurationManager.AppSettings["connectionString"];

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string selectQuery = @"SELECT [Code]
      ,[Name]
      ,[U_DepartmentId] AS DepartmentId
      ,[U_CardCode] as CardCode
      ,[U_Email] As Email
  FROM [NKCoatings].[dbo].[@FIT_DEPARTMENTS]
  where [U_DepartmentId]='" + departmentId +"'";

                    _emailListsByDepartment = connection.Query<EmailDepartMents>(selectQuery).ToList();
                }
            }


}

Edit 2 To Show send email function in case there is a issue with it in it self.

public void SendEmailNotificationPerDepartments(List SalesOrders) { try {

            SAPbobsCOM.UserTable sboTable = (SAPbobsCOM.UserTable)company.UserTables.Item("DEPARTMENTS");

            SAPbobsCOM.BusinessPartners sboBP = (SAPbobsCOM.BusinessPartners)company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oBusinessPartners);
            string emailAddressCC = ConfigurationManager.AppSettings["EmailAddressTo"];

            string body;
            string stmpServer = ConfigurationManager.AppSettings["SmtpAddress"];
            string EmailUserName = ConfigurationManager.AppSettings["EmailUserName"];
            string EmailPassword = ConfigurationManager.AppSettings["EmailPassword"];
            string SmtpPort = ConfigurationManager.AppSettings["SmtpPort"];
            MailMessage Msg = new MailMessage();

            Msg.From = new MailAddress("development@test.com");
            Msg.IsBodyHtml = true;

            Msg.Subject = "Sales Orders Created in SAP";
            body = "Sales orders has been imported into sap";

            StringBuilder sb = new StringBuilder();

            using (Html.Table table = new Html.Table(sb, id: "some-id"))
            {
                table.StartHead();
                using (var thead = table.AddRow())
                {
                    thead.AddCell("Works Order Number");
                    thead.AddCell("Purchase Order Number");
                    thead.AddCell("Date Required");
                    thead.AddCell("Stock Item Code");
                    thead.AddCell("Stock Item Name");
                    thead.AddCell("Customer");
                }
                table.EndHead();
                table.StartBody();

                foreach (var order in SalesOrders.Where(w=>w.DepartmentId == DepartmentId && w.SalesOrderNumber ==salesOrderId).OrderBy(o=>o.SalesOrderNumber))
                {


                    using (var tr = table.AddRow(classAttributes: "someattributes"))
                    {
                        tr.AddCell(order.WorksOrderNumber, "style:font-bold;");
                        tr.AddCell(order.PurchaseOrderNumber.ToString());
                        tr.AddCell(order.DateRequired.ToString());
                        tr.AddCell(order.ItemCode.ToString());
                        tr.AddCell(order.Description.ToString());
                        if(sboBP.GetByKey(order.CardCode))
                        {
                            sboBP.CardName.ToString();

                        } 


                    }
                }
            }



            foreach (var address in _emailListsByDepartment)
            {
                Msg.To.Add(address.Email);
            }


            foreach (var address in emailAddressCC.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries))
            {
                Msg.CC.Add(address);
            }

            body = body + Environment.NewLine + sb.ToString();
            Msg.Body = body;

            SmtpClient smtp = new SmtpClient(stmpServer);
            smtp.Credentials = new NetworkCredential(EmailUserName, EmailPassword);
            smtp.Host = stmpServer;
            smtp.Port = Convert.ToInt16(SmtpPort);
            smtp.Send(Msg);
        }
        catch (Exception ex)
        {
            log.Error("Error has occoured at the send email notification " + ex.ToString());
        }


 }

I think i am just having a saturday night black out here but I hope someone can help me out maybe I am doing something wrong.

Answer 1

It could look something like this:

        var list = new List<Email>()
        {
            new Email() {SalesOrderNumber = 10, Depart = 1},
            new Email() {SalesOrderNumber = 10, Depart = 2},
            new Email() {SalesOrderNumber = 20, Depart = 2},
            new Email() {SalesOrderNumber = 20, Depart = 2},
        };
        var groups = list.GroupBy(e => e.SalesOrderNumber) // sort all emails by SalesOrderNumber
            .Select(g => g.GroupBy(e => e.Depart)) // sort groups by Depart
            .Aggregate((l, r) => l.Concat(r)); // aggregate result to only one collection of groups
        foreach (var group in groups)
        {
            Console.WriteLine($"Group of SalesOrderNumber: {group.First().SalesOrderNumber}, Depart: {group.Key}");
            foreach (var email in group)
            {
                Console.WriteLine(email);
            }
        }