Issue with foreign key deployment to SQL server - Code First - Empty foreign key

Question

I have rest API in .NET core and EF Core 3.0, I am getting one weird issue. Below is my entities.

[Table(name: "BizObjects")]
public class BizObject
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Required]
    [StringLength(06)]
    public int Id { get; set; }
    public string Category { get; set; } //FK

    [MaxLength(2)]
    public int ItemIncrement { get; set; }

    [MaxLength(2)]
    public int SubItemIncrement { get; set; }
    public virtual BizObjectT BizObjectT { get; set; }
}

[Table(name: "BizObjectsT")]
public class BizObjectT
{
    [StringLength(02)]
    public string Lang { get; set; }
    public string Text { get; set; }

    #region Primary Key derived from Foreign key for Config tabels                
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    [ForeignKey(name: "BizObjectId")]
    [Required]
    public int BizObjectId { get; set; }
    public virtual BizObject BizObject { get; set; }       
    #endregion
}

Here is my DbContext.

public class ApplicationContext : DbContext, IDisposable
{
    public ApplicationContext(DbContextOptions options)
        : base(options: options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BizObject>()
            .HasOne<BizObjectT>(s => s.BizObjectT)
            .WithOne(g => g.BizObject)
            .HasForeignKey<BizObjectT>(s => s.BizObjectId);
    }

    public DbSet<BizObject> BizObjects { get; set; }
    public DbSet<BizObjectT> BizObjectsT { get; set; }
}

Now, this all works fine. I am able to create migration and I can deploy it to database. my foreign keys works properly in the API.

But the issue that I get is with the SQL server. Here I am not able to find the foreign keys primary table at in relationship window of implementation. even if try to add FK manually, I don't see that table Status.

See below snap.

I am not sure where the issue is, any suggestion would be a great help.

Thanks.

Update 1 :

Here is the entity generated migration.

public partial class initial : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "BizObjects",
            columns: table => new
            {
                Id = table.Column<int>(maxLength: 6, nullable: false),
                Category = table.Column<string>(nullable: true),
                ItemIncrement = table.Column<int>(maxLength: 2, nullable: false),
                SubItemIncrement = table.Column<int>(maxLength: 2, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_BizObjects", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "BizObjectsT",
            columns: table => new
            {
                BizObjectId = table.Column<int>(nullable: false),
                Lang = table.Column<string>(maxLength: 2, nullable: true),
                Text = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_BizObjectsT", x => x.BizObjectId);
                table.ForeignKey(
                    name: "FK_BizObjectsT_BizObjects_BizObjectId",
                    column: x => x.BizObjectId,
                    principalTable: "BizObjects",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "BizObjectsT");

        migrationBuilder.DropTable(
            name: "BizObjects");
    }
}

The weird thing is if I change database name it will work. But for only first few tables.

let's say I have 10 tables in migration and this BizObject is first table, then after changing database name, SQL will have reference of BizObject but not of other tables and this is random.

For reference :

• VS 2019 version 16.3.0

• SSMS Version 14.0

• SQL server 2014 version 12.0

Rest API Config :

• .NET CORE 3.0

• EF Core 3.0

Today I will check all this with new version of SQL server and SSMS and will post updates here later...

Update 2 :

Issues seems to be at SQL server or SSMS because I have removed few tables and run the migration, the foreign key didn't work but in the drop down of relational tables, I can see table names which I haven't added in migration.

Answer 1

Isn't your FK annotation off? Instead of

[Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
[ForeignKey(name: "BizObjectId")]
[Required]
public int BizObjectId { get; set; }
public virtual BizObject BizObject { get; set; } 

Sholdn't it be like this:

[Required]
public int BizObjectId { get; set; }
[ForeignKey(name: "BizObjectId")]
public virtual BizObject BizObject { get; set; }

See: EF Core Doc

I also removed the [Key] attribute since I am pretty sure it cannot be a key and a foreign key at the same time - You can add a unique index if you like. or if it is indeed used as PK (1:1) you should remove the [DatabaseGenerated] attribute since in the child record it cannot be generated by DB.

Answer 2

Finally, I think I got the root cause.

It's none other then SSMS. I have updated my SQL server and SSMS to latest versions and it's started working fine. will post more updates later on...