Entity Framework Core - Foreign Key 1 (extra foreign key column)

Question

I just upgraded to Entity Framework Core 2 and now I'm getting issues with an extra column existing and having a unique key even though it's not in my model and it's not defined anywhere else.

The index:

migrationBuilder.CreateTable(
    name: "Vouchers",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
        Code = table.Column<Guid>(nullable: false),
        IsClaimed = table.Column<bool>(nullable: false),
        LastModified = table.Column<DateTime>(nullable: false),
        NumberOfUnits = table.Column<int>(nullable: false),
        TransactionId = table.Column<int>(nullable: false),
        TransactionId1 = table.Column<int>(nullable: true) // why is this here?
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Vouchers", x => x.Id);
        table.ForeignKey(
            name: "FK_Vouchers_Transactions_TransactionId1",
            column: x => x.TransactionId1,
            principalTable: "Transactions",
            principalColumn: "Id",
            onDelete: ReferentialAction.Restrict);
    });

TransactionId1 isn't in the model:

public class Voucher : IAutoLastModified
{
    public int Id { get; set; }
    public DateTime LastModified { get; set; }

    public int TransactionId { get; set; }
    public Transaction Transaction { get; set; }

    public int NumberOfUnits { get; set; }
    public Guid Code { get; set; }
    public bool IsClaimed { get; set; }
}

Am I defining the Foreign Key wrongly?

modelBuilder.Entity<Voucher>().HasOne(x => x.Transaction).WithOne(x => x.Voucher).IsRequired(false);

My app fails because TransactionId1 is always going to be null and has a unique constraint that I can't remove.

Why did EF create an extra column for this table?

Gertrudis

Answer 1

Okay so I figured out what the issue was (keeping the question here for anyone who makes the same mistake I did).

I marked the relationship as Optional but the column was an int instead of int? so EF decided to add it's own column behind the scenes.

After fixing this I had to recreate the database - migration did not complete successfully due to existing data.

Gertrudis

Answer 2

This might also happen if you define your models two way binded but forget to use it in fluent:

public class Customer
{
    public Guid Id { get; set; }
    public List<Order> Orders {get; set;}
}

public class Order
{
    public Guid Id { get; set; }
    
    public Guid CustomerId { get; set; }
    public Guid Customer { get; set; }
}

// AppDbContext
builder.Entity<Order>()
     .HasOne(x => x.Customer) // <-- EDIT: Also use it like this, not .HasOne<Customer>()
     .WithMany() //WRONG -> should be .WithMany(x => x.Orders) OR modify the model to not define the collection at the customer entity
     .HasForeignKey(x => x.CustomerId)
     .OnDelete(DeleteBehavior.SetNull)
;

Trivial

Thanks! I spent almost a day, and would have spent even more, this was helpful to me. – Yvoneyvonne

Answer 3

I had this problem when the type of foreign key field in the child was not matching with the type of primary key field in the parent.

Example:

public class User // Parent
{
    public long Id { get; set; }
    public virtual ICollection<UserLike> UserLikes { get; set; } // Child collection
}

public class UserLike // Child
{
    public int Id { get; set; }
    public int UserId { get; set; } // Foreign key
    public virtual User User { get; set; } // Navigation property
}

In the DbContext, I had:

modelBuilder.Entity<UserLike>(entity =>
{
    entity.HasOne(x => x.User)
    .WithMany(x => x.UserLikes)
    .OnDelete(DeleteBehavior.ClientCascade);
}

Here, int UserId does not match with the type of primary key long Id in the parent. The solution is to make the UserId long:

public class UserLike // Child
{
    public int Id { get; set; }
    public long UserId { get; set; } // Foreign key
    public virtual User User { get; set; } // Navigation property
}

Oilla

Answer 4

You need to tell the model builder which column in your voucher table you want to use as the foreign key column. Otherwise Entity Framework will just create one for you.

To do this, add a HasForeignKey method to the fluent setup:

modelBuilder.Entity<Voucher>().HasOne(x => x.Transaction).WithOne(x => x.Voucher).HasForeignKey<Voucher>(x => x.TransactionId).IsRequired(false);

Note when setting up a one-to-one relationship, you need to define the entity the foreign key exists in as a generic constraint.

Sybil

Answer 5

I have faced this issue in the past using Data first approach. I had to delete an existing column but after every update that column appears in the edmx schema structure which I had to manually delete to make it work. can you recreate the edmx instead of update.

Parr

Answer 6

I had the same issue and the fix for me was to add the DataAnnotation tag of [ForeignKey("")] above the navigation property to explicitly state which one to use.

public Guid TransactionId { get; set; }

[ForeignKey("TransactionId ")]
public Transaction Transaction { get; set; }

Thanks

Compliment