Efcore: Support ColumnAttribute.Order

[bricelam]

2272 matched the CLR reflection order for columns within a table.

During design, we decided to wait for more feedback before honoring the value of ColumnAttibute.Order on the properties.

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.

[yitzchokneuhaus1]

@bricelam Is this enhancement scheduled for the near future to be implemented in either EF Core or EF6?

[bricelam]

It works in EF6. Still on the Backlog of EF Core.

[samconn]

+1 for implementing this feature, and sooner rather than later.

We're porting an SAAS application from EF6x to EFCore and were a wee bit taken aback to find that the Order designation on the ColumnAttribute is being ignored.

But kudos to the whole team (and community!) for at least going open-kimono on the design discussions to at least help us understand the reasoning.

[bricelam]

@andytaw mentioned another good case for this:

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().

[MelGrubb]

Those who work in the database all day tend like their columns in decreasing order of "importance", with the most significant or often-used coming at the start, and picky details way over on the right out of the way, or sometimes they like them clustered in neighborhoods where related fields are next to each other. Either way, this means that the column order can be significant to some team members. Meanwhile, over in the land of code, I like to use tools like ReSharper or CodeMaid to automatically sort my C# code, which _drastically_ reduces merge collisions. Really, I can't recommend it enough for team projects, it's a huge gain in productivity.

As it stands now, I can't make both sets of developers happy. I really need a way to specify the order of the database columns, and it seems to be that using the existing DataAnnotations' ColumnAttribute would be an ideal way to do this.

As for strategy, I'd recommend putting the properties with the attribute first, in increasing order according to the Order property of the attribute, and then the remaining properties in their existing "natural" order, although that order is non-deterministic and shouldn't be relied upon to remain stable in the future.

[pwen090]

@bricelam you mentioned it on #2272 but to reiterate and upvote here if we have something like:

```c#
public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
}

public class RepoBlog : Blog
{
[Key]
public int Id { get; set; }
}
```

This will cause the primary key Id to be output as the last column. I know we can edit migration files but surely somewhere along the lines someone will forget to do that and make it a pain to clean up. It would be great if we can either support Order attribute or at least have the generators try harder to always put key columns first.

[bricelam]

@pwen090 Tracked by https://github.com/aspnet/EntityFrameworkCore/issues/11314#issuecomment-375159209

[smitpatel]

Afaik the model posted by @pwen090 wouldn't work if Blog is mapped since derived types cannot have keys.

[davisnw]

Is there any workaround right now before this gets implemented? HasColumnOrder doesn't seem to be supported either, and it makes it a real pain to check that the mappings are in sync with a database that is not primarily under the control of EntityFramework. E.g, generate database with e.g. EnsureCreated and doing a schema comparison with SSDT - but if I can't control EF column order, the comparisons don't line up. I was thinking there might be something in the metadata api, but I haven't found it yet if it exists.

[ajcvickers]

@davisnw I'm not aware of any workaround for using EnsureCreated directly. The normal workaround is to scaffold a migration and arrange the column order in the migration.

[kayakingcoder]

As for strategy, I'd recommend putting the properties with the attribute first, in increasing order according to the Order property of the attribute, and then the remaining properties in their existing "natural" order, although that order is non-deterministic and shouldn't be relied upon to remain stable in the future.

This would not work in our scenario where we have LastModifiedDate and LastModifiedUserId as properties in a base class. I'd want a way to put those two fields at the end of my tables (as per the "importance" that @MelGrubb mentions) without being forced to put an order attribute on every property of descendant classes, instead they currently appear straight after the id.

I would prefer this strategy from https://linq2db.github.io/api/LinqToDB.Mapping.ColumnAttribute.html
"Specifies the order of the field in table creation. Positive values first (ascending), then unspecified (arbitrary), then negative values (ascending)."

[MelGrubb]

Couldn't you just put attributes with ridiculously high values on the base classes?

[kayakingcoder]

Couldn't you just put attributes with ridiculously high values on the base classes?

It doesn't matter how high you make those values, if the rule is to put the columns WITH the attribute first, then we'd be forced to put the attribute on every other column. The way they've done it in Linq to DB per that link I gave is just more flexible, why not go with that?

[liamwang]

It would be more perfect to make below conventions as default:

1. Primary keys first
2. Base class properties last

That way, I don't even need to set ColumnAttibute.

For example:

public class AuditEntity
{
    public int Id { get; set; }

