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 version is released

ORMs like Entity Framework Core provide great abstractions of the database. However, it should not prevent you from looking at the generated code. In this post, I want to draw attention to the schema generation. Indeed, in a relational database, using the right data types and relations is very important. If you don't provide the necessary data, Entity Framework Core won't generate the optimal database schema, and the performance of the database won't match your expectations.

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 matches the class but do you need nvarchar(max) or datetime2(7)? Maybe nvarchar(50) is enough to store a username, and date to store the date of birth. You can customize the default behavior of Entity Framework Core using attributes or the fluent API. There are two options, defining the maximum length for strings or binary, or defining the 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");
    }
}

Now, 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!