EF Core Composite Key, one foreign, one auto incrementing

Question

I have this entity:

public class PlayerScoreHistory
{
    public int Id { get; set; }

    public int PlayerScoreId { get; set; }

    public DateTime ScoreWhen { get; set; }
}

I need to make a composite key out of both of those Id fields. PlayerScoreId needs to be a foreign key to PlayerScore.Id . Id needs to be an auto incrementing id.

So, I got as far as:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<PlayerScoreHistory>()
        .HasKey(x => new { x.Id, x.PlayerScoreId });
}

This gives me the composite key. I did Add-Migration Initial to give me my initial migration.

To get the Foreign Key, I simply added the line in the constraints parameter:

 migrationBuilder.CreateTable(
    name: "PlayerScoreHistories",
    columns: table => new
        {
            Id = table.Column<int>(nullable: false),
            PlayerScoreId = table.Column<int>(nullable: false),
            ScoreWhen = table.Column<DateTime>(nullable: false)
        },
    constraints: table =>
        {
            table.PrimaryKey("PK_PlayerScoreHistories", x => new { x.Id, x.PlayerScoreId });
            table.ForeignKey("FK_PlayerScoreId", arg => new {  arg.PlayerScoreId}, "PlayerScores", "Id");
        });

So two questions:

1. How can I get the foreign key creation in the OnModelCreating method?
2. How can I make the Id column a Database Generated field and make sure EF Core doesn't try and set a value?

I'm not exactly sure what options are open to me, seeing as EF Core is extremely new...

The two errors I get:

1.

When I add this line to the Id column parameter configuration:

Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)

I get

SQL Cannot insert explicit value for identity column in table 'Table' when IDENTITY_INSERT is set to OFF [duplicate]

2.

When I remove the line and then try and add an entity, I get:

Can't insert null into column Id

---

Clearly I'm in between somewhere....

---

Edit So Far

I decided to remove the Id column and just use PlayerScoreId and ScoreWhen as the composite keys....

But, one question I still have is how to make OnModelCreating identity PlayerScoreId as a foreign key - without having navigation properties.....

Answer 1

First of all, editing migration files give you desired database as you want but EF runtime will not be aware about those changes. All the information which runtime requires should be configured in the model only.

Therefore you get exception

SQL Cannot insert explicit value for identity column in table 'Table' when IDENTITY_INSERT is set to OFF [duplicate]

Because EF runtime does not know about identity column so it will send 0 (default(int)) as the value for identity column in the insert statement.

I am not able to get second exception since, EF will not (& cannot) try to insert null for int type property because value could not be null in c# world.

Now answers to your questions:

1. Answer of Ivan is correct. That is the way to configure foreign key in modelbuilder.

   modelBuilder.Entity<PlayerScoreHistory>()
       .HasOne<PlayerScore>()
       .WithMany()
       .HasForeignKey(e => e.PlayerScoreId);
   

For more info on how to configure relationships - https://ef.readthedocs.io/en/latest/modeling/relationships.html

2. To tell that Id is database generated field, use following code in modelbuilder.

   modelBuilder
       .Entity<PlayerScoreHistory>()
       .Property(e => e.Id)
       .ValueGeneratedOnAdd();
   

This will tell EF that values for Id are generated while inserting entity, which for SqlServer translate to Identity if there is only 1 integer column configured for ValueGeneratedOnAdd. More info on generated properties is at https://ef.readthedocs.io/en/latest/modeling/generated-properties.html#value-generated-on-add

Answer 2

But, one question I still have is how to make OnModelCreating identity PlayerScoreId as a foreign key - without having navigation properties.....

You can use the HasOne / WithMany (or HasMany / WithOne) methods w/o specifying the navigation property, combined with HasForeignKey as usual:

modelBuilder.Entity<PlayerScoreHistory>()
    .HasOne<PlayerScore>()
    .WithMany()
    .HasForeignKey(e => e.PlayerScoreId);