Grouping and collect nested object with C# Linq

Question

happy coding. I can group and sum with linq but this is a bit different. I want to group the elements and gather them one by one. I wanna group by TaxTypeCode and collect TaxAmount values. I'm sorry it's such a long question.

My xml

   <Note>
    <NoteLine>
        <cbc:ID>1</cbc:ID>
        <Quantity unitCode="C62">1.0000</Quantity>
        <TaxTotal>
            <cbc:TaxAmount currencyID="USD">201.00</cbc:TaxAmount>
            <TaxSubtotal>
                <cbc:TaxableAmount currencyID="USD">500.00</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="USD">100.00</cbc:TaxAmount>
                <cbc:Percent>25.00</cbc:Percent>
                <TaxCategory>
                    <TaxScheme>
                        <cbc:TaxTypeCode>0003</cbc:TaxTypeCode>
                    </TaxScheme>
                </TaxCategory>
            </TaxSubtotal>
            <TaxSubtotal>
                <cbc:TaxableAmount currencyID="USD">500.00</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="USD">50.00</cbc:TaxAmount>
                <cbc:Percent>10.00</cbc:Percent>
                <TaxCategory>
                    <TaxScheme>
                        <cbc:TaxTypeCode>9040</cbc:TaxTypeCode>
                    </TaxScheme>
                </TaxCategory>
            </TaxSubtotal>
            <TaxSubtotal>
                <cbc:TaxableAmount currencyID="USD">500.00</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="USD">1.00</cbc:TaxAmount>
                <cbc:Percent>1.00</cbc:Percent>
                <TaxCategory>
                    <TaxScheme>
                        <cbc:TaxTypeCode>0001</cbc:TaxTypeCode>
                    </TaxScheme>
                </TaxCategory>
            </TaxSubtotal>
            <TaxSubtotal>
                <cbc:TaxableAmount currencyID="USD">500.00</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="USD">50.00</cbc:TaxAmount>
                <cbc:Percent>10.00</cbc:Percent>
                <TaxCategory>
                    <TaxScheme>
                        <cbc:TaxTypeCode>8001</cbc:TaxTypeCode>
                    </TaxScheme>
                </TaxCategory>
            </TaxSubtotal>
        </TaxTotal>
    </NoteLine>
    <NoteLine>
        <cbc:ID>2</cbc:ID>
        <Quantity unitCode="C62">1.0000</Quantity>
        <TaxTotal>
            <cbc:TaxAmount currencyID="USD">460.00</cbc:TaxAmount>
            <TaxSubtotal>
                <cbc:TaxableAmount currencyID="USD">1000.00</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="USD">200.00</cbc:TaxAmount>
                <cbc:Percent>20.00</cbc:Percent>
                <TaxCategory>
                    <TaxScheme>
                        <cbc:TaxTypeCode>0003</cbc:TaxTypeCode>
                    </TaxScheme>
                </TaxCategory>
            </TaxSubtotal>
            <TaxSubtotal>
                <cbc:TaxableAmount currencyID="USD">1000.00</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="USD">100.00</cbc:TaxAmount>
                <cbc:Percent>10.00</cbc:Percent>
                <TaxCategory>
                    <TaxScheme>
                        <cbc:TaxTypeCode>9040</cbc:TaxTypeCode>
                    </TaxScheme>
                </TaxCategory>
            </TaxSubtotal>
            <TaxSubtotal>
                <cbc:TaxableAmount currencyID="USD">1000.00</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="USD">100.00</cbc:TaxAmount>
                <cbc:Percent>10.00</cbc:Percent>
                <TaxCategory>
                    <TaxScheme>
                        <cbc:TaxTypeCode>8001</cbc:TaxTypeCode>
                    </TaxScheme>
                </TaxCategory>
            </TaxSubtotal>
            <TaxSubtotal>
                <cbc:TaxableAmount currencyID="USD">600.00</cbc:TaxableAmount>
                <cbc:TaxAmount currencyID="USD">60.00</cbc:TaxAmount>
                <cbc:Percent>10.00</cbc:Percent>
                <TaxCategory>
                    <TaxScheme>
                        <cbc:TaxTypeCode>8001</cbc:TaxTypeCode>
                    </TaxScheme>
                </TaxCategory>
            </TaxSubtotal>
        </TaxTotal>
    </NoteLine> 
</Note>

Then we need something like this. Totals taxamount, taxableamount, taxamount. I think this is possible, but how can I do it?

