Entity Framework Core: Specifying data type length and precision

  • Gérald Barré

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 datatypes 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 match the class but do you really 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's some useful links about data types:

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

Follow me:
Enjoy this blog?Buy Me A Coffee