如何用C# Linq构建含seat、game、block、customer关联的MySQL插入字符串

我正尝试从XML数据构建用于MySQL插入的字符串，需要生成包含子元素与父元素关联的字符串。目前已通过C# Linq获取常量game_id，但无法为seat元素同时关联其父元素block的id、子元素customer的customerid，现有代码无法实现该关联。

XML结构 #

<data>
  <game>
    <id>173</id>
    <stadium>173</stadium>
    <date>20230408181th</date>
    <title>123</title>
  </game>
  <block>
    <id>1</id>
    <entry>4</entry>
    <title>0</title>
    <visible>1</visible>
    <numbered>1</numbered>
    <seat>
      <id>1</id>
      <intid>123456</intid>
      <abo>N</abo>
      <customer>
        <customerid>10295</customerid>
      </customer>
    </seat>
    <seat>
      <id>1</id>
      <intid>1234567</intid>
      <abo>N</abo>
      <customer>
        <customerid>10295</customerid>
      </customer>
    </seat>
  </block>
  <block>
    <id>2</id>
    <entry>3</entry>
    <title>0</title>
    <visible>1</visible>
    <numbered>1</numbered>
    <seat>
      <id>1</id>
      <intid>12345678</intid>
      <abo>N</abo>
      <customer>
        <customerid>102951</customerid>
      </customer>
    </seat>
    <seat>
      <id>1</id>
      <intid>123456790</intid>
      <abo>N</abo>
      <customer>
        <customerid>102951</customerid>
      </customer>
    </seat>
  </block>
</data>

现有C#代码 #

AllocConsole();
var daneXML = XElement.Load("file.xml");
string game_id = daneXML.Elements("game").Elements("id").First().Value;
textbox.Text = game_id;
var games = daneXML.Elements("game").Select(game => game.Element("id").Value + "," + game.Element("stadium").Value +","+ game.Element("date").Value + ","+game.Element("title").Value);
foreach (var game in games)
{
    Console.WriteLine(game);
}

var blocks = daneXML.Elements("block").Select(block => block.Element("id").Value + "," + block.Element("entry").Value + "," + block.Element("visible").Value + "," + block.Element("numbered").Value);
foreach (var block in blocks)
{
    Console.WriteLine(block + "," + game_id);
}

var customers = daneXML.Elements("block").Elements("seat").Elements("customer").Select(customer => customer.Element("customerid").Value + "," + customer.Element("name").Value + "," + customer.Element("vorname").Value);
foreach (var customer in customers)
{
    Console.WriteLine(customer);
    //insert ignore
}

var seats = daneXML.Elements("block").Elements("seat").Select(seat => seat.Element("id").Value + "," + seat.Element("intid").Value + "," + seat.Element("abo").Value + "," + seat.Element("customerid").Value);
foreach (var seat in seats)
{
    Console.WriteLine(seat+","+game_id);
}

解决方案 #

要实现seat与父block的id、子customer的customerid关联，需要嵌套遍历block和seat，同时修正customerid的获取路径（原代码直接从seat取customerid是错误的，它在customer子元素下）。修改后的seats查询代码如下：

// 修正后的seat关联查询
var seats = daneXML.Elements("block")
    .SelectMany(block => block.Elements("seat")
        .Select(seat => 
            $"{seat.Element("id").Value}, " +
            $"{seat.Element("intid").Value}, " +
            $"{seat.Element("abo").Value}, " +
            $"{seat.Element("customer").Element("customerid").Value}, " +
            $"{block.Element("id").Value}, " +
            $"{game_id}"
        )
    );

foreach (var seat in seats)
{
    Console.WriteLine(seat);
}

说明 #

1. 使用SelectMany嵌套遍历block和其下的seat，这样在处理每个seat时可以直接访问父block的id。
2. 通过seat.Element("customer").Element("customerid")正确获取customer子元素中的customerid。
3. 最终拼接的字符串包含seat自身字段、customerid、父block的id以及game_id，满足MySQL插入的关联需求。

另外，原代码中customers查询尝试获取name和vorname，但XML中不存在这些字段，建议修改为仅获取customerid（或根据实际XML结构调整）：

var customers = daneXML.Elements("block")
    .Elements("seat")
    .Elements("customer")
    .Select(customer => customer.Element("customerid").Value);

内容的提问来源于stack exchange，提问作者Olaman