How do I set a column in SQL Server to varchar(max) using ASP.net EF Codefirst Data Annotations?

Question

I've been searching around the web trying to figure out the right syntax to have Entity Framework Code First create my table with a column: varchar(max).

This is what I have. By default this creates varchar(128). How do I create varchar(max)?

I have tried [MaxLength] without success.

Any help would be appreciated. Thanks!

[Column(TypeName = "varchar")]
public string MediaDesc { get; set; }

Solution 1

[Column(TypeName = "varchar(MAX)")]

Surprisingly the most obvious solution works.

The [MaxLength] attribute only creates a varchar column with a max length that isn't MAX but - in my case (SQL Server Express 2008 R2) - 8000.

Solution 2

This will get you nvarchar(max):

[StringLength(int.MaxValue)]

I don't think there's an attribute to force non-unicode (are you sure you want that?), so for varchar(max) you need a tweak in the DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Entity>().Property(x => x.MediaDesc).IsUnicode(false);
}

Solution 3

Use [MaxLength] annotation.

[Column(TypeName = "varchar")]
[MaxLength]
public string MediaDesc { get; set; }

Solution 4

Update for @Slauma answer.

Using a override for all strings like this in OnModelCreating:

modelBuilder.Properties<string>().Configure(s => 
    s.HasMaxLength(256).HasColumnType("nvarchar"));

and then modifying properties with attributes like this:

[Column(TypeName = "nvarchar(MAX)")]
public string CaseComment { get; set; }

Or this:

modelBuilder.Entity<YourClass>()
    .Property(b => b.CaseComment)
    .HasColumnType("nvarchar(MAX)");

This can cause the Exception. Validation failed for one or more entities. See 'EntityValidationErrors' property for more details. Even though the column is of correct data type Entity Framework still thinks it is nvarchar(256) and throws the error DbEntityValidationException.

To fix this use the following instead:

[Column(TypeName = "nvarchar(MAX)")]
[MaxLength]
public string CaseComment { get; set; }

Or

modelBuilder.Entity<YourClass>()
    .Property(b => b.CaseComment)
    .HasColumnType("nvarchar(MAX)")
    .HasMaxLength(null);

View more solutions

Comments

[Slauma]

For me this creates a varchar(8000) column, not varchar(MAX).

[Slauma]

Are you sure that int.MaxInt works for the StringLength attribute? I tried this with the MaxLength attribute ([StringLength(int.MaxInt)]) and get an exception that the value must not be greater than 4000. Maybe it works with StringLength, but if so, then that's all very confusing...

[Diego Mijelshon]

No idea about MaxLength. I tried with StringLength, and it works as expected.

[Slauma]

I just found that the combination with the [Column(TypeName = "varchar")] attribute is the problem. Without it works with MaxLength and StringLength as well. +1

[Kirk Woll]

I think using MaxLength is most idiomatic (and concise), but both StringLength and MaxLength are far better than specifying the column type name explicitly, as the accepted answer does.

[Shaiju T]

alter column size using database first method:, i used this in sql to change size from nvarchar(50) to max: alter table Product alter column WebSite nvarchar(max) null Note: this will not effect the model property, but if you alter the column name to new name then it will effect the model with error, then you can use option 'update model from databse' or delete the .edmx file and recreate it. Hope helps someone.

[RBT]

For MAX string length it works fine but if you want to mention specific string length in-line then it starts to fail. This [Column("ToDoItem", TypeName = "nvarchar(2000)")] will not work and fails with really mysterious errors which gives you no clue of what is going wrong behind the scenes. For MAX nvarchar length you don't need to mention the stringLength attribute separately. I'm on EF 6 btw.

[Kat Lim Ruiz]

MaxLength is a validation, not a column definition

[The Red Pea]

I had to tell it the name of the column, because it was inconsistent with my Property name : [Column("BUS_UNIT", TypeName="varchar(MAX)")] even though property was named BusUnit1