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]

Re: LINQ to SQL as XML

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.Collecti ons.Generic;
using System.Componen tModel;
using System.Linq;
using System.Windows. Forms;
using System.Xml.Linq ;
using ConsoleApplicat ion13;


static class Program
{
static void Main()
{
Application.Ena bleVisualStyles ();
using (NWindDataConte xt ctx = new NWindDataContex t())
{
ctx.Log = Console.Out; // show SQL
var suppliers = new XElement("suppl iers",
from supplier in ctx.Suppliers
select new XElement(
"supplier",
supplier.AsXAtt ributes())
);
Console.WriteLi ne(suppliers);
}
}

static IEnumerable<XAt tribute>
AsXAttributes(t his object source)
{
if(source == null) throw new ArgumentNullExc eption("source" );
foreach (PropertyDescri ptor prop in
TypeDescriptor. GetProperties(s ource))
{
object value = prop.GetValue(s ource);
if (value != null)
{
yield return new XAttribute(prop .Name, value);
}
}
}
}

[Marc Gravell]

Re: LINQ to SQL as XML

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

Marc

[Jon Skeet [C# MVP]]

Re: LINQ to SQL as XML

On Apr 12, 4:41 pm, Marc Gravell <marc.grav...@g mail.comwrote:

<snip>

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 PropertyDescrip tor. 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<XAt tributeAsXAttri butes(this object data)
{
foreach (PropertyInfo prop in data.GetType(). GetProperties() )
{
object value = prop.GetValue(d ata, null);
if (value != null)
{
yield return new XAttribute
(prop.Name.Repl ace("_", "-"), value);
}
}
}

Jon