The result I want

 <TaxTotal>
    <TaxAmount currencyID="USD">661.00</TaxAmount>
    <TaxSubtotal>
        <TaxableAmount currencyID="USD">1500.00</TaxableAmount>
        <TaxAmount currencyID="USD">300.00</TaxAmount>
        <Percent>25.00</Percent>
        <TaxCategory>
            <TaxScheme>
                <TaxTypeCode>0003</TaxTypeCode>
            </TaxScheme>
        </TaxCategory>
    </TaxSubtotal>
    ...
    ...
    ...
    ...
</TaxTotal>

I have a create sample class for serialize xml.

using (StreamReader reader = new StreamReader(@"C:\Users\test.xml"))
 {
       XmlSerializer serializer = new XmlSerializer(typeof(Note));
       Note obj = (Note)serializer.Deserialize(reader);
 }

But I can't do it still.

var groups = NoteItem.NoteLine .GroupBy(a => a.TaxTotal.TaxSubtotal.GroupBy(g => g.TaxCategory.TaxScheme.Name)) .Select(a => new TaxTotal() { // the last point I can come }).ToList(); the last point I can come. How can i do it? — arthur, Apr 11 '20 at 09:26
I think it's impossible this. I might be the only one in the world who wants it. — arthur, Apr 11 '20 at 10:14
Please [edit] your question if you have new information. It would help to see the classes and maybe data in tabular form instead of XML. That also shows that you are querying a class model and not an XML file. — Gert Arnold, Apr 11 '20 at 12:57

jdweng · Answer 1 · 2020-04-11T14:07:10.153

Try following. It is possible. Preserved all the namespaces. the trick is to create a template using the First() method. :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        const string FILENAME = @"c:\temp\test.xml";
        static void Main(string[] args)
        {
            XDocument doc = XDocument.Load(FILENAME);

            XElement note = doc.Descendants().Where(x => x.Name.LocalName == "Note").FirstOrDefault();
            XNamespace nsCbc = note.GetNamespaceOfPrefix("cbc");
            XNamespace ns = note.GetDefaultNamespace();

            foreach (XElement taxTotal in note.Descendants(ns + "TaxTotal"))
            {
                var groups = taxTotal.Elements(ns + "TaxSubtotal").GroupBy(x => (string)x.Descendants(nsCbc + "TaxTypeCode").First()).Select(x => new {
                    template = x.First(),
                    taxAmount = x.Descendants(nsCbc + "TaxableAmount").Sum(z => (decimal)z),
                    tax = x.Descendants(nsCbc + "TaxAmount").Sum(z => (decimal)z)
                }).ToList();

                foreach (var group in groups)
                {
                    group.template.SetElementValue(nsCbc + "TaxableAmount", group.taxAmount);
                    group.template.SetElementValue(nsCbc + "TaxAmount", group.tax);
                    group.template.SetElementValue(nsCbc + "Percent", 100 * (group.tax / group.taxAmount));

                }
                List<XElement> newSubtotals = groups.Select(x => new XElement(x.template)).ToList();
                taxTotal.Elements(ns + "TaxSubtotal").Remove();
                taxTotal.Add(newSubtotals);
            }


        }
    }
}

He is the output XML

