代码之家 › 专栏 › 技术社区 › nelsonwebs

使用LINQ嵌套集合的SQL到XML

linq-to-xml linq

nelsonwebs · 技术社区 · 16 年前

我有一个这样的数据表。

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

此表需要输出到一个像这样的XML文件。

<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>

我有一个Linq查询来从类似这样的SQL中获取数据。

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

但是,我无法使用LINQ(CSharp)编写XML。具体来说,问题是我不知道如何处理在每个名称节点(嵌套循环/集合)下有多个时间节点。怎么能做到?

这是一个SQL 08 ent数据库,如果它对任何人都重要的话。

1 回复 | 直到 16 年前

Anthony Pegram 16 年前

您应该在这个人的名字上创建一个分组,也可能在一个列表中选择价格。下面的模型应该可以工作。

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))))));

xdocument现在看起来像这样

<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>