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

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

Enjoy this blog? Buy Me A Coffee Donate with PayPal

Comments

Dan Howell -

How do I set the precision? In EF 6 I had a line like this:

modelBuilder.Properties<datetime>().Configure(c => c.HasColumnType("datetime2").HasPrecision(4));

… which would set the precision of all DateTime fields. Is their an equivalent in EF core 2.0?

Gérald Barré -

There is no HasPrecision method. Instead, you can set the full column type in HasColumnType:

modelBuilder.Entity<Sample>()
    .Property(sample => sample.DateOfBirth)
    .ForSqlServerHasColumnType("datetime2(4)");
Sagan -

It seems that with EF Core 2.2, the SQL default for the C# DateTime is now datetime2 and trying all of the methods shown above to try to get it to datetime2(7) failed to work…

Leave a reply