EntityFramework code first: Set order of fields

Question

I am using EntityFramework with the "Code first" approach with migrations.

I have successfully generated tables from my models, but the columns are being added in an alphabetical order rather than the order inside my model.

I have tried this:

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

[Column(Order=1)]
public int MySecondKeyProperty { get; set; }

But that doesn't seem to be working.

How can I manually set the order of the fields in the database?

I am using ASP.NET Core and EF Core (SqlServer) v1.1.0.

Answer 1

Currently ordering columns by class property is not implemented. Here's the long discussion about column ordering. Column ordering #2272

Update as of 07/12/2017

This issue is in the Backlog milestone. This means that it is not going to happen for the 2.0 release. We will re-assess the backlog following the 2.0 release and consider this item at that time.

Update as of 06/10/2019

Issue 2272 shipped with EF Core v2.1 and matches the order of the columns in the generated table to the order of the properties in the class. However, as @lloyd-conrade mentioned, this is only useful for initial creation

A new issue, #10059, has been created to track the possible implementation of respecting the Column attribute's Order property.

If the implementation of #2272 is insufficient for you and specifying something like [Column(Order = 1)] would help, please vote for this issue and add details about your scenario (if not already listed) below.

Note the "Punted for 3.0" label was added on May 10th, 2019, which is to say it will not ship in EF Core 3.0.

Answer 2

At this moment EF core doesn't support it.But there is a workaround for that.That is, you can explicitly specify SQL on your migration operation.

Instead of using the CreateTable method in your migrations, you need to explicitly write the SQL as shown below.There you can give the order as you wish.

migrationBuilder.Sql("CREATE TABLE Properties(
   MyFirstKeyProperty   INT   NOT NULL,
   MySecondKeyProperty int    NOT NULL,
   AGE  INT   NOT NULL,
   ......
   ......   
   PRIMARY KEY (MyFirstKeyProperty)
)");

You can read about the rowanmiller's commnet here about how to sort out that issue just for now

Answer 3

the order of the columns you mention is used to specify the order of the columns a compound foreign key, as it is in the case of some tables that have two or more fields as foreign key that refer to other tables, in that case you can use Column (Order = n)

Answer 4

As of Entity Framework Core 6 you can specify the column order with an annotation:

[Column(Order = 1)]
public int Id { get; set; }

https://learn.microsoft.com/en-us/ef/core/modeling/entity-properties?tabs=data-annotations%2Cwithout-nrt#column-order

Answer 5

Update: In EF Core 2.1, for the initial migration at least, columns are added to tables in the order in which the relevant properties are declared in their respective classes, rather than in alphabetical order. See here. But note that any subsequent Entity Framework migrations performed on the same tables won't change the column order of the columns created earlier.

Answer 6

You can use this solution to add support for explicit column ordering to your project: https://github.com/premchandrasingh/EFCoreColumnOrder. This solution adds the HasColumnOrder extension method. There is an example of use in the Sample folder. Do not forget to replace the corresponding services in your DbContext.