CODEWITHSUNDEEP
entity-framework-core
RazorWing
RazorWing

Reputation: 35

One-to-One relationship between multiple columns in one table to another

I am trying to create a one-to-one foreign-key relationship between the below columns from Room to RoomBase.

public class Room
{
    public int RoomRootId { get; set; }
    
    public int RoomId { get; set; }
    public RoomBase RoomObj { get; set; }

    public int StageId { get; set; }
    public RoomBase StageObj { get; set; }      

    public int ParentLobbyId { get; set; }
    public RoomBase ParentLobbyObj { get; set; }        
}

public class RoomBase
{
    public int RoomId { get; set; }
    public string Name { get; set; }
}

I have attempted to do this by using the following code in OnModelCreating.

modelBuilder.Entity<Room>(u =>
{
    u.ToTable("Rooms");
    u.Property(e => e.RoomId).HasColumnName("RoomId");
    u.HasOne<RoomBase>().WithOne().HasForeignKey<Room>(e => e.RoomId);
});

modelBuilder.Entity<Room>(u =>
{
    u.ToTable("Rooms");
    u.Property(e => e.StageId).HasColumnName("StageId");
    u.HasOne<RoomBase>().WithOne().HasForeignKey<Room>(e => e.StageId);
});

modelBuilder.Entity<Room>(u =>
{
    u.ToTable("Rooms");
    u.Property(e => e.ParentLobbyId).HasColumnName("ParentLobbyId");
    u.HasOne<RoomBase>().WithOne().HasForeignKey<Room>(e => e.ParentLobbyId);
});

This results in the following migration script:

migrationBuilder.CreateTable(
    name: "Rooms",
    columns: table => new
    {
        RoomRootId = table.Column<int>(type: "INTEGER", nullable: false)
            .Annotation("Sqlite:Autoincrement", true),
        RoomId = table.Column<int>(type: "INTEGER", nullable: false),
        StageId = table.Column<int>(type: "INTEGER", nullable: false),
        ParentLobbyId = table.Column<int>(type: "INTEGER", nullable: false),
        ParentLobbyObjRoomId = table.Column<int>(type: "INTEGER", nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Rooms", x => x.RoomRootId);
    });
migrationBuilder.CreateTable(
    name: "RoomBases",
    columns: table => new
    {
        RoomId = table.Column<int>(type: "INTEGER", nullable: false)
            .Annotation("Sqlite:Autoincrement", true),
        HostName = table.Column<string>(type: "TEXT", nullable: true),
        RoomRootId = table.Column<int>(type: "INTEGER", nullable: true),
        RoomRootId1 = table.Column<int>(type: "INTEGER", nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_RoomBases", x => x.RoomId);
        table.ForeignKey(
            name: "FK_RoomBases_Rooms_RoomRootId",
            column: x => x.RoomRootId,
            principalTable: "Rooms",
            principalColumn: "RoomRootId",
            onDelete: ReferentialAction.Restrict);
        table.ForeignKey(
            name: "FK_RoomBases_Rooms_RoomRootId1",
            column: x => x.RoomRootId1,
            principalTable: "Rooms",
            principalColumn: "RoomRootId",
            onDelete: ReferentialAction.Restrict);
    });

Question 1: I don't think it should be adding "FK_Room_RoomBases_ParentLobbyObjRoomId" but why dont we seem similar for the RoomObj and StageObj?

Question 2: I believe I am missing the navigation propery as when I perform a linq query using the "Include", the obj properties of Room is not populated. How do I implement this for RoomObj, StageObj and ParentLobbyObj?

Question 3: Why are additional columns being added to "RoomBases"? Maybe an incorrect FK config? I expected something like this under the CreateTable for Room, not RoomBase:

table.ForeignKey(
    name: "FK_Rooms_RoomBases_RoomId",
    column: x => x.RoomId,
    principalTable: "RoomBases",
    principalColumn: "RoomId";
table.ForeignKey(
    name: "FK_Rooms_RoomBases_StageId",
    column: x => x.StageId,
    principalTable: "RoomBases",
    principalColumn: "RoomId";
table.ForeignKey(
    name: "FK_Rooms_RoomBases_ParentLobbyId",
    column: x => x.ParentLobbyId,
    principalTable: "RoomBases",
    principalColumn: "RoomId";

I have checked numerous sites such as the below but I have not found an appropriate answer.

Upvotes: 1

Views: 955

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205799

The answer of all your questions is: because of incorrect relationships configuration, and more specifically, not mapping the Room navigation properties (the three u.HasOne<RoomBase>() calls).

By doing so, these navigation properties are left out from the fluently configured relationships, and EF Core will map them conventionally to a separate relationships with the corresponding conventional FKs.

The correct configuration is to specify navigation properties for each relationship

u.HasOne(e => e.RoomObj) // <--
    .WithOne().HasForeignKey<Room>(e => e.RoomId);

u.HasOne(e => e.StageObj) // <--
    .WithOne().HasForeignKey<Room>(e => e.StageId);

u.HasOne(e => e.ParentLobbyObj) // <--
    .WithOne().HasForeignKey<Room>(e => e.ParentLobbyId);

Upvotes: 1

Related Questions