How do I specify that a property should generate a TEXT column rather than an nvarchar(4000)

Question

I'm working with the Code First feature of Entity Framework and I'm trying to figure out how I can specify the column data types that should be created when the database is auto-generated.

I have a simple model:

public class Article
{
    public int ArticleID { get; set; }

    public string Title { get; set; }
    public string Author { get; set; }
    public string Summary { get; set; }
    public string SummaryHtml { get; set; }
    public string Body { get; set; }
    public string BodyHtml { get; set; }
    public string Slug { get; set; }

    public DateTime Published { get; set; }
    public DateTime Updated { get; set; }

    public virtual ICollection<Comment> Comments { get; set; }
}

When I run my application, a SQL CE 4.0 database is automatically created with the following schema:

DB Schema

So far, so good! However, the data I will be inserting into the Body and BodyHtml properties is routinely larger than the maximum allowed length for the NVarChar column type, so I want EF to generate Text columns for these properties.

However, I cannot seem to find a way to do this! After quite a bit of Googling and reading, I tried to specify the column type using DataAnnotations, from information found in this answer:

using System.ComponentModel.DataAnnotations;

...

[Column(TypeName = "Text")]
public string Body { get; set; }

This throws the following exception (when the database is deleted and the app is re-run):

Schema specified is not valid. Errors: (12,6) : error 0040: The Type text is not qualified with a namespace or alias. Only PrimitiveTypes can be used without qualification.

But I have no idea what namespace or alias I should be specifying, and I couldn't find anything that would tell me.

I also tried changing the annotation as per this reference:

using System.Data.Linq.Mapping;

...

[Column(DbType = "Text")]
public string Body { get; set; }

In this case a database is created, but the Body column is still an NVarChar(4000), so it seems that annotation is ignored.

Can anyone help? This seems like it should be a fairly common requirement, yet my searching has been fruitless!

Does CE support text? Looking in the data types list, I can see ntext, but not text. — Damien_The_Unbeliever, Feb 04 '11 at 08:02
@Damien, you are right, thanks - please add this as an answer (and add a link to the data types list) and I'll accept it. — Mark Bell, Feb 04 '11 at 08:12
I couldn't find an online list of datatypes - I had to download the CE BOL to check. It looks like Kristof Claes has added an answer that covers the same ground. — Damien_The_Unbeliever, Feb 04 '11 at 08:16
@Damien - Yes, I'm sorry. I hadn't seen your comment when I started typing my answer. — Kristof Claes, Feb 04 '11 at 08:25
I'll accept Kristof's answer then—thanks for your help guys! — Mark Bell, Feb 04 '11 at 09:01

score 81 · Accepted Answer · answered Feb 06 '11 at 13:38

81

I appreciate the effort that went into the existing answer, but I haven't found it actually answering the question... so I tested this, and found out that

[Column(TypeName = "ntext")]
public string Body { get; set; }

(the one from System.ComponentModel.DataAnnotations) will work to create an ntext type column.

(My problem with the accepted answer is that it seems to indicate that you should change the column type in the interface, but the question is how to do it programmatically.)

answered Feb 06 '11 at 13:38

Marcel Popescu

3,146
3
35
42

3

Model validation still causes troubles and raises an error when your try insert more than 4,000 chars, even database is set to ntext, but seems when it reads "string" type, it automatically guess max length=4000, ignore the type at the database. For me still not solved. – Nestor Jun 18 '11 at 00:52

score 33 · Answer 2 · answered Mar 21 '11 at 10:53

33

You can use the following DataAnnotation and Code-First will generate the maximum sized data type that the database allows. In the case of Sql CE it results in an underlying ntext column.

[MaxLength]

or using the EF 4.1 RC fluent API...

protected override void OnModelCreating(ModelBuilder modelBuilder){
    modelBuilder.Entity<Article>()
        .Property(p => p.Body)
        .IsMaxLength();
}

answered Mar 21 '11 at 10:53

Michael

609
7
10

2

For anyone still struggling with this under SQL Server CE + EF code-first, this is the solution you're looking for if you tried typeName=ntext and still got the error "the field must be a string or array type with a maximum length of '4000'" with data length >4000... [MaxLength] is all you need on the column to make it ntext and allow the full data length to be used – Rich Feb 03 '15 at 16:21

score 9 · Answer 3 · answered Feb 04 '11 at 08:13

9

Have you tried ntext? I have just created a SQL CE 4.0 database, and when I manually add a column to a table, I notice that text isn't available in the list of datatypes while ntext is. Just like you can pick nvarchar but no varchar.

Unfortunately, the biggest nvarchar size you can choose is 4000. So nvarchar(max) is also not an option.

There is ntext but no text

answered Feb 04 '11 at 08:13

Kristof Claes

10,797
3
30
42

But how you define your model, so model validation understands is a ntext, and not a string (with default max = 4,000). I have set ntext to my db, and still get error when adding more than 4,000 chars. My model property is string type. – Nestor Jun 18 '11 at 00:53
Have you tried putting `[Column(TypeName = "ntext")]` on the string property? – Kristof Claes Jun 18 '11 at 05:51
Yes, but was not enough, I added [MaxLength] to force check field size in database instead of default string type max length, and now is ok. – Nestor Jun 20 '11 at 02:02

