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.