Defining the order for an existing table using EF Core and migrations

Question

I'm trying to set up a new Blazor project and want to add a SQL Server database to it, which I'm configuring using Entity Framework Core and migrations.

I have a simple class User with some basic columns like firstname, lastname, etc. and an inherited class BaseEntity that contains columns like Created At or Updated At that will be added to each table.

When I create the migrations that define the user table for the first time, it automatically orders the columns from the inherited class to the end, which is good and what I expected. The issue is when trying to add new columns, which get added to the end of the table instead of ordered like they're defined in the class.

The old code of this project runs on a MySQL server, where I've updated the database with a script that I change/expand manually. There I always used the After keyword to define where the new column should be inserted into.

Is there a way to also use that after keyword in ef core? If not my current best solution would be to order the BaseEntity columns directly after the primary key and any foreign keys, which generally shouldn't change a lot...

---

Edit 19.02.2025 10:00:

From the conversation in the comments it seems what I'm trying to achieve is currently not possible as SQL Server doesn't contain a keyword like MySQL does with After. If anyone stumbles upon this question and has an answer to this problem (maybe a future version allows this), please do answer it, for the meantime I'll look for a different solution without ordering.

Answer 1

In SQL Server the order of the columns in a table cannot be changed after the table has been created, to do so would require dropping the table and re-creating it.

• This is not a limitation of EF, it is a feature of MS SQL Server.

Even if you use the [ColumnOrder] data annotations, this will only define the order of the columns in the initial CREATE statement, the annotation is ignored when adding new columns to an existing table.

A work around would be to modify the migration script to rename the table, create a new version of the table with the new schema, copy across the records and then drop the original table... You can script that easy enough, but what is the value in doing so?

The only way this order matters is if you are using SELECT * syntax. I cannot stress enough how irrelevant that requirement is. If you need the fields in a specific order, then specify those fields, in the order you want them, in your SELECT statement.

Another hack would be to use your EF migration to drop and recreate a VIEW that queries your table with the specific fields, but this sort of vanity really doesn't have a place in a commercial solution.

This requirement also has zero effect on the application runtime as EF will always specify the columns that it needs and the order or the columns in the table definition is irrelevant, it would be ignored anyway.