Linq - 查询表并以 XML 形式返回结果

Question

我有以下表结构：

CREATE TABLE [Report].[MesReport](
    [MesReportID] [int] IDENTITY(1,1) NOT NULL,
    [ParentID] [int] NOT NULL,
    [ReportTitle] [nvarchar](80) NOT NULL,
    [ReportName] [nvarchar](80) NOT NULL,
    [DatabaseServer] [nvarchar](80) NOT NULL,
    [DatabaseName] [nvarchar](50) NOT NULL,
    [Login] [nvarchar](80) NOT NULL,
    [ReportFile] [varbinary](max) NULL

我想创建一个 Linq 查询来查询该表并生成以下 XML。有人可以帮忙吗？

<MesReports> 
  <MesReport> 
      <ReportTitle>Mes to Sap Reconciliation Reports</ReportTitle> 
      <ReportName>None</ReportName>
      <DatabaseServer>None</DatabaseServer>
      <Database>None</Database>
      <Login>None</Login>
      <MesReportId>1</MesReportId> 
      <ParentId>-1</ParentId> 
  </MesReport> 
  <MesReport> 
      <ReportTitle>Finished Goods</ReportTitle> 
      <ReportName>None</ReportName>
      <DatabaseServer>None</DatabaseServer>
      <Database>None</Database>
      <Login>None</Login>
      <MesReportId>2</MesReportId> 
      <ParentId>1</ParentId> 
  </MesReport> 
  <MesReport> 
      <ReportTitle>Semi-Finished Goods</ReportTitle> 
      <ReportName>None</ReportName>
      <DatabaseServer>None</DatabaseServer>
      <Database>None</Database>
      <Login>None</Login>
      <MesReportId>3</MesReportId> 
      <ParentId>1</ParentId> 
  </MesReport> 
  <MesReport> 
      <ReportTitle>Warranty Reports</ReportTitle> 
      <ReportName>None</ReportName>
      <DatabaseServer>None</DatabaseServer>
      <Database>None</Database>
      <Login>None</Login>
      <MesReportId>4</MesReportId> 
      <ParentId>-1</ParentId> 
  </MesReport> 
  <MesReport> 
      <ReportTitle>Box - PBG</ReportTitle> 
      <ReportName>Warranty Box - PBG</ReportName>
      <DatabaseServer>MyServer</DatabaseServer>
      <Database>MESProduction</Database>
      <Login>6QDI+IoQbkZKJpVBcRJcNtoqR62606Q2</Login>
      <MesReportId>5</MesReportId> 
      <ParentId>4</ParentId> 
  </MesReport> 
  <MesReport> 
      <ReportTitle>Box - FFO</ReportTitle> 
      <ReportName>Warranty Box - FFO</ReportName>
      <DatabaseServer>MyServer</DatabaseServer>
      <Database>MESProduction</Database>
      <Login>6QDI+IoQbkZKJpVBcRJcNtoqR62606Q2</Login>
      <MesReportId>6</MesReportId> 
      <ParentId>4</ParentId> 
  </MesReport> 
  <MesReport> 
      <ReportTitle>Bin Count - PBG</ReportTitle> 
      <ReportName>Bin Count - PBG</ReportName>
      <DatabaseServer>MyServer</DatabaseServer>
      <Database>MESProduction</Database>
      <Login>6QDI+IoQbkZKJpVBcRJcNtoqR62606Q2</Login>
      <MesReportId>7</MesReportId> 
      <ParentId>4</ParentId> 
  </MesReport> 
  <MesReport> 
      <ReportTitle>Bin Count - FFO</ReportTitle> 
      <ReportName>Bin Count - FFO</ReportName>
      <DatabaseServer>MyServer</DatabaseServer>
      <Database>MESProduction</Database>
      <Login>6QDI+IoQbkZKJpVBcRJcNtoqR62606Q2</Login>
      <MesReportId>8</MesReportId> 
      <ParentId>4</ParentId> 
  </MesReport> 
</MesReports> 

Answer 1

这里的技巧是，您需要两个单独的 LINQ 语句，一个 linq-to-sql 来读取数据，一个 linq-to-xml 来写入数据。

var reportdata = from r in db.MesReport
//               where r.whatever ......
                select r;

var reportXmlItems = from rx in reportData
               select new XElement("MesReport", 
                    new XElement("ReportTitle", rx.ReportTitle),
                    new XElement("ReportName", rx.ReportName
                    new XElement("DatabaseServer", rx.DatabaseServer
                    new XElement("Database", rx.Database
                    new XElement("Login", rx.Login
                    new XElement("MesReportId", rx.MesReportId
                    new XElement("ParentId", rx.ParentId)
                );

 var reportXml = new XElement("MesReports", reportXmlItems);