Multiple Foreign Keys EF Core 2.1 code first generates only 1 in DB

.net-core-2.1 asp.net-core asp.net-core-2.1 ef-core-2.1 entity-framework-core

Question

I have the following model:

public class User : AuditedModel
{
    public string Login { get; }
    public string PasswordHash { get; }

    public User(string login, string password, User creationUser) : base(creationUser)
    {
        Login = login;
        PasswordHash = GeneratePasswordHash(password);
    }

    protected User(int id, DateTime creationDate, DateTime? terminationDate, string login, string passwordHash) : base(id, 0, creationDate)
    {
        Login = login;
        PasswordHash = passwordHash;
    }
}
public class AuditedModel : BaseModel
{
    protected AuditedModel(User creationUser)
    {
        CreationUser = creationUser;
        CreationDate = DateTime.UtcNow;
    }

    public User CreationUser { get; protected set; }
    public DateTime CreationDate { get; }
    public User TerminationUser { get; protected set; }
    public DateTime? TerminationDate { get; }
}
public class BaseModel
{
    public int Id { get;}

    protected BaseModel()
    {
    }

    public BaseModel(int id)
    {
        Id = id;
    }
}

EF Core Code First should create two self-referencing foreing keys for CreationUser and TerminationUser, as specified in the Fluent API:

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.ToTable(nameof(User));

        builder.HasKey(u => u.Id);
        builder.Property(u => u.Id).ValueGeneratedOnAdd();

        builder.Property(u => u.Login)
            .IsRequired();

        builder.Property(u => u.PasswordHash);

        builder.Property(u => u.CreationDate);

        builder.Property(u => u.TerminationDate);

        builder.HasOne(u => u.TerminationUser)
            .WithOne()
            .HasForeignKey<User>(u => u.Id)
            .OnDelete(DeleteBehavior.Restrict);

        builder.HasOne(u => u.CreationUser)
            .WithOne()
            .IsRequired()
            .HasForeignKey<User>(u => u.Id)
            .OnDelete(DeleteBehavior.Restrict);
     }
}

I intend to use migrations for this project, so I'm going to show the migration output, which reflects on the database:

public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.EnsureSchema(
            name: "public");

        migrationBuilder.CreateTable(
            name: "User",
            schema: "public",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),
                CreationDate = table.Column<DateTime>(nullable: false),
                TerminationUserId = table.Column<int>(nullable: true),
                TerminationDate = table.Column<DateTime>(nullable: true),
                Login = table.Column<string>(nullable: false),
                PasswordHash = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_User", x => x.Id);
                table.ForeignKey(
                    name: "FK_User_User_TerminationUserId",
                    column: x => x.TerminationUserId,
                    principalSchema: "public",
                    principalTable: "User",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Restrict);
            });

        migrationBuilder.CreateIndex(
            name: "IX_User_TerminationUserId",
            schema: "public",
            table: "User",
            column: "TerminationUserId");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "User",
            schema: "public");
    }
}

As shown, There is only the TerminationUserId ForeignKey column, even though the CreationUser navigation is set to required.

    I'm using:
    - Npgsql.EntityFrameworkCore.PostgreSQL 2.1.1
    - Microsoft.NETCore.App 2.1.0
    - Microsoft.EntityFrameworkCore.Tools.DotNet 2.0.3
    - Microsoft.EntityFrameworkCore.Design 2.1.1
    - Microsoft.VisualStudio.Web.CodeGeneration.Tools 2.0.4
1
1
7/5/2018 2:15:15 PM

Accepted Answer

I'm surprised that the TerminationUserId foreign key column was created given that it does not exist in your model. If you want both of these foreign key columns to be created then you need to add them to your model as follows:

public class AuditedModel : BaseModel
{
    protected AuditedModel(User creationUser)
    {
        CreationUser = creationUser;
        CreationDate = DateTime.UtcNow;
    }

    public int CreationUserId { get; protected set; }
    public User CreationUser { get; protected set; }
    public DateTime CreationDate { get; }
    public int? TerminationUserId { get; protected set; }
    public User TerminationUser { get; protected set; }
    public DateTime? TerminationDate { get; }
}

And then in your Fluent API mapping:

    builder.HasOne(u => u.TerminationUser)
        .WithOne()
        .HasForeignKey<User>(u => u.TerminationUserId)
        .OnDelete(DeleteBehavior.Restrict);

    builder.HasOne(u => u.CreationUser)
        .WithOne()
        .IsRequired()
        .HasForeignKey<User>(u => u.CreationUserId)
        .OnDelete(DeleteBehavior.Restrict);
2
7/5/2018 2:37:40 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow