Entity Framework String Fields

tl;dr – Make sure you explicitly set the max length of your Entity Framework models’ string fields via the [StringLength] attribute as they are all NVARCHAR(MAX) by default, and you cannot create indexes on these fields.

When you use Entity Framework code first, all of the string fields on your models get converted toNVARCHAR(MAX) fields in the database. As of 2018, SQL Server only supports a combined index size of 900 bytes. This means that NVARCHAR(MAX) fields can never be included in an index.

You can use either the [System.ComponentModel.DataAnnotations.MaxLength] attribute or the [System.ComponentModel.DataAnnotations.StringLength] to denote the max length of a given string field. This will ensure that the backing database field is sized appropriately. The field will still need to be less than 450 characters (since NVARCHAR(450) = 900 bytes).

Here is an example of how to change a string field length so that  it is NVARCHAR(100):

[StringLength(100)]
public string Name {get; set;}

Per this stackoverflow question (and related comments), you might as well use the [StringLength] attribute as it works both for model validation and EntityFramework.

This simple addition will help keep your database sized more appropriately and will allow these additional fields to be included in SQL indexes.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s