sql to xml using linq - nested collections

Question

I have a table of data that looks something like this.

name, hour, price1, price2, price3, price4, price5
fred, 3, 12.5, 13.5, 14, 15, 16
dave, 6, 8, 12, 18, 20.2, 25
fred, 6, 10, 11, 14, 15, 19.7

This table needs to be output to an xml file that looks like this.

<timeCost>
  <person name="fred">
    <time hour="5">
      <cost price="12.5" />
      <cost price="13.5" />
      <cost price="14" />
      <cost price="15" />
      <cost price="16" />
    </time>
    <time hour="6">
      <cost price="10" />
      <cost price="11" />
      <cost price="14" />
      <cost price="15" />
      <cost price="19.7" />
    </time>
  </person>
  <person name="dave">
    <time hour="6">
      <cost price="8" />
      <cost price="12" />
      <cost price="18" />
      <cost price="20.2" />
      <cost price="25" />
    </time>
  </person>
</timeCost>

I have a linq query to get the data from SQL something like this.

 // initialize data context
 var people = from p in dc.people orderby p.name, p.hour select p;

However, I'm having trouble writing the xml out using linq (csharp). Specifically, the problem is I don't know how to deal with having multiple time nodes under each name node (nested loops/collections). How can this be done?

This is a sql 08 ent db if it matters to anyone.

Answer 1

You should create a grouping on the person's name and also probably select the prices into a list. Something like the mock-up below should work.

class Person
{
    public string Name { get; set; }
    public int Hour { get; set; }
    public decimal Price1 { get; set; }
    public decimal Price2 { get; set; }
    public decimal Price3 { get; set; }
    public decimal Price4 { get; set; }
    public decimal Price5 { get; set; }
}

...

List<Person> people = new List<Person>()
{
    new Person() { Name = "Fred", Hour = 3, Price1 = 12.5m, Price2 = 13.5m, Price3 = 14, Price4 = 15, Price5 = 16 },
    new Person() { Name = "Fred", Hour = 6, Price1 = 10, Price2 = 11, Price3 = 14, Price4 = 15, Price5 = 19.7m },
    new Person() { Name = "Dave", Hour = 6, Price1 = 8, Price2 = 12, Price3 = 18, Price4 = 20.2m, Price5 = 25 }
};

var query = from p in people
            group p by p.Name into pg
            select new
            {
                Name = pg.Key,
                Records = from record in pg
                            select new
                            {
                                Hour = record.Hour,
                                Prices = new List<Decimal>() { record.Price1, record.Price2, record.Price3, record.Price4, record.Price5 }
                            }
            };

XDocument document = new XDocument(new XElement("timeCost",
                                from person in query
                                select new XElement("person", new XAttribute("name", person.Name),
                                    from record in person.Records
                                    select new XElement("time", new XAttribute("hour", record.Hour),
                                        from price in record.Prices
                                        select new XElement("cost", new XAttribute("price", price))))));

The XDocument now looks like this

<timeCost>
  <person name="Fred">
    <time hour="3">
      <cost price="12.5" />
      <cost price="13.5" />
      <cost price="14" />
      <cost price="15" />
      <cost price="16" />
    </time>
    <time hour="6">
      <cost price="10" />
      <cost price="11" />
      <cost price="14" />
      <cost price="15" />
      <cost price="19.7" />
    </time>
  </person>
  <person name="Dave">
    <time hour="6">
      <cost price="8" />
      <cost price="12" />
      <cost price="18" />
      <cost price="20.2" />
      <cost price="25" />
    </time>
  </person>
</timeCost>