43

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; } }
Improve this question
1

5 Answers 5

Reset to default
69

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'"); } }
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

The default Column name of uuid is ok, though I need to set/override the Primary Key Name.
I see. I believe Jeff Siver's suggestion is the way to go. If you use code first migration, you can place your T-SQL rename query there. More of details of sp_rename found here msdn.microsoft.com/en-us/library/ms188351.aspx.
thanks @cubski. This did the trick. Needed to use square braces in the rename command for it to work. execute sp_rename @objname=N'[dbo].[PK_dbo.Jobs]', @newname=N'[PK_Jobs]'--,@objtype=N'INDEX'
@darwindave Good to know, btw, is it necessary to add the "@objname","@newname", "@objtype" for it to work?
objtype is actually commented out with -- so no it's not needed. As for naming the parameters I struggled for hours to get this working it was something I tried,
|
11

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

Improve this answer

2 Comments

If I could override the Key name that is auto generated by the database this would be fine. Though the Key attribute doesn't seem to take parameters.
@darwindave - See cubski's answer below
2

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")
Improve this answer

4 Comments

I believe this is the answer though I get this error: Error 7 'System.Data.Entity.ModelConfiguration.Configuration.EntityMappingConfiguration<dahhal.Common.Job>' does not contain a definition for 'MapKey' and no extension method 'MapKey' accepting a first argument of type 'System.Data.Entity.ModelConfiguration.Configuration.EntityMappingConfiguration<ServiceM8.Common.Job>' could be found (are you missing a using directive or an assembly reference?) C:\Work\Freelancing\dahhal\Reports\dahhal.AccessDbManager\AccessDb.cs 349 71 dahhal.AccessDbManager
Sorry - that syntax was for foreign keys. I've changed the code to specifying a column name for any property.
I tried modelBuilder.Entity<Job>().HasKey(j => j.uuid).Property(j => j.uuid).HasColumnName("pk_Jobs"); Though this just renames the uuid to pk_Jobs. I need to set the key name and not the column name
Sorry, I think I misunderstood what you are asking. If I understand it now, you are looking for a way to name the primary key constraint when EF is used to generate the database. Sorry, but there is no easy way to do that. Have a look at this question, stackoverflow.com/questions/8476035/…, for one possible approach.
1

(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". } }
Improve this answer

Comments

0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.