Linq - Query a table and return results as XML

Question

I have the following table structure:

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

I want to create a Linq query that will query this table and generate the following XML. Can someone help with this?

<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

The trick here is that you need two separate LINQ statement, one linq-to-sql to read the data, and one linq-to-xml to write it.

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