Converting database data into an XML presentation with LINQ and ADO.NET Entity Framework

Sometimes, you might need to export SQL database data into XML, for example for subsequent manipulation. Chances are you are already using an ADO.NET Entity Framework data model in your application to access the data on an SQL Server database. If this is the case, then using LINQ to XML will nicely allow you to convert the data into XML. However, there are some caveats you should be aware.

Firstly, LINQ to Entities isn't yet perfect, and doesn't support all operations that LINQ to Objects for example supports. Let's see how this looks like. Assume you needed to create an XML presentation of certain orders in the Northwind sample database's Orders table. Your XML target might be something like this:

<orders>
  <order>
    <date>1997-08-25</date>
    ...
  </order>
  <order>
    <date>1997-10-03</date>
    ...
  </order>
  <order>
    <date>1997-10-13</date>
    ...
  </order>
  ...
</orders>

Your first attempt might be to use code similar to the following to construct the XML:

XElement orderElement = new XElement("orders",
  from o in entities.Orders
  where o.CustomerID == "ALFKI"
  select new XElement("order",
    new XElement("date", o.OrderDate)));

This code looks slick, and sure it is. However, the problem is that although the code compiles fine, it fails at runtime with the following exception:

System.NotSupportedException: Only parameterless constructors
and initializers are supported in LINQ to Entities.

What a shame! Maybe sometime, LINQ to Entities will support constructors with parameters, but with .NET 4.0 and Visual Studio 2010, it unfortunately doesn't.

How could you fix this problem, then? Luckily, the solution is quite easy: first materialize ("evaluate") the database query results into a certain object, such as a list. Then, proceed to use LINQ to XML to create the actual XML elements. Like this, for instance:

NorthwindEntities entities = new NorthwindEntities();
var matchingOrders =
    (from o in entities.Orders
    where o.CustomerID == "ALFKI"
    select o).ToList();

XElement orderElement = new XElement("orders",
    from o in matchingOrders
    select new XElement("order",
         new XElement("date", o.OrderDate)));

MessageBox.Show(orderElement.ToString());

Now the code works, and it wasn't overly difficult. Great!