Migrations: Order columns of abstract base class properties last in CreateTable

[TanvirArjel]

Before the release of Entity Framework Core 2.1, Entity Framework Core team announced that,

Column ordering in migrations: Based on customer feedback, we have updated migrations to initially generate columns for tables in the same order as properties are declared in classes.

but unfortunately, it is not working as expected in case of the following scenarios:

public abstract class AuditModel
{
    public int? CreatedBy { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public int? ModifiedBy { get; set; }
    public DateTime? ModifiedAt { get; set; }
    public bool IsActive { get; set; } = true;
}

public class Employee : AuditModel
{
   [Key]
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public long EmployeeId { get; set; }

   public string EmployeeNo { get; set; }

   [ForeignKey("Company")]
   public long CompanyId { get; set; }

   [ForeignKey("CompanyOffice")]
   public long OfficeId { get; set; }

   [ForeignKey("Section")]
   public long SectionId { get; set; }
    ..........................
}

Entity Framework Core Migration is generating the Employee Table columns as the following order:

  [CreatedBy] <-----
  ,[CreatedAt] <-----
  ,[ModifiedBy] <-----
  ,[ModifiedAt] <-----
  ,[IsActive] <-----
  ,[EmployeeId]
  ,[EmployeeNo]
  ,[CompanyId]
  ,[OfficeId]
  ,[SectionId]
  ............

Actually It should have been:

  ,[EmployeeId]
  ,[EmployeeNo]
  ,[CompanyId]
  ,[OfficeId]
  ,[SectionId]
   ............
  ,[CreatedBy] <-----
  ,[CreatedAt] <-----
  ,[ModifiedBy] <-----
  ,[ModifiedAt] <-----
  ,[IsActive] <-----

Note that, Entity Framework 6.x Migration generates the columns as expected order even in case of inheritance.

[DesarrolloNedugaTech]

I tried it and it works, thanks and regards.

[Seabizkit]

can this be fixed already we are on version 3.1 of ef core already. I would also like to see the "index" attribute back. like ef 6 had. I wouldn't think it would be that hard to implement order by on attributes for migrations.

[bricelam]

Yes, the plan is to fix this in the next version of EF Core. Here is the issue for an index attribute: #4050. I'm not sure what you mean by order by on attributes for migrations, but please always feel free to submit a PR; all six team members are eager to help users fix their biggest pet peeves or implement small enhancements.

[zgonzales]

Please fix this soon, it is pretty high on the list when sorting the issues by most comments.

[Seabizkit]

@bricelam FYI just in case it wasn't clear

There are two attributes....

• "[Index(name), order, IsUnique = true)]"
• "[Column(Order = 601)]"

What we are wanting is the order they are specified in the model. when there is an abstract class inherited then they must be appended to the end except for the table key.

We also want to be able to specify preference.... so giving a lower Order will make it appear closer to the start. Basically what EF6 is doing with the added ability to specify higher or lower index on abstract classes.

This issue is more about the ordering than the Index, just sharing as you gave a link to the missing Index one, but the "bigger" issue is the order.

Index can be specified using fluent notation.

class entity
{
    Id 
    [Column(Order = 2)]
    DateCreated

    DateModfied
}

class persion : entity
{
     Name
     Email
}

the order

Id
DateCreated
Name
Email
DateModfied

[bricelam]

Nobody here has any expectations about unmapped types in the middle of a hierarchy, right? This is just about the case identified in the issue description?

[bricelam]

Also, if an owned type has an unmapped base type, it's properties should go next to the other owned type's properties still, right?

[TanvirArjel]

Nobody here has any expectations about unmapped types in the middle of a hierarchy, right?

Yes! but if anybody wants this then he/should be able to use ColumnOrderAttribute to specify the custom position,

[aalsamawi]

I managed ordering by customizing SqlServerMigrationsAnnotationProvider and SqlServerMigrationsSqlGenerator. Even though it says internal API, I think it is much cleaner until available out of the box. Here is git repo https://github.com/premchandrasingh/EFCoreColumnOrder

This doesn't work with MySQL. Any thoughts? here's the error I'm getting:

System.InvalidOperationException: The current migration SQL generator 'CustomSqlServerMigrationsSqlGenerator' is unable to generate SQL for operations
of type 'Microsoft.EntityFrameworkCore.Migrations.Operations.MySqlCreateDatabaseOperation'.
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder
builder)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlDatabaseCreator.CreateCreateOperations()
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlDatabaseCreator.Create()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Pomelo.EntityFrameworkCore.MySql.Migrations.Internal.MySqlMigrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The current migration SQL generator 'CustomSqlServerMigrationsSqlGenerator' is unable to generate SQL for operations of type 'Microsoft.EntityFrameworkCore.Migrations.Operations.MySqlCreateDatabaseOperation'.

[pgiacomo69]

Today, with Net 5.0 RC2 and Pomelo.EntityFrameworkCore.MySql:

public partial class BaseEntity { [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] [Column("ID", Order = 0)] public long Id { get; set; } [Column("CREATED_AT", Order = 995)] [IgnoreDataMember] [JsonIgnore] public DateTime? CreatedAt { get; set; } [Column("CREATED_BY_SESSION_ID", Order = 996)] [IgnoreDataMember] [JsonIgnore] public long CreatedBySessionId { get; set; } [Column("UPDATED_AT", Order = 997)] [IgnoreDataMember] [JsonIgnore] public DateTime? UpdatedAt { get; set; } [Column("UPDATED_BY_SESSION_ID", Order = 998)] [IgnoreDataMember] [JsonIgnore] public long UpdatedBySessionId { get; set; } [Column("VERSION_ID", Order = 999)] public long? VersionId { get; set; } }

[Table("Tb_Geo_Zone")] public class GeoZona :BaseEntity { [Required] [Column("codice", Order = 1)] [StringLength(10)] public string codice { get; set; }

    [Column("nome", Order = 2)]
    [StringLength(10)]
    public string Nome { get; set; }
}

Sql autogenerated from Migration:

CREATE TABLE Tb_Geo_Zone ( ID bigint NOT NULL AUTO_INCREMENT, codice varchar(10) CHARACTER SET utf8mb4 NOT NULL, nome varchar(10) CHARACTER SET utf8mb4 NULL, CREATED_AT datetime(6) NULL, CREATED_BY_SESSION_ID bigint NOT NULL, UPDATED_AT datetime(6) NULL, UPDATED_BY_SESSION_ID bigint NOT NULL, VERSION_ID bigint NULL, CONSTRAINT PK_Tb_Geo_Zone PRIMARY KEY (ID), CONSTRAINT AK_Tb_Geo_Zone_codice UNIQUE (codice) );

Now it works!!!!

[skini82]

I'm having the same issue in EF Core 6.0.3 on VS2022 with SQLServer DB