8

I'm using EntityFramework 6.1.3 with CodeFirst for an Asp.Net application. I have an existing table ("Users") on which I'm trying to add a foreign key "GroupId". This is the class (Everything with //new are changes made after the last migration)

[Table("Users")] public class User { [Key] [Column("PK_USER")] public int Id { get; set; } [Column("Username")] public string Username { get; set; } [Column("Name")] public string Name { get; set; } [Column("Password")] public string Password { get; set; } [Column("Firstname")] public string Firstname { get; set; } [Column("Lastname")] public string Lastname { get; set; } [Column("LastLogin")] public DateTime? LastLogin { get; set; } [Column("Department")] public string Department { get; set; } [Column("EMail")] public string EMail { get; set; } [Column("IsWindowsUser")] public bool? IsWindowsUser { get; set; } [Column("Signature")] public string Signature { get; set; } [Column("FK_ROLE")] public int? RoleId { get; set; } [ForeignKey("RoleId")] public virtual Role Role { get; set; } // new [Column("GroupId")] public int? GroupId { get; set; } //new [ForeignKey("GroupId")] public virtual Group Group { get; set; } //new public virtual ICollection<GroupResponsibility> Responsibilites { get; set; } public virtual ICollection<UserField> UserFields { get; set; } public override string ToString() { return Username; } }

After I run add-migration the following code is generated (omitted other changes)

 AddColumn("dbo.Users", "GroupId", c => c.Int()); AddColumn("dbo.Users", "Group_Id", c => c.Int()); CreateIndex("dbo.Users", "GroupId"); CreateIndex("dbo.Users", "Group_Id"); AddForeignKey("dbo.Users", "Group_Id", "dbo.Groups", "Id"); AddForeignKey("dbo.Users", "GroupId", "dbo.Groups", "Id");

As you can see EntityFramework recognized the foreign key but still added a default one "Group_Id". If I go through with it, and update the database, the navigational property "Group" will releate to "Group_Id" instead of the desired "GroupId".

Any ideas what might cause this?

Update 1

I commented out the navigational property and got the same result. Looks like the ICollection Users on the other end of the relation is the culprit. Here is the class "Group"

[Table("Groups")] public class Group { [Key] public int Id { get; set; } public string Name { get; set; } public int? GroupLeaderId { get; set; } [ForeignKey("GroupLeaderId")] public virtual User GroupLeader { get; set; } public virtual ICollection<User> Users { get; set; } public virtual ICollection<GroupResponsibility> Responsibilites { get; set; } public virtual ICollection<ApplicationForm> Applications { get; set; } public override string ToString() { return Name; } }

If I comment out the ICollection Users, I get the following exception when adding the migration:

Unable to determine the principal end of an association between the types 'SparePartsDb.Entities.Group' and 'SparePartsDb.Entities.User'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

Update 2

After some googling and changing the Key Attribute on the Group class...

 [Key, ForeignKey("GroupLeader")] public int Id { get; set; }

...I'm able to comment out the ICollection and run the migration. However, GroupId is not not longer recognized as a foreign key even though the navigiational property is present in the class User.

AddColumn("dbo.Users", "GroupId", c => c.Int());
Improve this question
2
  • That looks correct. The superfluous column definitions shouldn't affect it (you don't need column annotations if they match the property name). I would try commenting out the nav property and see what get's generated. Commented Sep 5, 2016 at 16:49
  • @SteveGreene: I commented out the navigational property and got the same result. Looks like the ICollection Users on the other end of the relation is the culprit. I updated my post. Commented Sep 6, 2016 at 7:17

2 Answers 2

Reset to default
13

OK, so I've searched for "migration adding duplicate foreign key" in multiple permutations for hours, but I finally figured out what was causing it for me: don't assume Fluent API knows which field you're referring to with WithOne() or WithMany()-- specify the field. See below for details.

I had two entity models in a one-to-many relationship, Address and Invitation, with public virtual ICollection<Invitation> Invitations on Address and public virtual Address Address on Invitation. I chose Fluent API over convention/attributes, so my Invitation builder looked like this:

builder .HasOne(x => x.Address) .WithMany() .HasForeignKey(x => x.AddressId);

Unfortunately, EF Core 2.2 doesn't like having that empty WithMany() in there. Running dotnet ef migrations add InitialCreate resulted in this nonsense, much like OP:

migrationBuilder.CreateTable( name: "Invitation", columns: table => new { AddressId = table.Column<Guid>(nullable: false), AddressId1 = table.Column<Guid>(nullable: true), ... }, constraints: table => { table.PrimaryKey("PK_Invitation", x => x.Id); table.ForeignKey( name: "FK_Invitation_Address_AddressId", column: x => x.AddressId, principalTable: "Address", principalColumn: "Id", onDelete: ReferentialAction.Cascade); table.ForeignKey( name: "FK_Invitation_Address_AddressId1", column: x => x.AddressId1, principalTable: "Address", principalColumn: "Id", onDelete: ReferentialAction.Restrict); });

Switching my builder to read:

builder .HasOne(x => x.Address) .WithMany(x => x.Invitations) .HasForeignKey(x => x.AddressId);

fixed the problem for me.

Running dotnet ef migrations remove followed by dotnet ef migrations add InitialCreate again gave me a much nicer migration:

migrationBuilder.CreateTable( name: "Invitation", columns: table => new { AddressId = table.Column<Guid>(nullable: false), ... }, constraints: table => { table.PrimaryKey("PK_Invitation", x => x.Id); table.ForeignKey( name: "FK_Invitation_Address_AddressId", column: x => x.AddressId, principalTable: "Address", principalColumn: "Id", onDelete: ReferentialAction.Cascade); });

Hope this helps some other poor soul searching this down.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

yes ffs ms plz fix this, ps thank you! im having this issue with EF 3.1 (2020-03-27)
this how ever does not fix it for EF 6.4 but does for Core
0

You shouldn't need to put a 

[Column("GroupId")]

on top of the

public int? GroupId { get; set; }

Entity Framework should be able to recognize your mapping by itself.

As stated in msdn:

When generating the database, code first sees the BlogId property in the Post class and recognizes it, by the convention that it matches a class name plus “Id”, as a foreign key to the Blog class. But there is no BlogId property in the blog class. The solution for this is to create a navigation property in the Post and use the Foreign DataAnnotation to help code first understand how to build the relationship between the two classes —using the Post.BlogId property — as well as how to specify constraints in the database.

And the code to this explanation would be:

public class Post { public int Id { get; set; } public string Title { get; set; } public DateTime DateCreated { get; set; } public string Content { get; set; } public int BlogId { get; set; } [ForeignKey("BlogId")] public Blog Blog { get; set; } public ICollection<Comment> Comments { get; set; } }

As you can see, only the complex object has the mapping defining the fk, EF should do the rest for you.

source

Improve this answer

1 Comment

Thanks for the detailed explanation. However the redundant column attribute doesn't seem to make a difference.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.