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?
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.
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());
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With