Nesting results in DataSet by field values

Question

Hello.

I'm using DataRelation to nest tables in a DataSet, to get a XML result like this:

<Customer>
    <Name>John</Name>
    <Age>30</Age>
    <Sex>Male</Sex>
    <Order>
        <Id>123</Id>
        <Price>200.00</Price>
    </Order>
</Customer>

I want to group the data by a field value, like sex, to get a result like this:

<Male>
    <Customer>
        <Name>John</Name>
        <Age>30</Age>
        <Order>
            <Id>123</Id>
            <Price>200.00</Price>
        </Order>
    </Customer>
</Male>

How can I achieve this (except by iterating the tables myself and creating new ones)?

Thanks.

Answer 1

If you're looking for an XML output, you can use Linq-to-XML to get your result. First step is to produce your XML from the DataSet. I imagine it would look something like this:

<Customers>
    <Customer> 
        <Name>John</Name> 
        <Age>30</Age> 
        <Sex>Male</Sex> 
        <Order> 
            <Id>123</Id> 
            <Price>200.00</Price> 
        </Order> 
    </Customer>
    <Customer> 
        <Name>Jane</Name> 
        <Age>28</Age> 
        <Sex>Female</Sex> 
        <Order> 
            <Id>124</Id> 
            <Price>100.00</Price> 
        </Order> 
    </Customer>
    <Customer> 
        <Name>Jill</Name> 
        <Age>37</Age> 
        <Sex>Female</Sex> 
        <Order> 
            <Id>125</Id> 
            <Price>350.00</Price> 
        </Order> 
    </Customer>
    <Customer> 
        <Name>Jack</Name> 
        <Age>34</Age> 
        <Sex>Male</Sex> 
        <Order> 
            <Id>126</Id> 
            <Price>475.00</Price> 
        </Order> 
    </Customer>
</Customers>

Load that XML into an XDocument, and then you can use LINQ to perform your groupings and use that result.

XDocument document = XDocument.Parse(xml);

var query = from customer in document.Root.Elements("Customer")
            group customer by customer.Element("Sex").Value into sexgroup
            select new XElement(sexgroup.Key,
                from cust in sexgroup
                select new XElement("Customer",
                    cust.Element("Name"),
                    cust.Element("Age"),
                    cust.Element("Order")));

XDocument changedOutput = new XDocument();
changedOutput.Add(new XElement("GroupedCustomers", query));

The new XDocument now holds the following structure:

<GroupedCustomers>
  <Male>
    <Customer>
      <Name>John</Name>
      <Age>30</Age>
      <Order>
        <Id>123</Id>
        <Price>200.00</Price>
      </Order>
    </Customer>
    <Customer>
      <Name>Jack</Name>
      <Age>34</Age>
      <Order>
        <Id>126</Id>
        <Price>475.00</Price>
      </Order>
    </Customer>
  </Male>
  <Female>
    <Customer>
      <Name>Jane</Name>
      <Age>28</Age>
      <Order>
        <Id>124</Id>
        <Price>100.00</Price>
      </Order>
    </Customer>
    <Customer>
      <Name>Jill</Name>
      <Age>37</Age>
      <Order>
        <Id>125</Id>
        <Price>350.00</Price>
      </Order>
    </Customer>
  </Female>
</GroupedCustomers>

Edit Based on the comment of not knowing what's in the customer element list (other than name and sex), you could change the approach slightly in the query. This will produce the same output as above based on the given XML sample.

var query = from customer in document.Root.Elements("Customer")
            group customer by customer.Element("Sex").Value into sexgroup
            select new XElement(sexgroup.Key,
                from cust in sexgroup
                select new XElement("Customer",
                    cust.Elements().Where(ele => ele.Name.LocalName != "Sex")));

Or you can simply select the entire cust element, which would still include the Sex tag, which might make more sense to you when you're processing the records. You still get them into groups, but you do not lose any information at the customer level.

var query = from customer in document.Root.Elements("Customer")
            group customer by customer.Element("Sex").Value into sexgroup
            select new XElement(sexgroup.Key,
                from cust in sexgroup
                select cust);