LINQ to XML: stored procedure returning result of FOR XML PATH

[Martin Honnen]

I am trying to get familiar with LINQ to SQL using Visual Studio 2008
with .NET 3.5 SP1, currently how you can use stored procedures. While I
am able to use a stored procedure successfully that does a normal SELECT
query I currently can't find a way to use a stored procedure doing a
SELECT FOR XML PATH to return XML. The generated code has a property of
type XElement but that is always null in the result of the stored
procedure call.

Here are the details:

Stored procedure

ALTER PROCEDURE dbo.uspTestForXml1
AS
SELECT id, col1
FROM example1
FOR XML PATH, ROOT('root'), TYPE;

Code generated in the data context is

[Function(Name="dbo.uspTestForXml1")]
public ISingleResult<uspTestForXml1Result> uspTestForXml1()
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<uspTestForXml1Result>)(result.ReturnValue));
}

The type uspTestForXml1Result that method returns is generated as follows:

public partial class uspTestForXml1Result
{

private System.Xml.Linq.XElement _Column1;

public uspTestForXml1Result()
{
}

[Column(Storage="_Column1", DbType="Xml")]
public System.Xml.Linq.XElement Column1
{
get
{
return this._Column1;
}
set
{
if ((this._Column1 != value))
{
this._Column1 = value;
}
}
}
}


Code trying to use that stored procedure, logging to Console.Out:

DataClasses1DataContext dc = new DataClasses1DataContext();
dc.Log = Console.Out;

foreach (uspTestSp1Result item in dc.uspTestSp1())
{
Console.WriteLine("id: {0}, col1: {1}", item.id,
item.col1);
}
Console.WriteLine();

foreach (uspTestForXml1Result item in dc.uspTestForXml1())
{
Console.WriteLine("result: {0}", item.Column1 == null ?
"null" : item.Column1.ToString());
}

Output of that code on the console is:

EXEC @RETURN_VALUE = [dbo].[uspTestSp1]
-- @RETURN_VALUE: Output Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.30729.1

id: 1, col1: foo
id: 2, col1: bar
id: 3, col1: baz

EXEC @RETURN_VALUE = [dbo].[uspTestForXml1]
-- @RETURN_VALUE: Output Int (Size = 0; Prec = 0; Scale = 0) [Null]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.30729.1

result: null



So the XElement property Column1 in the uspTestForXml1Result is null
while it should instead contain the XML generated by the FOR XML PATH query.

Can anyone explain why that approach fails and how to use a stored
procedure doing a FOR XML with LINQ to SQL to populate an XElement?

 

[Martin Honnen]

So the XElement property Column1 in the uspTestForXml1Result is null
while it should instead contain the XML generated by the FOR XML PATH
query.

I made some progress by using a user defined function (returning an xml
value) instead of using a stored procedure, that way LINQ to SQL
correctly populates an XElement on the .NET side of the function call.
Nevertheless the question below remains:

 

[Opus13]

Were you able to find an answer for this? I am experiencing exactly the same
issue.

 

[Martin Honnen]

Were you able to find an answer for this? I am experiencing exactly the same
issue.

No, I haven't found an answer. As a workaround I was able to use user
defined function instead of a stored procedure, in that case an XElement
was populated with the result of the FOR XML query.

 

[Opus13]

Martin,

I was able to finally find a solution. You need to name the output in the sp:

Select @XML as XMLResult

If you do this, the designer will create an object called XMLResult (or
whatever you name it) and it should know what it is (it worked for me).
Apart from that, in a another file you can extend the partial class and put
the object in there. In the attributes for that object, add Name="".

Ex:
public partial class usp_DoStuff
{
private System.Xml.Linq.XElement _Column1;
public usp_DoStuff()
{
}

[Column(Storage="_ReviewXml", DbType="Xml", Name="")]
public System.Xml.Linq.XElement Column1
{
get
{
return this._Column1;
}
set
{
if ((this._Column1 != value))
{
this._Column1 = value;
}
}
}
}


You have to extend the partial class in another file to prevent the designer
from overwriting your changes when you update the designer.

I hope this helps.