Column ordering

[anpete]

Outcome from much discussion on this topic...

In EF6 we tried having column order match the order of properties in the class. The issue is that reflection can return a different order on different architectures. Although the order of properties in the model does not theoretically matter... it actually created a lot of very difficult to debug issues on EF6 where the order had a subtle effect on conventions, model diffing, etc. We managed to fix some of them (after many releases) but there are still some issues we have never really fixed. For that reason, metadata is stored in a deterministic (alphabetical) order in metadata in EF7.

In EF6, we also had a column ordering API, but this only impacts the initial create of the table. If you later change the order, migrations does not handle this (it would require a table rebuild). Also, if you add new columns, they are always appended to the table (since anything else also requires a table rebuild).

We could, however, store an ordering hint as an annotation on the model (based on the reflection order). This would only affect the order that migrations writes the columns in a create table call. That way it doesn't matter if it changes when you run your app on different platforms, since it's just an annotation that is ignored for the most part. If we wanted to, we could also flow an order that was specified in [Column] annotations into the hint field.

[bricelam]

Only for keys?

[anpete]

No, for fine grained control of table layout. See #2269.

[ChristineBoersen]

Just to add to this, adding the HasColumnOrder attribute isn't currently respected either and instead the columns are added in alphabetical order.

Is there a way to work around this incomplete feature in beta 6 ?

[ErikEJ]

@ChristineBoersen Why do you need to manage the colum order?

[ChristineBoersen]

There are times you need to write code at the database level and column order can become more important (inserting into a table without full column specs in a trigger for example). On a wide table, it can save time and since columns in sql databases don’t change order once written easily, is usually fairly safe.

In addition, I prefer to look at the model and data in the same column order 99% of the time so I am not hunting for information visually (example comparing query output debugging a query versus framework output). Maybe it’s a little OCD on my part as well as the consistency just “feels right”.

That or worst case obsolete the currently non-implemented .HasColumnOrder attribute so people know it is incomplete at least. :)

Christine

