C# XML LINQ GroupBy from CSV File

Question

I am in the process of generating XML from a CSV file.

In the picture I attach the result I am waiting for

using (var reader = new StreamReader(csvPath + csvName))
{
    using (var csv = new CsvReader(reader))
    {

        csv.Configuration.HasHeaderRecord = true;
        csv.Configuration.RegisterClassMap<MapClass>();
        var records = csv.GetRecords<Data>().ToList();

         from w in records
         select

         // group by invoice number ?

        new XElement("INVOICE",
            new XAttribute("DATE",w._invoiceDate ),
            new XAttribute("NUMBER", w._invoiceNumber),
            new XAttribute("QTY", w._orderQty)  

        }
}

I want to display the data grouped by the invoice number with the amounts added up (equivalent to a pivot table). Can I ask you for a tip for me?

using (var reader = new StreamReader(csvPath + csvName))
            {
                using (var csv = new CsvReader(reader))
                {

                    csv.Configuration.HasHeaderRecord = true;
                    csv.Configuration.RegisterClassMap<MapClass>();
                    List<Data> records = csv.GetRecords<Data>().ToList();


                    var result = records.
                        GroupBy(x => x._invoiceNumber).
                        Select(x => new
                        {
                            // IGrouping does't contain 'Value" definition
                            InvoiceNumber = x.Key,
                            InvoiceQty = x.Value.Sum(y => y._orderQty),
                            InvoiceTotal = x.Value.Sum(y => y._invoiceTotal)
                        }).ToList();
                }

            }

`group record by record.InvoiceNumber into w`? What exactly is your problem? — MakePeaceGreatAgain, Nov 20 '19 at 09:48
I want to group a data by record.w._invoiceNumber and Sum w._orderQty for each Invoice — enginne, Nov 20 '19 at 09:53
See the docs: https://learn.microsoft.com/dotnet/csharp/language-reference/keywords/group-clause — MakePeaceGreatAgain, Nov 20 '19 at 09:55
Possible duplicate of https://stackoverflow.com/questions/7325278/group-by-in-linq — MakePeaceGreatAgain, Nov 20 '19 at 10:00
Does this answer your question? [Linq: GroupBy, Sum and Count](https://stackoverflow.com/questions/16522645/linq-groupby-sum-and-count) — xdtTransform, Nov 20 '19 at 10:07
Btw you may want to declare your variable outside of the using block and only keep the `GetRecords` in it. Right now the Using is unnecesseray waiting to dispose the resource. ` List records;` is no cost. — xdtTransform, Nov 20 '19 at 10:10
https://stackoverflow.com/questions/178899/serializing-lists-of-classes-to-xml — xdtTransform, Nov 20 '19 at 10:18

Arjun Vachhani · Answer 1 · 2019-11-20T12:08:43.700

0

below code should give to data of table you expect, further you can generate XML you need

var result = records.GroupBy(x => x._invoiceNumber).Select(x => new { InvoiceNumber = x.Key, InvoiceQty = x.Sum(y => y._orderQty), InvoiceTotal = x.Sum(y => y._invoiceTotal) }).ToList()

edited Nov 20 '19 at 12:08

answered Nov 20 '19 at 10:00

Arjun Vachhani

1,761
3
21
44

I got information that in not allowed convert lambda to deletage – enginne Nov 20 '19 at 10:46
clarify, are you getting any error or exception with proper message. – Arjun Vachhani Nov 20 '19 at 10:49
GroupBy return Key Value structure. so Value is collection of Invoice for specific group – Arjun Vachhani Nov 20 '19 at 11:45
Ok , but I have a compile error with 'Value'. Do you have any idea ? I added this code above – enginne Nov 20 '19 at 11:51

score 0 · Answer 2 · answered Nov 20 '19 at 14:11

We can close this topic. I found a solution

var res =   from e in records
                        group e by e._invoiceNumber into g
                        where g.Count() >= 1
                        select new
                        {
                            Invoice = g.Key,
                            Total = g.Count(),
                            TotalQty = g.Sum(x => Convert.ToInt32( x._orderQty.ToString()))
                        };


                        foreach(var item in res)
                        {
                            Console.WriteLine($" {item.Invoice}  :   {item.TotalQty}  ");
                        }

C# XML LINQ GroupBy from CSV File

2 Answers2