Features Of Entity Framework Core 2.1 - Better Column Ordering In Initial Migration

Before we discuss the feature itself, let's first decide if the order of any column in a table really matters.

 

Yes and No - The first thing to understand is that the logical order of columns in a table has an impact on physical storage. If we have a table without a clustered index (primary key in SQL Server), then the table is a Hash table. If we make any query on the Hash table, the data retrieval (reading) would be based on scanning of the whole row. Additionally, other factors for impact can be the following - the columns types under scan are varchar or the image or text and data is really big (in gigabytes).

 

Note - I could not find any reliable source to support my answer.

 

Entity Framework Core 2.0

 

If you are using an old version, start with a quick recap on how column ordering could be done. There are actually 3 ways.

 

Default - If you do not add any code for an order of column, then the order of columns is alphabetically in the table.

public class Post
{

       [Key]
       public int PostId { get; set; }


       public string Title { get; set; }


       public int UserId { get; set; }


       public DateTime CreatedOn { get; set; }


       public string Content { get; set; }

       public DateTime ModifiedOn { get; set; }


       public int BlogId { get; set; }
}

The result is as below.

 

 

 

Manual - we can reorder the column in the scaffolded migration.

migrationBuilder.CreateTable(
                name: "Posts",
                columns: table => new
                {
                    PostId = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    Title = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    UserId = table.Column<int>(type: "int", nullable: false),
                    CreatedOn = table.Column<DateTime>(type: "datetime2", nullable: false),
                    Content = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    ModifiedOn = table.Column<DateTime>(type: "datetime2", nullable: false),
                    BlogId = table.Column<int>(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Posts", x => x.PostId);
                });

The result is below. Note that now the order of columns is exactly like the order of properties in our Post class above.

 

 

Using T-SQL migration

migrationBuilder.Sql("CREATE TABLE [dbo].[Posts](" +
               "[PostId][int] IDENTITY(1, 1) NOT NULL," +
               "[Title][nvarchar](max) NULL," +
               "[UserId][int] NOT NULL," +
               "[CreatedOn][datetime2](7) NOT NULL," +
               "[Content][nvarchar](max) NULL," +
               "[ModifiedOn][datetime2](7) NOT NULL," +
               "[BlogId][int] NOT NULL," +
               "CONSTRAINT[PK_Posts_new] PRIMARY KEY CLUSTERED([PostId] ASC))") ;

The result is exactly like above (1).

 

Using DataAnnotations

public class Post
    {
        [Key, Column( Order = 0)]
        public int PostId { get; set; }

        [Column( Order = 1)]
        public string Title { get; set; }

        [Column( Order = 2)]
        public int UserId { get; set; }

        [Column(Order = 3)]
        public DateTime CreatedOn { get; set; }

        [Column(Order = 4)]
        public string Content { get; set; }
        [Column(Order = 5)]
        public DateTime ModifiedOn { get; set; }

        [Column(Order = 6)]
        public int BlogId { get; set; }
}

Entity Framework Core 2.1

 

Now, with new Entity Framework Core 2.1, it is actually very easy. The order of columns is exactly the order of properties in the class.

public class Post
    {
        public int PostId { get; set; }


        public string Title { get; set; }


        public int UserId { get; set; }


        public DateTime CreatedOn { get; set; }


        public string Content { get; set; }

        public DateTime ModifiedOn { get; set; }


        public int BlogId { get; set; }
    }

The scaffolded migration created automatically is as below.

migrationBuilder.CreateTable(
                name: "Posts",
                columns: table => new
                {
                    PostId = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    Title = table.Column<string>(nullable: true),
                    UserId = table.Column<int>(nullable: false),
                    CreatedOn = table.Column<DateTime>(nullable: false),
                    Content = table.Column<string>(nullable: true),
                    ModifiedOn = table.Column<DateTime>(nullable: false),
                    BlogId = table.Column<int>(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Posts", x => x.PostId);
                });

And, the result is like this.

 

Conclusion

 

The new feature in Entity Framework Core 2.1 is really going to save a lot of time for developers and reduce the complexity level for workarounds to get the order of columns in a table.