score 7 · Answer 4 · answered Oct 09 '14 at 04:56

7

You can use System.ComponentModel.DataAnnotations.Schema.ColumnAttribute

[Column(TypeName="text")]
public string Text { get; set; }

or via Fluent API:

modelBuilder.Entity<YourEntityTypeHere>()
    .Property( e => e.Text)
    .HasColumnType("text");

answered Oct 09 '14 at 04:56

Nerdroid

13,398
5
58
69

score 6 · Answer 5 · answered Feb 23 '11 at 18:32

The issue with using the string length attribute e.g.

[StringLength(4010)]

Is that any string > the number of chars defined in the attribute will trigger a validation exception, which kind of goes against any reason why you would use a non defined field size on a column, or you use a huge number in the attribute and lose any validation offered by the attribute. Ultimately you are using a validation mechanism to solve a mapping issue if you use the StringLength attribute, where as Marcel Popescu's answer using the Column attribute is a much better solution as it is using the mapping attributes to define type, and still allows you to use the StringLength attribute for validation.

Another option is to use the EF4 CTP5 fluent API and define the column mapping in the OnModelCreating event in the DbContext e.g.

protected override void OnModelCreating(ModelBuilder modelBuilder){
    modelBuilder.Entity<Article>()
    .Property(p => p.Body)
    .HasColumnType("nvarchar(max)");
}

Also it should be noted that NText is a deprecated data type (ntext, text, and image (Transact-SQL) MS Books Online) and the recommendation is to use NVarChar(MAX) in its place

For MS SQL CE is not deprecated, here the question mention the case for MS SQL CE (4). — Nestor, Jun 18 '11 at 00:55
True, just checked the Sql CE 4.0 Docs (http://msdn.microsoft.com/en-us/library/ms172424%28v=SQL.110%29.aspx) however the ntext type cant be used with any (sql server) string functions, and it stores data in a separate data page to the rest of the table, so personally unless i had a very clear reason not to i would stick with using nvarchar(max) for performance, and functionality reasons — nakchak, Jul 18 '11 at 12:38

score 5 · Answer 6 · edited May 20 '16 at 22:12

5

I know, this is probably a year too late but:

Use:

[StringLength(-1)]

This will create a nText field. I managed to store at least 25Kbytes in that field using the Compact Edition 4.0 database.

edited May 20 '16 at 22:12

Halcyon

14,631
17
68
99

answered Feb 08 '12 at 16:13

Johan Plogfeldt

51
1
1

score 1 · Answer 7 · answered Jun 04 '16 at 02:12

In case you do add-migration and update-database using Package Manager, you may amend the create table by adding storeType as follows:

       CreateTable(
            "dbo.Table_Name",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Title = c.String(nullable: false, maxLength: 500),
                    Body = c.String(unicode: false, storeType: "ntext"),
                })
            .PrimaryKey(t => t.ID);

Gábor · Answer 8 · 2017-05-19T22:37:05.237

If you don't want to annotate all your properties and you use a contemporary EF, use a convention:

public class StringNTextConvention : Convention {
  public StringNTextConvention() {
    Properties<string>().Configure(p => p.HasColumnType("ntext"));                    
  }
}

You can call it from your onModelCreating():

modelBuilder.Conventions.Add(new StringNTextConvention());

and all your strings will automagically turn into ntext columns.

score 1 · Answer 9 · answered Feb 04 '11 at 07:18

1

Have you tried lowercase "text"? Per this MSDN discussion, the data provider is case sensitive.

answered Feb 04 '11 at 07:18

Axarydax

16,353
21
92
151

I have tried that, and it doesn't make any difference: the generated column is still a `varchar`. +1 for the useful discussion link though, seems like there are some other methods I could try—I'll have a read through. – Mark Bell Feb 04 '11 at 07:24

score 1 · Answer 10 · answered Feb 16 '11 at 15:38

This DataAnnotation will make Code-First generate a nvarchar(MAX) column in sql

[StringLength(1073741822)]

Not sure if other big numbers do the same... I got this one using the calculator and the nvarchar(MAX) spec.

I've tried it with SQL Server 2005 Express or not, but not with CE

I'm using it and it works, but I would like to know if it's a good idea or if I'm missing something... is there any other way to make code-first know that I want nvarchar(MAX)?

score 1 · Answer 11 · answered Feb 17 '11 at 14:03

1

This DataAnnotation will make Code-First generate a nvarchar(MAX) column in sql also:)

[StringLength(4010)]

answered Feb 17 '11 at 14:03

Bohdan Lyzanets

1,652
22
25

score 1 · Answer 12 · edited Jun 03 '15 at 23:10

1

Agree that TypeName = "ntext" seems to work, although I also have to add:

[StringLength(Int32.MaxValue)]

to stop the default string length of 128 getting in the way.

edited Jun 03 '15 at 23:10

Nerdroid

13,398
5
58
69

answered Mar 28 '11 at 19:07

Steve Owen

2,022
1
20
30

1

This will not work with SQL CE. See Michael's answer on here in using [MaxLength] instead. – LordHits Mar 31 '11 at 02:55

How do I specify that a property should generate a TEXT column rather than an nvarchar(4000)

12 Answers12

Linked

Related