    public int CreatedBy { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.Now;
    public int UpdatedBy { get; set; }
    public DateTime UpdatedAt { get; set; } = DateTime.Now;
}

public class Member : AuditEntity
{
    public string Name { get; set; }
    public string Avatar { get; set; }
}

The ordered columns of Member should be:

Id,
Name,
Avatar,
CreatedBy,
CreatedAt,
UpdatedBy, 
UpdatedAt

[zgonzales]

Would love to see this implemented, really wish that when adding a new column to a table it would be ordered before the less important existing inherited fields instead of at the end.

[davisnw]

Can someone explain the resistance to allowing us to simply specify the desired order somewhere in our EntityFramework mappings rather than having to scaffold and manually edit migration files?

1. Order attribute is not supported
2. Specifying order via fluent api is not supported
3. Order doesn't doesn't seem to be exposed via the metadata api in any way that would let us write our own convention (this would provide the greatest flexibility for projects to order as they please)

I suggest that Entity Framework Core should not put so much effort into guessing how we like our column orders, and just let us specify them easily in our mappings. Different projects have different rules and different interactions between the application and database administrators.

[roji]

Note that databases in general do not support adding columns to a table in a specific position, or altering the position of an existing column (SQL Server, PostgreSQL) - any change in order requires a table rebuild, which can be quite complex and seems problematic for something as cosmetic as a column ordering change. So while something could be implemented here for initial table creation, as the schema evolves things would get out of sync.

@davisnw if you look at the comments above, you'll see that people are asking for different things - automatic ordering by EF Core when using inheritance, or just manually via the attribute, or both. This isn't to say nothing should be implemented - just that some careful thought into the precise design is needed.

[davisnw]

@roji Thanks for the response.

With regards to "adding columns in a specific position ... requires a table rebuild" for many cases, that is not a big deal (tables are relatively small). Though I agree that if you are using entity framework for migrations, that the need for a table rebuild should be surfaced to the programmer.

With regards to column order being "cosmetic" - column order is for the humans, who often have strong preferences for a specific column order, because they are often running queries directly in the database, bypassing Entity Framework entirely.

With regards to "as the schema evolves things would get out of sync.", Entity Framework Core is not always the primary driver of database changes. I somewhat frequently work on projects where Entity Framework is just the "application glue", and "keeping things in sync" is typically going from the database changes (sometimes managed by Sql Server Data Tools) back to Entity Framework, and thus have little use for Entity Framework Core migrations (other than hacking column order to make schema comparison easier). Being able to specify column order more simply would make that job easier.

With regard to people wanting different things, that is why I suggest that it should be easier for us to specify our own column order (understanding that ColumnAttribute.Order may not be the clearest way to do that).

I did find (off of issue https://github.com/dotnet/efcore/issues/11314#issuecomment-586265217) that someone managed to do HasColumnOrder(...) (not ColumnAttribute.Order) by using "internal" apis (so could be broken in the future).

So perhaps I should file a separate issue regarding the metadata api - have to do a little more digging on that.

[roji]

@davisnw thanks for the additional comments - just to be clear, I wasn't saying we shouldn't do anything, just voicing some observations.

With regards to column order being "cosmetic" - column order is for the humans, who often have strong preferences for a specific column order, because they are often running queries directly in the database, bypassing Entity Framework entirely.

I don't doubt that - maybe the word "cosmetic" was too strong; I only meant that it has no effect on EF Core and on typical programmatic access methods to the table. The 63 votes on this issue do show that people care about this for sure.

With regards to "as the schema evolves things would get out of sync.", Entity Framework Core is not always the primary driver of database changes

That's absolutely true, but in that case the problem of adding columns and enforcing column order is out of EF Core's scope, isn't it? It becomes the concern of whatever external tool you use to change your database schema.

[MelGrubb]

I'd say it's more than cosmetic. Whether you try to pretend the database is there or not, eventually you end up opening SSMS and "SELECT * FROM WHERE"ing a table to track down a problem. At that point, the order of the columns becomes important. On a wide table with several fields that are inherently more "interesting" than others, I want to see the interesting ones first. Audit fields like created and modified date/by are pretty boring and yet "created" alphabetizes right up there at the front, taking screen space away from the things I wanted to see. Yes, I can hand-craft a query to get to the good parts, but do you have any idea how many times a day I right-click and select the top 1000 rows? It's ridiculous.

As for having to rebuild the table. I'd say EF should just do its best with what it has. The first time the table builds, it should obey my ordering attributes absolutely. After that, when I add columns, it should use the metadata to add the new columns properly ordered in relation to each other, but not necessarily in relation to the existing columns.

Perhaps an optional parameter or configuration option could say that you are okay with the full table rebuild, and to just go ahead and do it, but by default, I'd favor the simpler faster option. New stuff goes at the end, sorry.

[videokojot]

As for strategy, I'd recommend putting the properties with the attribute first, in increasing order according to the Order property of the attribute, and then the remaining properties in their existing "natural" order, although that order is non-deterministic and shouldn't be relied upon to remain stable in the future.

This would not work in our scenario where we have LastModifiedDate and LastModifiedUserId as properties in a base class. I'd want a way to put those two fields at the end of my tables (as per the "importance" that @MelGrubb mentions) without being forced to put an order attribute on every property of descendant classes, instead they currently appear straight after the id.

I would prefer this strategy from https://linq2db.github.io/api/LinqToDB.Mapping.ColumnAttribute.html
"Specifies the order of the field in table creation. Positive values first (ascending), then unspecified (arbitrary), then negative values (ascending)."

This is also our desired scenario, we want to have some columns from abstract base class in the beginning and some columns last. Thanks.

[mjavad007]

Implementing this would also help in adding composite key using data annotations. if ef6 we could use column to denote the order of composite key. Since this is not implemented in ef core we get error "entity type has composite type defined with data annotations, use fluent api" in ef core. So implement this or add parameter to key to denote order of composite key.

[ajcvickers]

@mjavad007 Conflating column order with key order was a mistake in EF6 since the two can be different. #11003 is tracking composite key mapping by attribute.