<?xml version="1.0" encoding="utf-8"?>
<Note xmlns:cbc="abc">
  <NoteLine>
    <cbc:ID>1</cbc:ID>
    <Quantity unitCode="C62">1.0000</Quantity>
    <TaxTotal>
      <cbc:TaxAmount currencyID="USD">201.00</cbc:TaxAmount>
      <TaxSubtotal>
        <cbc:TaxableAmount currencyID="USD">500.00</cbc:TaxableAmount>
        <cbc:TaxAmount currencyID="USD">100.00</cbc:TaxAmount>
        <cbc:Percent>20.0</cbc:Percent>
        <TaxCategory>
          <TaxScheme>
            <cbc:TaxTypeCode>0003</cbc:TaxTypeCode>
          </TaxScheme>
        </TaxCategory>
      </TaxSubtotal>
      <TaxSubtotal>
        <cbc:TaxableAmount currencyID="USD">500.00</cbc:TaxableAmount>
        <cbc:TaxAmount currencyID="USD">50.00</cbc:TaxAmount>
        <cbc:Percent>10.0</cbc:Percent>
        <TaxCategory>
          <TaxScheme>
            <cbc:TaxTypeCode>9040</cbc:TaxTypeCode>
          </TaxScheme>
        </TaxCategory>
      </TaxSubtotal>
      <TaxSubtotal>
        <cbc:TaxableAmount currencyID="USD">500.00</cbc:TaxableAmount>
        <cbc:TaxAmount currencyID="USD">1.00</cbc:TaxAmount>
        <cbc:Percent>0.200</cbc:Percent>
        <TaxCategory>
          <TaxScheme>
            <cbc:TaxTypeCode>0001</cbc:TaxTypeCode>
          </TaxScheme>
        </TaxCategory>
      </TaxSubtotal>
      <TaxSubtotal>
        <cbc:TaxableAmount currencyID="USD">500.00</cbc:TaxableAmount>
        <cbc:TaxAmount currencyID="USD">50.00</cbc:TaxAmount>
        <cbc:Percent>10.0</cbc:Percent>
        <TaxCategory>
          <TaxScheme>
            <cbc:TaxTypeCode>8001</cbc:TaxTypeCode>
          </TaxScheme>
        </TaxCategory>
      </TaxSubtotal>
    </TaxTotal>
  </NoteLine>
  <NoteLine>
    <cbc:ID>2</cbc:ID>
    <Quantity unitCode="C62">1.0000</Quantity>
    <TaxTotal>
      <cbc:TaxAmount currencyID="USD">460.00</cbc:TaxAmount>
      <TaxSubtotal>
        <cbc:TaxableAmount currencyID="USD">1000.00</cbc:TaxableAmount>
        <cbc:TaxAmount currencyID="USD">200.00</cbc:TaxAmount>
        <cbc:Percent>20.0</cbc:Percent>
        <TaxCategory>
          <TaxScheme>
            <cbc:TaxTypeCode>0003</cbc:TaxTypeCode>
          </TaxScheme>
        </TaxCategory>
      </TaxSubtotal>
      <TaxSubtotal>
        <cbc:TaxableAmount currencyID="USD">1000.00</cbc:TaxableAmount>
        <cbc:TaxAmount currencyID="USD">100.00</cbc:TaxAmount>
        <cbc:Percent>10.0</cbc:Percent>
        <TaxCategory>
          <TaxScheme>
            <cbc:TaxTypeCode>9040</cbc:TaxTypeCode>
          </TaxScheme>
        </TaxCategory>
      </TaxSubtotal>
      <TaxSubtotal>
        <cbc:TaxableAmount currencyID="USD">1600.00</cbc:TaxableAmount>
        <cbc:TaxAmount currencyID="USD">160.00</cbc:TaxAmount>
        <cbc:Percent>10.0</cbc:Percent>
        <TaxCategory>
          <TaxScheme>
            <cbc:TaxTypeCode>8001</cbc:TaxTypeCode>
          </TaxScheme>
        </TaxCategory>
      </TaxSubtotal>
    </TaxTotal>
  </NoteLine>
</Note>

I just cam up with better solution and replace my original answer. — jdweng, Apr 11 '20 at 12:15
nice work but totals don't work. I edited the file and the question. — arthur, Apr 11 '20 at 13:13
I tested with your xml and totals are correct. You had some math errors in the percentages that the code fixed. What totals are wrong? I did have to add a namespace for cbc to get code to work. — jdweng, Apr 11 '20 at 14:03

score -1 · Answer 2 · answered Apr 11 '20 at 15:34

@jdweng thanks for all, your comments guided me.

I solved it out with your help.

XmlSerializer serializer = new XmlSerializer(typeof(Note));
                Note obj = (Note)serializer.Deserialize(reader);


                var Taxes = obj.NoteLine.SelectMany(rd => rd.TaxTotal.TaxSubtotal)
                .GroupBy(tt => tt.TaxCategory.TaxScheme.TaxTypeCode).Select(cl => new
                {
                    TaxableAmount = cl.Sum(x => x.TaxableAmount.Text),
                    TaxAmount = cl.Sum(x => x.TaxAmount.Text),
                    Tax = cl.First(),
                    cl.Key,
                });

                List<TaxSubtotal> TaxSubtotals = new List<TaxSubtotal>();

                int i = 1;
                foreach (var item in Taxes.ToList())
                {
                    TaxSubtotals.Add(new TaxSubtotal()
                    {
                        Percent = item.Tax.Percent,
                        TaxableAmount = new TaxableAmount()
                        {
                            CurrencyID = "USD",
                            Text = item.TaxableAmount
                        },
                        TaxAmount = new TaxAmount()
                        {
                            CurrencyID = "USD",
                            Text = item.TaxAmount
                        },
                        TaxCategory = new TaxCategory()
                        {
                            TaxScheme = new TaxScheme()
                            {
                                Name = item.Tax.TaxCategory.TaxScheme.Name,
                                TaxTypeCode = item.Tax.TaxCategory.TaxScheme.TaxTypeCode
                            },
                        },
                    });
                }
            }

Grouping and collect nested object with C# Linq

2 Answers2