Column ordering
anpete opened this issue · 105 comments
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.
Only for keys?
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 ?
@ChristineBoersen Why do you need to manage the colum order?
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
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.
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.
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.
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...
@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
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
Orderargument of theColumnattribute, 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)
...the order of the property in the entity class...
FYI, this information is lost during compilation. The order of TypeInfo.DeclaredProperties is non-deterministic.
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?
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).
@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.
@rowanmiller Since @sebok has shown interest, do you think we can make this up-for-grabs?
For sure, I'll add some notes to the description of the issue.
@rowanmiller, sorry for the late reply. No not when reverse engineering an existing database model. Just that when you use the 'New Scaffolded Item' on a POCO you would expect the items to be layed out in the order they were declared in the model.
@smitpatel talk to @bricelam about how to implement this - we think it can be contained completely within migrations (in the ModelDiffer rather than using annotations)
I came up with a work around for this issue in an ASP.NET 5 web application. Before the first migration run the "migrations add" command. Then open the migration file that is generated in the Migrations folder. Arrange the columns in the order you want them in. Then run the "database update" command.
Unfortunately I can't figure out a way to order columns that are added after the first migration. But you can go into MS SQL and reorder those columns. And that order will stick even after updating the same table with subsequent migrations.
Unfortunately I can't figure out a way to order columns that are added after the first migration.
There isn't anyway to do this since SQL Server requires a table rebuild (rename existing table, create new table, copy data, delete old table) to re-order the columns - you can only add columns to the end of the table. We may support this later, we have #329 tracking the enhancement.
Yeah, now that you say that it seems obvious. Because in SSMS you have to turn on the Drop/Create option to be able to move the columns around. Thanks for your time and for the link to the issue tracker.
Again changed to milestone!!!. Let me know that when it will take?
Will this be closed for 1.0.1 milestone? I started using asp .net core and I didn't like the alphabetical order of fields. Also, there is not much of help of the web on how to deal with it. I hope this issue will be resolved by the next update.
@vseyedan There is no plan to change this in any of the currently scheduled releases. The correct thing to do it you don't like the column order is to edit the generated migration file and move the order around as desired.
Try simple fix https://github.com/isukces/EfCore.Shaman . It is also available as Nuget package https://www.nuget.org/packages/EfCore.Shaman/ .
It changes column creation order at least in CREATE TABLE statement.
Situation can be improved with complex type support, of course if Ef Core will keep complex type attributes together.
Important: what there people talk is not definitely about "custom columns order", but it is about organizing columns to islands: e.g. such columns as Voltage and SetpointForVoltage everybdy would like to keep together in a hundred columns table (measurements). Another example CreateAt and RowVersion columns/attributes.
So, will column ordering in the database at some point match what is specified using ColumnAttribute(Order=) at some point? I was surprised to see that this doesn't appear to work. Also, I was surprised that there doesn't appear to be a fluent API method for specifying the column order. Isn't that the whole point to that attribute? I'm running into the problem because I'm generating code from my model that does bulk loads using SqlBulkCopy and apparently it needs the columns in the exact order that they are in the database. I think I could get it to work if the columns were sorted by property or column name. It doesn't look like that's the case though. The order looks arbitrary. I can see how migrations might complicate things, but, it would be nice if it at least worked when you call Database.EnsureCreated().
@jemiller0 at this stage EF Core has no concept of column ordering, though this issue is tracking adding that. If you use migrations, you can simply reorder the columns in the CreateTable call. But, as you are using EnsureCreated that isn't an option for you.
Thanks. I think I found a workaround to my immediate problem regarding SqlBulkCopy. I was able to specify the column ordering using the SqlBulkCopy.ColumnMappings collection. It is definitely an annoyance not being able to specify the column ordering in the table though.
SSDT team says there are no plans to "Ignore column order" in schema compare or dacpac publish
EFCore team is wondering: "Why do you need it?" and stating that there are no plans to "concept" column order.
As for now, scafold-ing a DB into context and then recreating a new db from the context (whitout ad hoc coding) results in a different schema
I don't see why this can't at least be done at least for EnsureCreated() if you have Column(Order) attributes specified. It seems like a glaring omission to me. Especially, considering you have an attribute defined for just this purpose.
@jemiller0 it absolutely can, the proposal described at the top is what we intend to implement. It's just a matter of priority and order that we implement the many things on the backlog. BTW if you are using migrations, you can just manually reorder the column order in the scaffolded migration.
EFCore team is wondering: "Why do you need it?" and stating that there are no plans to "concept" column order.
We do plan to add it, it's just one of many items on our backlog.
I have my old application with more than 100 tables and the columns on each table are well organized. After Scaffolding my MS SQL DB into EntityFrameworkCore all the columns order in the cs files classes generated for each table match with the DB. But after run the command to create a new migration, the columns in each table was alphabetically reordered. Edit the migration cs file to fix this issue will take me my whole life to do it.
Is there any update to fix this issue?
Regards.
@wgutierrezr Thanks for the feedback. I don't have anything to add above that which Rowan posted previously.
Curious: is your intention to re-create the database and all columns from scratch using Migrations?
"Curious: is your intention to re-create the database and all columns from scratch using Migrations?"
Hi
That's my intention. @ajcvickers Thank you for your answer.
Any Update on That?
(Any UserVoice place to vote for it?)
I Really need this too!
Thanks.
I have the same problem. I need ordering columns.
Still not implement it, it is an annoying missing feature.
It seems like something is wrong with respect to adding new features to EF Core. I think there needs to be more frequent releases that address high priority issues. I'm amazed that it has gone as long as it has without a new release. One problem that I ran into was the one where adds in extra ORDER BY columns on queries which slows them down if you are working with tables with a lot of rows. It seems like the EF team bit off more than they can chew. Maybe they need to focus on a smaller number of new features with each release. Or, at least have a release for bug fixes. Is it really that hard to just preserve the column ordering rather than sorting them alphabetically? Even if the ordering could be different on different platforms because of reflection, it would be better than sorted alphabetically. I gave up and wrote my own code for generating my schema. The problem is that I've ended up doing more and more of that to the point where I almost ask myself, it is even worth using EF and I've been a huge fan and proponent of EF overall.
@jemiller0 I think the team would much prefer more frequent releases, but currently their release cycle is much too cloesly tied to the .NET Core / .NET Standard release cycles. Once .NET Core 2.0 has been released, lets hope for a more frequent/stable release rate of for example 4 releases per year.
I gave up and wrote my own code for generating my schema.
@jemiller0 Are you using DbContextOptions, overriding methods on the DbContext or Migration, or something altogether separate? I'd be curious to see your implementation, can you share?
What I have is separate from EF. Basically, it's a console application that you can point at a database, an assembly with a DbContext in it, or an XML file. It's hackish, but, works for what I need it for. Basically, I just point it at the assembly with the DbContext in it and it uses reflection to enumerate the entities and gets the information from the attributes. It won't pick up things that are configured using the fluent API. However, I have a way of configuring it programmatically. So, it's not really a solution that is fully baked. It uses T4 templates for generating a number of different things. I also generate code for doing bulk inserts/updates (something that I found that EF is far to slow to do). It also can generate a web app from a DbContext similar, but, more robust than what ASP.NET Dynamic Data does. I also generate a number of methods so that I can add/remove tables, indexes and foreign key constraints on a per-table basis which I have found useful for doing things like bulk loads. For example, I can do things like easily drop foreign key constraints referencing a table, truncate the table, drop the indexes, bulk load data, and then re-add the foreign key constraints and indexes so that I can speed up the load as much as possible. Long story short, I have found that being able to generate code for a lot of this stuff is very helpful. Particularly with some of the projects that I've been working on that have over a thousand tables. I've also used it to generate code for working with Dapper and NHibernate so that for example, in the web app, I can switch between them and EF and EF Core to see what works best. Ideally, I would like to use EF Core for everything, but, I've ran into problems here and there and basically have a mess of a number of different technologies in use at the moment.
Is there any updates on this? Please provide some ETA.
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. However, keep in mind that there are many other high priority features with which it will be competing for resources.
Also, this item is marked up-for-grabs, meaning this is something we think could be tackled by an external contributor, which could result in it being addressed sooner than might be the case if it has to compete with other items for the team to work on.
It has already been written there https://github.com/isukces/EfCore.Shaman
It could be a great thing to order columns (automatically) based on the order you define the attributes in your class.
@ktrkumarbablu, this is just another way to do this. I think is a good approach since you order the attributes as you wish. Obviously, my idea is not about dropping the old way, just to have an easy way and a fully customizable way.
@mhosman Sure. I completely agree and it would keep your code a little cleaner too. I think it would be a good option to have if there is a reliable way to do it. Maybe another class level attribute for doing that.
For Example, something like [OrderColumnsByClassOrder]. It would be nice to have options for sure.
It is a "good" option as database administrators do not like to see messy tables in their workbench.
Design notes
This work will be scoped to just the scaffolded MigrationBuilder.CreateTable() calls (and DbContext.Database.EnsureCreated()). No additional metadata will be added to the model.
We support some pretty advanced models. Here are some advanced scenarios and the behavior we'll use when ordering.
| Scenario | Behavior |
|---|---|
| Multiple CLR types due to inheritance or table splitting. | Sort topologically |
| Multiple CLR properties for a single column. | Arbitrarily pick one |
| Composite shadow foreign key properties. | Use navigation and key order |
| Shadow properties | Sorted alphabetically afterward |
We had some open questions that we'd like to gather more feedback on:
- Should the primary key always be first?
- Should
ColumnAttribute.Orderhave any influence?
When a database is open and read using human tools, it’s much much better to see its content in the same order as designed in the matching model class.
I believe it should be ordered from the base class to the current in order of clr properties being a PK or not. If the developer is not happy about it, he could just change the order. After this we could include the shadow properties in order of declaration on OnModelCreating if possible.
I like the PK to be always first. I can't give arguments for or against though, it's just an habit I have and I often see when designing a database.
I think ColumnAttribute should be used to allow the user to tweak the order specifically, and also because users probably expect it to do so... It should have priority over the order in which the properties are defined in the classes (which is in theory not guaranteed)
I think the PK always on Top and option to Sort or Not Sort fields. Particularly I prefer to have the same order for fields as I have in my DB.
The pk can be multi columns
The only case where having EF (as opposed you) putting the PK first is tf you have an abstract base class without the PK.
abstract class EntityBase
{
public DateTime CreateDate { get; set; }
}
class Customer : EntityBase
{
public int Id { get; set; }
}Since the base-most type will be listed first, the order will be CreateDate, Id, and you can't exactly reorder CLR properties.
If we do nothing, the workaround is just to rearrange them in the scaffolded migration.
Implementing this, it turns out there are many things to consider:
- Column sharing
- ColumnAttribute.Order
- Composite keys
- Foreign keys
- Inheritance
- Owned types
- Primary keys
- Shadow properties
- Table splitting
- Nesting/transitive relationships
We'll discuss as a team in the next design meeting just how much we should initially support.
Additional design notes
- Primary keys will always be first
- If shadow and composite, use the key order for components
- When more than one CLR properties for a column, arbitrarily use one for the order
- When no CLR property for a column, order them alphabetically afterwards
- Fallback to a CLR navigation property when no CLR property for a foreign key column
- If composite, use the key order for compontnes
- Columns of owned types will be injected in place of their defining CLR navigation property
- For inheritance and table splitting, group columns by their defining type sorted topologically. Sibling order should be deterministic
- We won't support
ColumnAttibute.Orderyet. #10059 will track this work and gather feedback
The order the properties of the class are written should be the order they are created in the DB. I understand there are other issues that come into play (inheritance, etc.), but consider this simple class/table:
Name
Address
City
State
Zip
Email
Phone
Everyone would expect them to be in this order... but applying an alphabetical sort you get this:
Address
City
Email
Name
Phone
State
Zip
This is a simple example with limited fields and sorting alphabetically makes no sense.... now imagine that with a much larger DB model... it would be a complete mess.
I would have to assume that most people order their classes/table fields grouping similar data together (at least everyone I have dealt with). And order it in some sort of way that makes sense for that model.
And drastically changing that order performing an alphabetical sort is completely against everything normal and expected.
They are countless times one will interact with the raw database (SSMS, LINQPad, Crystal Reports, etc.) and it needs to make sense... let alone what an actual DB admin would say if everything is sorted alphabetically or not having similar fields grouped together!
Yes, for this simplest case, we all agree:
class Customer
{
public string Name { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Zip { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
}...will produce...
CREATE TABLE Customer (
Name TEXT,
Address TEXT,
City TEXT,
State TEXT,
Zip TEXT,
Email TEXT,
Phone TEXT
);Most of the design notes are just around the nuances of the implementation. (The stuff 80% of users probably won't think or care about.)
@bricelam That's is exactly I need. How about when doing reverse engineering from an existing DB?
Yes, we already preserve the column order when reverse engineering the entity types.
What about DisplayAttribute, which also has an Order property? Not sure if that should be taken into consideration.
On an unrelated note, I just noticed that ScaffoldTableAttribute isn't present in .NET Core or .NET Standard 2. Which is odd since ScaffoldColumnAttribute is present.
IMO DisplayAttribute is related to the view part of an application and its Order should be something about the view, by opposition to ColumnAttribute which is related to the model and its Order should be taken in consideration by Entity Framework.
Hi,
At first, I'd like to thank you for your great work on EF Core.
I'm new to EF Core and used EF6 in the last years. While working on a new project I'm getting familiar with EF Core and ASP.NET Core.
I wonder if it would be possible to have the column creation order a bit more like it was in EF6.
Let me explain. I got used to always create an entity base class like this:
public abstract class EntityBase
{
[Key]
public Guid Id { get; set; }
[Required]
public DateTimeOffset CreatedAt { get; set; }
[Required]
public string CreatedBy { get; set; }
public DateTimeOffset? ChangedAt { get; set; }
public string ChangedBy { get; set; }
public string Info { get; set; }
}The entities are derived from this base class like this:
public class Department : EntityBase
{
public string Name {get; set;}
}My Entities always rely on this layout.
In EF6 the column order of the table has been automatically created like this:
CREATE TABLE Departments (
Id UNIQUEIDENTIFIER,
Name TEXT,
CreatedAt DATETIMEOFFSET,
CreatedBy TEXT,
ChangedAt DATETIMEOFFSET,
ChangedBy TEXT,
Info TEXT
);I liked this layout because there are some advantages. I prefer to have the PK at the the first column. The special entity data will follow. And one always knows that entity base information can be found at the end of the table. (formerly I used a complex type for this)
Regards
Sven
@sven5 @ktrkumarbablu This issue has been fixed in version 2.1.0. You can try it out using the nightly builds, or wait for the 2.1.0-preview1-final release which is expected sometime this month.
Hi Brice, thanks for the update.
@bricelam Will there be a way to correct the order of the columns from already applied migrations once 2.1.0 is officially released? We attempted to try out the 2.1.0-preview1-final but we had to pull back from the dependency snowball (needing dotnet sdk 2.1.0-preview1, asp.net core, etc). This weekend was our last chance to be able to rebuild the database but hope in the future we can non-destructively fix the column order. Thanks
No. CLR property order is only used when scaffolding the initial CreateTable call. After the migration is generated, you are free re-arrange the columns in whatever order you want (same as before this feature).
If you add a new property, we won't rebuild (drop and recreate) the table to make it match the CLR property order. We'll simply issue an ALTER TABLE..ADD COLUMN statement and the database will append it to the end of the existing table.
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().
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
@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.
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.
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?
@TachyonRSA ColumnAttribute ordering is being tracked by #10059
I am facing the same issue in 3.0.1, Its not fixed in earlier versions?
I haven't yet tested this on core 3.
The problem still persists in EF core 3.1.0, i hope this will be fixed asap
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 Could you file a new issue (if not already covered by the ones listed) explaining exactly what you mean by not working?
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
@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..
When will it fixed this error.
@amitkandwal416 This issue was resolved in EF Core 2.1.0. Are you looking for issue #10059?
@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..
Best option is to use MySQL... Then your migrations can have Alter table... After" commands. Sadly Microsoft SQL doesn't :(
Alternative.. Rebuild the Model from the db... (scaffold with -force) and recreate the initial migration...
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)
I guess miracles really do happen. It only took how many years for this basic functionality?