varchar(max) + linq to sql

Question

I've got a varchar(max) column I'm trying to read but the field is being truncated at 4,000 characters. I've seen similar questions, but they are on the sql side.

What do I need to do to get the entire field?

Eg:

using (DataContext dc = new DataContext())
{
    var foo = dc.foos.First();
    if (foo.Formula2.Length > 4000)
    {
        Console.WriteLine("success!");
    }
}

I've tried things like setting TextSize but it didn't make a difference.:

dc.ExecuteCommand("SET TEXTSIZE 100000;");
var foo = dc.foos.First();

UPDATE:

The server data type is varchar(max). The field in question is formula2:

If I try and change the type to something different like Object, I get message '*Mapping between DbType 'VarChar(MAX)' and Type 'System.Object' in Column 'Formula2' of Type 't_PriceFormula' is not supported.*'

Any suggestions?

Answer 1

sounds like .net convert varchar to nvarchar i dont know how to directly solve it. but System.Text.Encoding might help try this

//1 set col in db to varbinary(max)

//2 and when u want to save to db
string formula = "...";
System.Data.Linq.Binary bin = new System.Data.Linq.Binary(System.Text.Encoding.ASCII.GetBytes());

//3 when u pull ou
    Binary bin = row.Formula2;
    string formula = System.Text.Encoding.ASCII.GetString(bin.ToArray());

Answer 2

With thanks to 888.

This seems a little convoluted but it worked.

Convert the field to an array, then back to a string.

var foo = dc.foos.First();
string formula = new string(foo.Formula2.ToArray());