LINQ to SQL as XML

[Marc S]

I have a LINQ class that I can use to query data from my database. I
want to query the data directly into an XML string without defining
each field.

If I have a table Names like the following:

Table: Names
Id int,
LastName varchar(32),
FirstName varchar(32)

Then I have a LINQ query that queries my DataContext:

var qry = from n in context.Names
where n.Id == pId
select n ;



I want to query the context as if to say
"select * from Names where Id = pId for XML AUTO"


If there a way to do that without manually creating an XElement?

 

[Marc Gravell]

LINQ is intended to be an object pipeline - as such, there is no
*immediate* way to do this - i.e. you can't impact the auto-generated
SQL to that extent short of writing your own impementation. You could
issue direct SQL, but that might defeat the purpose...

However! One option might be to feed the LINQ output into LINQ-to-SQL;
the SQL query won't have "FOR XML AUTO" in it, but the result should
be largely the same...

For example (and borrowing an idea [AsXAttributes*] slightly from Jon
Skeet's book [I'm sure he'll forgive me])...

*=the idea is Jon's, but no concrete implementation is offered in the
book (it may well be on the website - I didn't look); so any fault in
this alternative implementation is mine, not his - i.e. credit => Jon,
blame => me ;-p

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Windows.Forms;
using System.Xml.Linq;
using ConsoleApplication13;


static class Program
{
static void Main()
{
Application.EnableVisualStyles();
using (NWindDataContext ctx = new NWindDataContext())
{
ctx.Log = Console.Out; // show SQL
var suppliers = new XElement("suppliers",
from supplier in ctx.Suppliers
select new XElement(
"supplier",
supplier.AsXAttributes())
);
Console.WriteLine(suppliers);
}
}

static IEnumerable<XAttribute>
AsXAttributes(this object source)
{
if(source == null) throw new ArgumentNullException("source");
foreach (PropertyDescriptor prop in
TypeDescriptor.GetProperties(source))
{
object value = prop.GetValue(source);
if (value != null)
{
yield return new XAttribute(prop.Name, value);
}
}
}
}

 

[Marc Gravell]

LINQ output into LINQ-to-SQL
Should have read: LINQ-to-SQL output into LINQ-to-XML

Marc

 

[Jon Skeet [C# MVP]]

For example (and borrowing an idea [AsXAttributes*] slightly from Jon
Skeet's book [I'm sure he'll forgive me])...

*=the idea is Jon's, but no concrete implementation is offered in the
book (it may well be on the website - I didn't look); so any fault in
this alternative implementation is mine, not his - i.e. credit => Jon,
blame => me ;-p

Our code is almost identical - except I've just used PropertyInfo
instead of PropertyDescriptor. Oh, and I replace "_" with "-" in the
name, for the sake of some particular example I was interested in. I
also didn't bother with the null argument check - you're more
fastidious than I am, clearly

Here's the code (downloaded from http://csharpindepth.com/Downloads.aspx
as I don't have the original with me at the moment):

public static IEnumerable<XAttribute> AsXAttributes(this object data)
{
foreach (PropertyInfo prop in data.GetType().GetProperties())
{
object value = prop.GetValue(data, null);
if (value != null)
{
yield return new XAttribute
(prop.Name.Replace("_", "-"), value);
}
}
}

Jon