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; }

`[Column(TypeName = "varchar(MAX)")]` works for me. But I get a `varchar(8000)` with your code snippet, not `varchar(128)` as you say. Strange. What EF version are you using and what database version? — Slauma, Jun 27 '12 at 23:42
Thanks Slauma. Figures I tried a bunch of different ways except the most obvious. Works like a charm! — Maddhacker24, Jun 28 '12 at 00:28
I was actually surprised that it works :) (I'll write it as answer then...) — Slauma, Jun 28 '12 at 08:19

score 88 · Accepted Answer · answered Jun 28 '12 at 08:21

88

[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.

answered Jun 28 '12 at 08:21

Slauma

175,098
59
401
420

**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. – Shaiju T Oct 08 '15 at 08:33
1

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. – RBT May 09 '16 at 00:43
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` – Nate Anderson May 30 '17 at 18:31

Diego Mijelshon · Answer 2 · 2012-06-28T19:42:14.553

20

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);
}

edited Jun 28 '12 at 19:42

answered Jun 28 '12 at 16:28

Diego Mijelshon

52,548
16
116
154

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... – Slauma Jun 28 '12 at 18:18
No idea about MaxLength. I tried with StringLength, and it works as expected. – Diego Mijelshon Jun 28 '12 at 19:44
3

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 – Slauma Jun 28 '12 at 19:56
2

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. – Kirk Woll Mar 24 '14 at 20:47
1. Here is the "Fluent" : /* Microsoft.EntityFrameworkCore.Metadata.Builders.EntityTypeBuilder builder */ builder.Property(ml => ml.MyBigStringScalarProperty).HasMaxLength(Int32.MaxValue); and (2) I prefer this answer. I have to support MsSqlServer and Postgres...and I have verified (my fluent style at least) gives (the MsSqlServer desired behavior) AND the the postgres equivalent of (ddl column) "MyBigStringScalarProperty TEXT". Thanks Deigo. – granadaCoder Nov 15 '22 at 15:14

score 4 · Answer 3 · answered Jun 28 '12 at 06:23

4

Use [MaxLength] annotation.

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

answered Jun 28 '12 at 06:23

bhuvin

1,382
1
11
28

8

For me this creates a `varchar(8000)` column, not `varchar(MAX)`. – Slauma Jun 28 '12 at 08:23
5

MaxLength is a validation, not a column definition – Kat Lim Ruiz Jul 03 '16 at 16:29

score 2 · Answer 4 · answered Mar 16 '18 at 08:20

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);

score 0 · Answer 5 · edited Nov 11 '21 at 02:56

[Column(TypeName = "varchar(MAX)")] results in varchar(Max) in the database. If you have a custom convention setting strings MaxLength to 100,

modelBuilder
    .Properties<string>()
    .Configure(c => c.IsUnicode(false).HasMaxLength(100));

you need to include the MaxLength attribute [Column(TypeName = "varchar(MAX)"), MaxLength]. If you don't, you will get varchar(MAX) in the database but validation will throw for more than 100 characters.

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

5 Answers5

Linked