Entity Framework Core: Specifying data type length and precision

Note: I use the preview version of Entity Framework Core 2.0 (2.0.0-preview2-final). Things may change after the final release.

ORMs like Entity Framework Core provide powerful abstractions over the database, but that should not stop you from examining the generated SQL. In this post, I want to highlight schema generation. In relational databases, choosing the right data types matters. Without the necessary configuration, Entity Framework Core cannot generate an optimal schema, which can hurt database performance.

Let's use this class containing common datatypes:

public class Sample
{
    public int Id { get; set; }
    public string ColString { get; set; }
    public DateTime ColDateTime { get; set; }
    public decimal ColDecimal { get; set; }
    public float ColSingle { get; set; }
    public double ColDouble { get; set; }
    public int ColInt32 { get; set; }
    public long ColInt64 { get; set; }
    public byte[] ColBytes { get; set; }
}

Entity Framework Core generates the following table for SQL Server:

CREATE TABLE [dbo].[Samples] (
    [Id]                        INT             IDENTITY (1, 1) NOT NULL,
    [ColBytes]                  VARBINARY (MAX) NULL,
    [ColDateTime]               DATETIME2 (7)   NOT NULL,
    [ColDecimal]                DECIMAL (18, 2) NOT NULL,
    [ColDouble]                 FLOAT (53)      NOT NULL,
    [ColInt32]                  INT             NOT NULL,
    [ColInt64]                  BIGINT          NOT NULL,
    [ColSingle]                 REAL            NOT NULL,
    [ColString]                 NVARCHAR (MAX)  NULL,
    CONSTRAINT [PK_Samples] PRIMARY KEY CLUSTERED ([Id] ASC)
);

The table reflects the class, but do you actually need nvarchar(max) or datetime2(7)? For example, nvarchar(50) may be sufficient for a username, and date for a date of birth. You can customize Entity Framework Core's default behavior using either data annotations or the fluent API. The two main options are: setting a maximum length for strings or binary columns, or specifying the exact SQL data type.

public class Sample
{
    [MaxLength(50)] // nvarchar(50)
    public string Username { get; set; }

    [Column(TypeName = "char(3)")]
    public string Currency { get; set; }

    [Column(TypeName = "date")]
    public DateTime DateOfBirth { get; set; }
}

public class SampleContext : DbContext
{
    public DbSet<Sample> Samples { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Sample>()
            .Property(sample => sample.Username)
            .HasMaxLength(50);

        modelBuilder.Entity<Sample>()
            .Property(sample => sample.Currency)
            .HasColumnType("char(3)");

        modelBuilder.Entity<Sample>()
            .Property(sample => sample.DateOfBirth)
            .ForSqlServerHasColumnType("date");
    }
}

With these settings, Entity Framework Core will generate the expected schema. Here are some useful links about data types:

• Entity Framework Core data types
• SQL Server data types

Do you have a question or a suggestion about this post? Contact me!