How to indicate that a property should generate a TEXT column, not nvarchar (4000)

Question

I am working with the Code First function in the Entity Framework, and I am trying to figure out how I can specify the data types of the columns that should be created when the database is automatically 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, the SQL CE 4.0 database is automatically created with the following schema:

So far so good! However, the data that I will insert in the Body and BodyHtml is usually greater than the maximum length for the NVarChar column NVarChar , so I want EF to generate Text columns for these properties.

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

 using System.ComponentModel.DataAnnotations; ... [Column(TypeName = "Text")] public string Body { get; set; } 

This throws the following exception (when deleting the database and re-executing the application):

 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 indicate, and I could not find anything that could tell me.

I also tried changing the annotation to this link :

 using System.Data.Linq.Mapping; ... [Column(DbType = "Text")] public string Body { get; set; } 

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

Can anyone help? It seems like this should be a fairly common requirement, but my search turned out to be fruitless!

Answer 1

I appreciate the effort that went into the existing answer, but I did not actually find it as the answer to the question ... so I checked it and found out that

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

(one of System.ComponentModel.DataAnnotations ) will work to create an ntext column.

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

Answer 2

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

 [MaxLength] 

or using the EF 4.1 RC APIs ...

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

Answer 3

Have you tried ntext ? I just created a SQL CE 4.0 database, and when I manually add a column to the table, I noticed that text not available in the list of data types, and ntext is not. Just as you can choose nvarchar , but not varchar .

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

Answer 4

Problem using string length attribute like

 [StringLength(4010)] 

This is any row> the number of characters defined in the attribute, will cause a check exception, which type is against any reason why you would use an undefined field size in the column, or use a huge quantity in the column attribute and lose any validation offered by the attribute. Ultimately, you use a validation mechanism to solve the matching problem if you use the StringLength attribute, where Marcel Popescu's answer using the Column attribute is a much better solution, since it uses matching attributes to determine the type and still allows you to use the StringLength attribute for validation .

Another option is to use the free EF4 CTP5 API and define the column mapping in the OnModelCreating event in DbContext, for example.

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

It should also be noted that NText is an obsolete data type ( text, text and graphic (Transact-SQL) MS Books Online ), and the recommendation is to use NVarChar (MAX) in its place

Answer 5

I know this is probably too late, but:

Using:

 [StringLength(-1)] 

This will create an nText field. I was able to save at least 25 KB in this area using Compact Edition 4.0 databases.

Answer 6

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

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

or through the Fluent API:

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

Answer 7

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

Answer 8

This DataAnnotation will cause Code-First to generate an nvarchar (MAX) column in sql

 [StringLength(1073741822)] 

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

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

I use it and it works, but I would like to know if this is a good idea or if I am missing something ... is there any other way to make the code the first to know that I want nvarchar (MAX)?

Answer 9

This DataAnnotation will force Code-First to generate an nvarchar (MAX) column in sql as well :)

 [StringLength(4010)] 

Answer 10

If you do not want to comment on all your properties and use modern EF, use the convention:

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

You can call from your onModelCreating() :

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

and all your ntext automatically converted to ntext columns.

Answer 11

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

 [StringLength(Int32.MaxValue)] 

to stop the default string length of 128.

Answer 12

If you add-reconfigure and update-the database using the package manager, you can modify 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);