0

I have a model

    public class Category
    {
        [Key]
        [Column(Order = 0)]
        public int Id { get; set; }

        [Required]
        [Column(Order = 1, TypeName = "NVARCHAR")]
        [StringLength(100)]
        public string? Name { get; set; } 

        [DisplayName("Display Order")]
        [Column(Order = 3)]
        public int DisplayOrder { get; set; }

        
        [Column(Order = 2, TypeName = "SMALLDATETIME")]
        public DateTime CreatedDateTime { get; set; } = DateTime.Now;
      

    }

When i use TypeName and StringLength properties inside Model

PMC > add-migration NewMigration
PMC > update-database

This overrides DataTypes inside SQL Server.

But when i try to change [Column(Order = ?)] property inside my model it is not overriding. After i delete migrations in Project and Database inside SQL Server and run those commands again it works perfectly.

I want to override Database column orders without deleting Project Data folder and DB.

Thank you for help.

UPinar
  • 1,067
  • 1
  • 2
  • 16
  • Overriding what? And why? Column order doesn't matter. There's no way to change the order of columns in a table without actually dropping it and creating a new one. Data in relational databases is stored in *rows*, so changing column order means all rows have to be rewritten. That's as expensive as rewriting the table and would actually require to create a new temporary table. That's why databases don't allow this. When you change the column order in eg SSMS, the generated script creates a new table with the new order and copies all data there, then deletes the old and renames the new table – Panagiotis Kanavos Feb 25 '22 at 07:39
  • Can you tell me the name of the scripts. I want to check it. And you are telling that with using EF can not execute those scripts ? – UPinar Feb 25 '22 at 07:41
  • No you can't. I already explained what SSMS does. It doesn't reorder columns, it creates a new table. Just click on the `Generate Change Script` button instead of `Apply` to see the actual script. Again, why do you care? The column order doesn't matter, especially when using an ORM. – Panagiotis Kanavos Feb 25 '22 at 07:42
  • Where is Generate Script button? Yeah for the learning process i am using ORM – UPinar Feb 25 '22 at 07:45
  • That's why you get a warning when you try to modify a table with lots of data. It's because SSMS will have to create another large table, causing significant delays – Panagiotis Kanavos Feb 25 '22 at 07:45
  • The [Generate Change Script](https://learn.microsoft.com/en-us/sql/ssms/visual-db-tools/save-change-script-dialog-box-visual-database-tools?view=sql-server-ver15) button is the scroll-like button in the toolbar above the designer. It's also available in the menu that appears when you right-click in the empty area inside the designer. SSMS can generate a script for almost all actions, eg backups, shrinking, creating new logins. Almost all dialog boxes have a `Generate Script` toolbar button – Panagiotis Kanavos Feb 25 '22 at 07:48
  • ALTER TABLE dbo.Categories SET (LOCK_ESCALATION = TABLE) this is the column that i see in Generate Change Script this is altering? I still dont understan what you said :) – UPinar Feb 25 '22 at 07:51
  • FYI, for SSMS to generate those script, you have to uncheck "Prevent saving changes that require table re-creation" in SSMS's Designers options. – Gert Arnold Feb 25 '22 at 07:52
  • Okay i see now it said the changes you have made require dropped and re-created. Can you write the first comment that you write to answer pls. @PanagiotisKanavos – UPinar Feb 25 '22 at 07:54
  • Why do you care about column order though? There may be ways to solve your actual problem. For example, you can change the order columns are displayed in a grid with the `Display` attribute. In a SELECT query you can specify columns in any order you want. If you use a DataTable, you can access columns by name. Save with a `DbDataReader` – Panagiotis Kanavos Feb 25 '22 at 08:04
  • Yeah i know for me in that model there was not any problem but in another model i have 2 FK column which are the last properties in order so i wanted to change their order to after PK, than i try to change it with EF but it did not work. The question is, is there any way to solve it but you tell me that no and when we think about large tables it make sense. We can use another approaches but now i am learning this and when i go with DBFirst or another ORM like Dapper maybe i will check another options – UPinar Feb 25 '22 at 08:14
  • So your real question is how to specify relations with composite keys in EF Core? That's already supported. You can specify the composite key in the `OnModelCreating` method, eg `.HasKey(c => new {c.CategoryId1, c.CategoryId2});`. You can also specify the columns with the `ForeignKey` attribute, eg `[ForeignKey("CategoryId1, CategoryId2")]`. Both options are shown in [this question: Composite Key as Foreign Key](https://stackoverflow.com/questions/5436731/composite-key-as-foreign-key) – Panagiotis Kanavos Feb 25 '22 at 08:45
  • No real question is the question that i asked in question. I already created Foreign Keys but i can not change their `[Column(Order = 1, TypeName = "NVARCHAR")]` `[StringLength(100)]` Column(Order) property with EF Core. Actually i still dont understand we can change columns **StringLenght** but we can not change **Column order** . In your comment you told that table is recreated in db when changing column order but is it not happening in changing nvarchar(max) to nvarchar(100) ? @PanagiotisKanavos – UPinar Feb 25 '22 at 09:02

0 Answers0