From: Erik Ejlskov Jensen [mailto:notifications@github.com]
Sent: Sunday, August 9, 2015 2:09 PM
To: aspnet/EntityFramework EntityFramework@noreply.github.com
Cc: Christine Boersen christine@daisytec.com
Subject: Re: [EntityFramework] Migrations: Need column order annotation/API. (#2272)

@ChristineBoersen https://github.com/ChristineBoersen Why do you need to manage the colum order?

—
Reply to this email directly or view it on GitHub #2272 (comment) . https://github.com/notifications/beacon/ANCv3frvA8bNvpsgfDiBF20GHBH12nS9ks5ol47YgaJpZM4Eqf6c.gif

[jmalatia]

Just created a db with 7.0.0-beta7-15540 and it created the table columns in alphabetical order... I would expect it to create the table in the same order as the model is written as in EF6. Db Column order is important for review as well as other things.

See also this issue #412 which I believe references a similar issue.

[CodeZombieCH]

I strongly agree with @ChristineBoersen and @jdaley (#2269 (comment)). Column order is of importance when you work with SQL management tools. It allows you to group columns that share a logical relation.

[rowanmiller]

This one is interesting... in EF6.x you theoretically get the order things are defined in the class but in actual fact it depends on what processor you run on. We saw plenty of cases where the model would change between x86/x64... this causes all sorts of headaches because it can have very subtle effects on conventions etc. and result in a different model between processors.

The other data point is that column ordering only works for the initial table creation. After that, columns are always added to the end of the table.

If you want to change the ordering though... you can just re-arrange things in the generated migration. That is explicit, and columns will be created in the order they are listed in the create table call. And we know it won't change, since the API calls are explicitly listed in code.

[RossAndrewMcLeod]

There is something else that I have noticed (small but annoying) when you scaffold out your model the order in CRUD Views is also alphabetical...

[rowanmiller]

@RossAndrewMcLeod - do you mean when you reverse engineer a model from the database? If so, we have an item to generate them in the same order they are defined in the database #4062

[ghost]

Just to add to this : in our use case, the development team creates the database with migrations, and another team (more business oriented) has a read access on this database directly through SQL Server Management Studio (they extract business data by querying directly in SQL). For them, it is VERY convenient to have the columns ordered in a "logical" order. We would like to be able to generate the migrations (at least the first migration) directly in this "logical" order, without having to re-arrange things in the generated migrations.

This order could be based:

• on the Order argument of the Column attribute, if it is provided ;
• otherwise, in the order of the property in the entity class

(columns with an Order argument explicitely given would always be positionned before)

[bricelam]

...the order of the property in the entity class...

FYI, this information is lost during compilation. The order of TypeInfo.DeclaredProperties is non-deterministic.

[divega]

If I remember correctly last time we discussed this subject we said that we could flow the reflection order (which as pointed out is considered non deterministic) as an annotation that then we could use as a hint when generating the migrations. That would help attain user exectations while not making our models harder to compare or hash. I am surprised not to see any notes about this in the bug, though, so I wonder if I am making it up or if there is another bug tracking that? @rowanmiller do you remember the conversation I am referring to?

[ghost]

I've made this patch to add this feature to 7.0.0-rc1 :

Reorder properties according to Column attribute ('Order' argument).

For an entity:
- Reordering is performed only if at least one property of the
entity has a Column attribute with an 'Order' argument
- Properties without the Column/Order argument are positionned
according to the order of TypeInfo.DeclaredProperties (although
it is non-deterministic, it is considered as a hint of user
expectation).

[rowanmiller]

@divega yep, I agree that we should do that. This means we have a hint to give to migrations about the order things should be created in the table... but it's completely independent of the order that metadata is defined in the model. It will give folks what they want in most cases.

[divega]

@rowanmiller Since @sebok has shown interest, do you think we can make this up-for-grabs?

[rowanmiller]

For sure, I'll add some notes to the description of the issue.

[andytaw]

Column order is of no great significance if all data operations go through EF.
I need to use SQL Bulk Copy as well as EF.
I could (will...) write a whole bunch of tedious code to manage the two, however I'd much prefer to use .HasColumnOrder().

[wgutierrezr]

I'm using Unit of Work approach and trying to automate a Table Original Record log, when accessing EntityEntry Metadata.GetProperties() the list returns ordered by FieldName. My classes have the correct order similar to my SQL Server tables. How can I get the properties without order? I'm using Core 2.1.2

[ajcvickers]

@wgutierrezr Properties in the model metadata are always stored using a stable ordering. Reflection order is not stable across all platforms and so cannot be used for this. You'll need to go back to the CLR types to find the reflection order.

[arman-g]

I just want to say that [Column(Order=x)] attribute is not respected in some cases. I am not sure if this is a bug or it's by design or some limitation but I think being able to control the order of the column is well desired. For example there are situations when we want to add some properties to an existing model (IdentityUser) that we have no way of changing the order of its members (please see the images attached). I hope there is a fix to this problem.

[ghost]

I currently have a BaseEntity that all my entities inherit from and I have tried setting the column order as follows:

    public abstract class BaseEntity
    {
        [Column(Order = 100)]
        public DateTime? CreatedAt { get; set; }
        [Column(Order = 101)]
        public DateTime? UpdatedAt { get; set; }
    }

But the order of the column is not being respected, they are being put at the beginning of the table instead of the last. This is a .NET Core 2.2 project. Anyone know why this isn't working?

[ajcvickers]

@TachyonRSA ColumnAttribute ordering is being tracked by #10059

[subbiahkalidasan]

I am facing the same issue in 3.0.1, Its not fixed in earlier versions?

[isukces]

I haven't yet tested this on core 3.

[karimkun]

The problem still persists in EF core 3.1.0, i hope this will be fixed asap

[bricelam]

This issue was resoved in EF Core 2.1.0. Please provide feedback on #11727, #11314, or #10059 or file a new issue if what you want isn't covered by those.

[TanvirArjel]

@bricelam

This issue was resolved in EF Core 2.1.0.

In which patch version? Because EF Core 2.1.0 has been released on 5/29/2018. Please mention the patch version too.

[subbiahkalidasan]

This issue was resoved in EF Core 2.1.0. Please provide feedback on #11727, #11314, or #10059 or file a new issue if what you want isn't covered by those.

If it is fixed in 2.1.0, How come its not working in 3.1.1?

[bricelam]

@subbiahkalidasan Could you file a new issue (if not already covered by the ones listed) explaining exactly what you mean by not working?

[premchandrasingh]

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

[videokojot]

@premchandrasingh Hi, I tried your solution, but it does not work... HasColumnOrder is not respected... :( do you have any idea. I am on .NET Core 3.1..

[amitkandwal416]

When will it fixed this error.

[bricelam]

@amitkandwal416 This issue was resolved in EF Core 2.1.0. Are you looking for issue #10059?

[xguan2014]

@premchandrasingh Hi, I tried your solution, but it does not work... HasColumnOrder is not respected... :( do you have any idea. I am on .NET Core 3.1..

[XnAndrew]

Best option is to use MySQL... Then your migrations can have Alter table... After" commands. Sadly Microsoft SQL doesn't :(

[XnAndrew]

Alternative.. Rebuild the Model from the db... (scaffold with -force) and recreate the initial migration...

[pgiacomo69]

Today, I'm trying .NET 5 RC2, compiled my Blazor app, update-database to create it, and guess what? The [(Order=n)] Attributes that where in entity fields suddenly worked! (They never did until now)

[jemiller0]

I guess miracles really do happen. It only took how many years for this basic functionality?

[Matt11]

From my experience, with EF 5.0.6 (latest stable version 2021-06-08) - this still does not work (using MS SQL Server).

[bricelam]

@Matt11 Could you clarify what you mean by does not work? Are you looking for #10059?

[Matt11]

@bricelam - exactly, trying to specify in which order the fields are being created via migration/code first, via [Column(Order = x)] attribute. Despite of specifying it for a table, EF core does not follow this order (as you can see via Sql mgmt studio).