ntext in ServiceStack.OrmLite

Question

how can i have nText datatype in ServiceStack.OrmLite Code first ?

public class Email
{
    [AutoIncrement]
    public long ID { get; set; }


    public DateTime Date { get; set; }

    public string From { get; set; }

    public string Subject { get; set; } 

    nText =>
    public string Body { get; set; } 

}

if i use string datatype , ormlite generate nVarchar(8000) in database

i need more than 8000 character for data

C#'s string datatype is fine for ntext. It can contain unicode characters, and can hold an arbitrary length string. — Andomar, Jul 01 '12 at 11:56
Don't use `text`, `ntext` or `image` for new development -- these data types are [deprecated](http://msdn.microsoft.com/en-us/library/ms187993.aspx). Use, respectively, `varchar(MAX)`, `nvarchar(MAX)`, and `varbinary(MAX)`. You'll need to consult your ORM documentation for how to use those types. — Jon Seigel, Jul 21 '12 at 13:01
I support @JonSeigel 's comment. And you can give length of string using attributes in Ormlite. — kunjee, Oct 10 '12 at 04:11
I agree with @JonSeigel, unless you have to support SQL 2000. In that case you may be forced to struggle with the likes of `text`, `ntext` and `image`. — Ryan Gates, Jun 11 '13 at 18:14

score 4 · Accepted Answer · edited May 23 '17 at 12:28

You need to convert your Body type to byte[] from string for ServiceStack.OrmLite to use the varchar(max) column type. Something like the below:

public byte[] Body { get; set; }

The reason for this lies within the ServiceStack.OrmLite code.

In the file ServiceStack.OrmLite/OrmLiteDialectProviderBase.cs, under the method InitColumnTypeMap() is:

DbTypeMap.Set<byte[]>(DbType.Binary, BlobColumnDefinition);

In the file ServiceStack.OrmLite.SqlServer/SqlServerOrmLiteDialectProvider.cs, under the method SqlServerOrmLiteDialectProvider() is:

base.BlobColumnDefinition = "VARBINARY(MAX)";

From this code, you can see that an internal mapping is taking place from the C# type to the internal ServiceStack.OrmLite type and then back out to the SqlServer type.

This question explains how to convert back and forth between strings and byte arrays, How do I get a consistent byte representation of strings in C# without manually specifying an encoding?.

score 2 · Answer 2 · edited May 23 '17 at 11:57

Assuming you really want NTEXT. If you want nvarchar(max) or varchar(max) see https://stackoverflow.com/a/25729568/37055

Decorate your domain model with System.ComponentModel.DataAnnotations.StringLengthAttribute

such as

[StringLengthAttribute(8001)]
public string Markdown { get;set; }

or

[StringLength(Int32.MaxValue)]
public string Markdown { get;set; }

using any length greater than 8000 to exceed to maximum length of Sql Server varchar/nvarchar column types.

Use a custom dialect provider the understands the NTEXT declaration.

public class NTextSqlProvider : SqlServerOrmLiteDialectProvider
{
  public new static readonly NTextSqlProvider Instance = new NTextSqlProvider();

  public override string GetColumnDefinition(string fieldName, Type fieldType, 
            bool isPrimaryKey, bool autoIncrement, bool isNullable, 
            int? fieldLength, int? scale, string defaultValue)
  {
     var fieldDefinition = base.GetColumnDefinition(fieldName, fieldType,
                                    isPrimaryKey, autoIncrement, isNullable, 
                                    fieldLength, scale, defaultValue);

     if (fieldType == typeof (string) && fieldLength > 8000)
     {
       var orig = string.Format(StringLengthColumnDefinitionFormat, fieldLength);

       fieldDefinition = fieldDefinition.Replace(orig, "NTEXT");
     }

     return fieldDefinition;
  }
}

Use the provider when you construct the database factory

var dbFactory = new OrmLiteConnectionFactory(conStr, NTextSqlProvider.Instance);

ntext in ServiceStack.OrmLite

2 Answers2

Linked