Using Linq for SQL with FOR XML procedures

The problem is

I have a stored procedure in MS SQL that ends with SELECT ... FOR XML, and formerly I used XmlReader to access the data from it (by calling SqlCommand.ExecuteXmlReader())

Now I'm trying to figure out how to access to this using LINQ
With VS constructor I drag and drop this procedure into Linq To SQL data class designer.

It creates something like that

[Function(Name="dbo.my_Procedure")]
public ISingleResult my_Procedure([Parameter(Name="param1", DbType="VarChar(50)")] string param1, ... all other params)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo) (MethodInfo.GetCurrentMethod())), param1, ... all other params);
return ((ISingleResult)(result.ReturnValue));
}


Strange thing that actually procedure returns not a sinlgle line but a collection of arbitrary broken XML source (I'm just wondering what parameter controls the size of each piece)

So to put all XML together you need to create a stream (memory stream) or file and an XmlTextWriter object, opened over the stream and a code like that to select all parts of the XML


System.Collections.Generic.IEnumerable rawXMLLines =
(new my_ProcedureDataContext()).my_Procedure(param1, ....)
.Select(my_Procedure => my_Procedure.XML_F52E2B61_18A1_11d1_B105_00805F49916B);

foreach (string rawXML in rawXMLLines)
{
writer.WriteRaw( rawXML );
}

Well, not so much development work but...

Seems, that we lost here the main advantage of the SELECT ... FOR XML clause - an ability to write Xml to the output stream directly from XmlReader (that we get from ExecuteXmlReader()) , which is the most effective way if you are working with large XML files.

In the example, shown above, the results of the procedure is extracted by LINQ engine to the collection of strings before we start writing these strings one by one into output stream with XmlWriter.