Entity Framework not using my foreign key but generating a new column instead

Question

I am using the Fluent API and a code-first approach for defining the schema. I wonder why the migration is generating a new column instead of using the defined foreign key. Thus, the DbSet is not linking correctly.

I am following this example: https://learn.microsoft.com/en-us/ef/core/modeling/relationships

The relationship looks like this: server-players (parent-child)

Player.cs

public class Player
{
    public int Id { get; set; }
    public int PlayerId { get; set; }
    public string Name { get; set; }

    public int ServerId { get; set; }
    public Universe Server { get; set; }
}

Universe.cs (Server)

public class Universe
{
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Domain { get; set; }

        public List<Planet> Players { get; set; } = new List<Player>();
}

PlayerConfiguration.cs (Fluent API)

public class PlayerConfiguration : IEntityTypeConfiguration<Player>
{
    public void Configure(EntityTypeBuilder<Player> builder)
    {
        builder.ToTable("Player")
            .HasAlternateKey(e => new { e.PlayerId, e.ServerId });
        builder.HasOne<Universe>()
            .WithMany()
            .HasForeignKey(e => e.ServerId);
    }
}

The database script generated by EF:

CREATE TABLE [dbo].[Player] 
(
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (MAX) NULL,
    [ServerId]   INT            NOT NULL,
    [ServerId1]  INT            NULL,

    CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED ([Id] ASC),

    CONSTRAINT [AK_Player_PlayerId_ServerId] 
        UNIQUE NONCLUSTERED ([PlayerId] ASC, [ServerId] ASC),
    CONSTRAINT [FK_Player_Universe_ServerId1] 
        FOREIGN KEY ([ServerId1]) REFERENCES [dbo].[Universe] ([Id])
);

GO

CREATE NONCLUSTERED INDEX [IX_Player_ServerId]
ON [dbo].[Player]([ServerId] ASC);
GO

CREATE NONCLUSTERED INDEX [IX_Player_ServerId1]
ON [dbo].[Player]([ServerId1] ASC);

So when I call context.Universes.Players.Add(...some players...) and context.SaveChanges(). The ServerIds will save to ServerId1 and keeping default in ServerId column. When I try to query the data from the database, no Players will be found.

I realized that the AK is using the ServerID as junction while the FK is using ServerId1 when I am typing the question. However, I will need to setup a composite unique key that identifies each player has only one ID in the server. The Id column is a system generated one. While PlayerId is captured from different servers. The PlayerId can be duplicated across server.

I design the system to generate a new Id too as it is linked to the system frequently for other purposes. It will waste time to link a composite key every time it is called.

I got the sample of defining a unique key from THIS ANSWER suggesting that I can use Alternate Key. Is this actually not true? Or I actually am not defining the FK correctly?

---

Updated 12/01/2018
PlayerId is in Player class instead of Universe class. That was just mistake while copying.

.WithMany(u => u.Players) generates the same result as .WithMany() as suggested in the comment. Migration file has blank Up/Down too.

Answer 1

Thanks for all the comments and Answers contributed by the community.
The answer to the final goal is actually way more low level then the question asked.

The Players List did not pull up because of Lazy Loading.
That is solved by adding context.Universes.Include(u => u.Players) to the loading script.

---

However, it is interesting finding about the multiple FK being generated too. I will cover this below in depth.

Update 1 - 14/01/2018
The correct way to call the Fluent API is

builder.HasOne(e => e.Server).WithMany(u => u.Players).HasForeignKey(e => e.ServerId)

As Ivan suggested in the comment that

.HasOne<Universe>() is telling EF that the relationship does not have reference navigation property in Player class, hence when it discovers Player.Server navigation property, it creates another one-to-many relationship.

---

I wonder it is a bug with the Fluent API. The Data Annotations method suggested works perfectly by adding

//To Universe Class
[InverseProperty("Server")]
public List<Player> Players { get; set; } = new List<Player>();

//To Player Class
public int ServerId { get;set; }
[ForeignKey("ServerId")]
public Universe Server { get; set; }

It turns out that if I use the Fluent API

builder.HasOne<Universe>()
    .WithMany(u => u.Players)
    .HasForeignKey(e => e.ServerId);

It will generate the Relationship twice. I wonder if InverseProperty is required in Fluent API as the Official Documentation did not say so.

I have also tried the method suggested by @rufus-lobo saying that the navigation name should be kept by EF. But it is still generating the FK column twice when using Fluent API.

It is observed that ServerId is nullable and ServerId1 is required. My guess is that:

1. Universe has List<Player>. But it is not specified in the Fluent API that Player must be attached to a server. The first ServerID is created to maintain the relationship.
2. When EF looks into Player later. The relationship of Player to Universe comes in. The second ServerId1 is then used to keep the 1-to-1 relationship.

Answer 2

EF Core assumes foreign keys to have the format <Classname>Id.
Try the following:

public class Player
{
    public int Id { get; set; } 
    public string Name { get; set; }

    public int UniverseId { get; set; }
    public Universe Universe { get; set; }
}

public class Universe
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public int PlayerId { get; set; } // I think you can do without this.
    public string Name { get; set; }
    public string Domain { get; set; }

    public List<Planet> Planets { get; set; } = new List<Planet>();
}