Order the Columns, while creating DB by Entity Framework 4.1. with fluent API

Question

I want have an order of my database table columns after creating DB by entity framework 4.1. in the following way:

1) PK 2) all foreign key columns 3) all complex types columns 4) all Other Columns

The problem is that it is no possibility to set the Order for foreign key's by fluent API, like for example HasColumnOrder for primitive properties.(all foreign key columns are the last columns)

Are there some ideas?

Thanks

Chris

score 3 · Answer 1 · answered Jul 15 '13 at 16:28

I know this is an old thread, but I thought I would actually answer it. I agree, the column order in the DB makes no difference. Except for when you are in the DB searching on data, then it can be useful to have the logical/useful fields first.

The answer is with the .HasColumnOrder(x) method. Just put in a zero-based number of what order you want the field to be in. See an example here: http://hanksnh.wordpress.com/2011/04/08/inheritance-with-entity-framework-4-1/

score 1 · Answer 2 · edited May 23 '17 at 10:30

1

If you want to set order for foreign key columns you must expose them as properties (use foreign key association instead of independent association). Btw. order of columns in database table doesn't matter. Only order of columns in a key matters and it is why there is no brother support for this.

edited May 23 '17 at 10:30

Community

1
1

answered Aug 08 '11 at 11:21

Ladislav Mrnka

360,892
59
660
670

2

Oh belive me order of columns does matter in weird corner cases so theorethically it does not matter but in real life it is different. – Mateusz May 14 '15 at 19:50

score 0 · Answer 3 · answered Aug 21 '15 at 11:07

I have solved the problem with foreign key columns order in the database while using independent association with the help of EF migrations.

Disable automatic migrations and create initial migration for your data model.

For model class:

public class Session
{
  public int? Id { get; set; }     
  public Track Track { get; set; }   
  public Car Car { get; set; }    
  public int Event { get; set; }
  public DateTime Date { get; set; }    
  public string Name { get; set; }    
}

the migration code will be generated:

CreateTable("dbo.Sessions", c => new
{
  Id = c.Int(nullable: false, identity: true),
  Event = c.Int(nullable: false),
  Date = c.DateTime(nullable: false),
  Name = c.String(nullable: false, maxLength: 64),
  Car = c.Int(nullable: false),
  Track = c.Int(nullable: false),
}) ...

Now just reorder the foreign key columns (Car, Track) by moving them up. When you create new database and open the table, the column order will be like expected.

score 0 · Answer 4 · answered Dec 22 '15 at 12:55

If you are looking for column order, I think its pretty easy. In your DbContext class, override OnModelCreating. Then grab modelBuilder, and from it pull out EntityTypeConfiguration. Then using it configure the order as follows.

public class AppDbContext : IdentityDbContext<AppUser, AppRole, int, AppUserLogin, AppUserRole, AppUserClaim>
{
    public AppDbContext() : base("AvbhHis")
    {

    }

    public DbSet<PatientCategory> Product { get; set; }
    public DbSet<LookupBase> LookupBase { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        modelBuilder
            .Entity<PatientCategoryLookup>()
            .Map<PatientCategoryLookup>(m =>
            {
                m.ToTable("LookupPatientCategory");
                m.MapInheritedProperties();
            });
        EntityTypeConfiguration<PatientCategoryLookup> config = modelBuilder.Entity<PatientCategoryLookup>();
        config.Property(e => e.Id).HasColumnOrder(0);
        config.Property(e => e.PatientCatgCode).HasColumnOrder(1);
        config.Property(e => e.PatientCatgName).HasColumnOrder(2);
        config.Property(e => e.Description).HasColumnOrder(3);
        config.Property(e => e.ModifiedTime).HasColumnOrder(4);
        config.Property(e => e.History).HasColumnOrder(5);

        base.OnModelCreating(modelBuilder);
    }

}

And then finally you need to add migration and then update database.

Order the Columns, while creating DB by Entity Framework 4.1. with fluent API

4 Answers4