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.

Architecture of .NET projects with LINQ

LINQ promises a lot of advantages and simplify most things that usually should be implemented in Data acess layer

However, currently I can't find any suitable design patterns that can show how one can build a huge project (say 100+) tables avoiding copy-paste code.

Recently, I found a post Building Multi-Tier Web Application in .NET 3.5 Framework Using LINQ to SQL but there is no answer to how to avoid a lot of copy-paste code.

So, I think I should play more with that code, generic classes and new C# 3.0 language features...