Linq to sql truncating string returned by Stored P

Question

I have asked this question before. but i was not able to get any answer. may be i wasnt very clear. let me give some more details.

I have a SP which returns a long string. here is dbml file code

[Function(Name="dbo.spX")]
public ISingleResult<spXResult> spX([Parameter(DbType="VarChar(8000)")] string str)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), str);
    return ((ISingleResult<spXResult>)(result.ReturnValue));
}

and here is spXResult class

public partial class spXResult
{
    private string _XML_F52E2B61_18A1_11d1_B105_00805F49916B;

    public spXResult()
    {  }

    [Column(Name="[XML_F52E2B61-18A1-11d1-B105-00805F49916B]", 
     Storage="_XML_F52E2B61_18A1_11d1_B105_00805F49916B", 
     DbType="NText", UpdateCheck=UpdateCheck.Never)]
    public string XML_F52E2B61_18A1_11d1_B105_00805F49916B
    {
        get
        {
            return this._XML_F52E2B61_18A1_11d1_B105_00805F49916B;
        }
        set
        {
            if ((this._XML_F52E2B61_18A1_11d1_B105_00805F49916B != value))
            {
                this._XML_F52E2B61_18A1_11d1_B105_00805F49916B = value;
            }
         }
     }
}

and here is my code

ISingleResult<spXResult> result = ctx.spX("1234");

string returnStr = result.First().XML_F52E2B61_18A1_11d1_B105_00805F49916B;

everything is fine, when the result is not a long string, but as soon as the sp returns a very long string, it truncates the result. i have no clue why.. can someone please help.

thanks

Answer 1

Just change your sp from

SELECT ...
  FROM MyTable
  FOR XML AUTO

to

DECLARE @ResultXML xml

SET @ResultXML = 
 (SELECT ...
  FROM MyTable
  FOR XML AUTO)

SELECT @ResultXML as [MyXML]

and recreate linq method

Answer 2

The only thing fishy I can spot is this - here in the declaration, you hvae:

public ISingleResult<spXResult> spX([Parameter(DbType="VarChar(8000)")] string str)

(DbType=VARCHAR(8000)) - which is ANSI (non-Unicode), but then in the column declaration you use NTEXT - first of all, that's UNICODE (2-byte per character), and why NTEXT?? Above you have VARCHAR?

[Column(Name="[XML_F52E2B61-18A1-11d1-B105-00805F49916B]", 
     Storage="_XML_F52E2B61_18A1_11d1_B105_00805F49916B", 
     DbType="NText", UpdateCheck=UpdateCheck.Never)]

That seems a bit odd.......

Can you try to make it the same in both places? E.g. VARCHAR(8000) in both cases??

Marc

Answer 3

LinqToSql splits the XML result set into chunks, so you need to run a loop like this:

ISingleResult<spXResult> result = ctx.spX("1234");
string xml = "";
foreach (var x in result)
    xml += x.XML_F52E2B61_18A1_11d1_B105_00805F49916B;

Or using LINQ:

string xml = result.Aggregate("", (current, x) => current + x.XML_F52E2B61_18A1_11d1_B105_00805F49916B);