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

I want have an order of my database table columns after 开发者_Go百科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

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/

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.

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.

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.