Linq/XML: grouping results properly within XML element

Question

OK, I asked for how to return a Linq query results as XML, and I got the answer here.

But there's one little problem: the results do not get grouped logically within the XML. For example:

XElement xml = new XElement("States",
  from s in MyStates
  from cy in s.Counties
  from c in cy.Cities
  where s.Code == "NY"
  orderby s.Code, cy.Name, c.Name
  select new XElement("State",
    new XAttribute("Code", s.Code),
    new XAttribute("Name", s.Name),
    new XElement("County",
      new XAttribute("Name", cy.Name),
      new XElement("City",
        new XAttribute("Name", c.Name)
      )
    )
  )
);

Console.WriteLine(xml);

The output is of the form:

<State Code="NY" Name="New York ">
  <County Name="WYOMING">
    <City Name="WARSAW" />
  </County>
</State>
<State Code="NY" Name="New York ">
  <County Name="WYOMING">
    <City Name="WYOMING" />
  </County>
</State>
<State Code="NY" Name="New York ">
  <County Name="YATES">
    <City Name="BELLONA" />
  </County>
</State>
<State Code="NY" Name="New York ">
  <County Name="YATES">
    <City Name="MIDDLESEX" />
  </County>
</State>
<State Code="NY" Name="New York ">
  <County Name="YATES">
    <City Name="PENN YAN" />
  </County>
</State>
<State Code="NY" Name="New York ">
  <County Name="YATES">
    <City Name="RUSHVILLE" />
  </County>
</State>

instead of:

<State Code="NY" Name="New York ">
  <County Name="WYOMING">
    <City Name="WARSAW" />
    <City Name="WYOMING" />
  </County>
  <County Name="YATES">
    <City Name="BELLONA" />
    <City Name="MIDDLESEX" />
    <City Name="PENN YAN" />
    <City Name="RUSHVILLE" />
  </County>
</State>

How do I get the results to appear as desired?

Answer 1

To get the results you want, I believe you'll have to nest LINQ to XML queries. On the outer query, you'll have to query for distinct states...then an inner query to get the counties for that state...then another inner query to get the cities for that county.