How to Specify Primary Key Name in EF-Code-First

Question

I'm using Entity Framework Codefirst to create my Database. The default Primary key with the schema name dbo.pk_Jobs seems to upset access 2007 when I connect to it over ODBC. If I manually edit the name and remove the schema name and rename this Primary Key to pk_jobs, Access can now read the table.

Can I specify the Primary Key name to not include the name of the schema using Fluent Api, Data Attributes or any other method.

public class ReportsContext : DbContext
{
    public DbSet<Job> Jobs { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Job>().ToTable("Jobs");
        modelBuilder.Entity<Job>().HasKey(j => j.uuid);

        base.OnModelCreating(modelBuilder);
    }
}
public class Job
{
    public Guid uuid{ get; set; }
    public int active{ get; set; }
}

Answer 1

If you want to specify the column name and override the property name, you can try the following:

Using Annotations

public class Job
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("CustomIdName")]
    public Guid uuid { get; set; }
    public int active { get; set; }
}

Using Code First

    protected override void OnModelCreating(DbModelBuilder mb)
    {
        base.OnModelCreating(mb);

        mb.Entity<Job>()
            .HasKey(i => i.uuid);
        mb.Entity<Job>()
          .Property(i => i.uuid)
          .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
          .HasColumnName("CustomIdName");
    }

Inside Migration Configuration

public partial class ChangePrimaryKey : DbMigration
{
    public override void Up()
    {
        Sql(@"exec sp_rename 'SchemaName.TableName.IndexName', 'New_IndexName', 'INDEX'");
    }

    public override void Down()
    {
        Sql(@"exec sp_rename 'SchemaName.TableName.New_IndexName', 'Old_IndexName', 'INDEX'");
    }
}

Answer 2

You can use the Key attribute to specify the parts of the primary key. So your Job class might be

public class Job
{
    [Key]
    public Guid uuid{ get; set; }
    public int active{ get; set; }
}

The data annotation attributes are defined in the System.ComponentModel.DataAnnotations namespace

Answer 3

If I understand, you are asking how to change the name of the primary key column used by Entity Framework. The following addition to your HasKey statement should take care of this:

modelBuilder.Entity<Job>().Property(j => j.uuid).HasColumnName("pk_Jobs")

Answer 4

(This is a complement to the answer/comments by @Jeff Sivers and @cubski.)

As far as I know you can't specify the PK name with Data Attribute. Sometimes I need to get rid of the dbo. part of the name and I then use a manually edited code first migration to change the name, like this:

public partial class ChangeNameOnPKInCustomers : DbMigration
{
    private static string fromName = "PK_dbo.Customers";    // Name to change

    private static string toName = fromName.Replace("dbo.", "");

    public override void Up()
    {
        Sql($"exec sp_rename @objname=N'[dbo].[{fromName}]', @newname=N'{toName}'");
        // Now the PK name is "PK_Customers".
    }

    public override void Down()
    {
        Sql($"exec sp_rename @objname=N'[dbo].[{toName}]', @newname=N'{fromName}'");
        // Back to "PK_dbo.Customers".
    }
}

Answer 5

When you add an explicit migration using Code First, you get a .cs file with the name of the migration which is a partial class with a base class DbMigration.

For your Primary Key Constraint, you have either DropPrimaryKey or AddPrimaryKey function depending on what you are trying to do. My problem was with DropPrimaryKey as my Db had different name for the Primary Key.

Both these functions have overloads to take the name of the PK so that you could explicitly specify the name of the PK. Worked fine for me for DropPrimaryKey with explicit PK name. The argument being object anonymousArguments which is by default null.