c# - Double id field being generated in EF core code first when adding foreign key

Question

I'm working on an application that keeps track of our client's cars. I'm using EF core code first. This is where I have a problem with two tables, vehicle and licensePlate.

A vehicle can have multiple licensePlate (but only one active at a time), and a licensePlate can belong to one vehicle. Everything has worked fine so far, but for a new feature I want to implement the history of a car's license plates. For this I have added an activeLicensePlate field to the vehicle class, which should contain a foreign key to the licensePlate (id field) table.

The code I have:

Class Vehicle (before adding the new code):

namespace Models
{
    public class Vehicle : IIdentifiable
    {
        public virtual long Id { get; set; }
        public virtual long ChassisNr { get; set; }
        public virtual ICollection<LicensePlate> LicensePlates { get; set; }
        public virtual FuelTypes FuelType { get; set; }
        public virtual VehicleTypes VehicleType { get; set; }
        public virtual int Mileage { get; set; }
        public virtual ICollection<Maintenance> Maintenances { get; set; }
        public virtual ICollection<Application> Applications { get; set; }
        
    }
}

Class Vehicle (after trying to add a field to keep track of the active license plate):

namespace Models
{
    public class Vehicle : IIdentifiable
    {
        public virtual long Id { get; set; }
        public virtual long ChassisNr { get; set; }
        public virtual ICollection<LicensePlate> LicensePlates { get; set; }
        public virtual FuelTypes FuelType { get; set; }
        public virtual VehicleTypes VehicleType { get; set; }
        public virtual int Mileage { get; set; }
        public virtual ICollection<Maintenance> Maintenances { get; set; }
        public virtual ICollection<Application> Applications { get; set; }
        
        //not working, migration creates 2 columns
        public virtual LicensePlate ActiveLicensePlate { get; set; }
        public virtual long ActiveLicensePlateId { get; set; }
    }
}

Class LicensePlate:

namespace Models
{
    public class LicensePlate : IIdentifiable
    {
        public virtual long Id { get; set; }
        public virtual string LicensePlateCharacters { get; set; }

        //rel vehicle
        public virtual long VehicleId { get; set; }
        public virtual Vehicle Vehicle { get; set; }
    }
}

Model constraints:

 //rel vehicle license plate one many
modelBuilder.Entity<LicensePlate>()
.HasOne<Vehicle>(l => l.Vehicle)
.WithMany(v => v.LicensePlates)
.HasForeignKey(l => l.VehicleId)
.IsRequired();

Vehicle model constraints:

using Microsoft.EntityFrameworkCore;
using Models;

namespace ReadRepositories.Mappings
{
    public static class VehicleModelConstraints
    {
        public static void OnModelCreatingVehicle(this ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Vehicle>()
                .HasKey(v => v.Id);

            modelBuilder.Entity<Vehicle>()
                .Property(v => v.Id)
                .ValueGeneratedOnAdd();

            modelBuilder.Entity<Vehicle>()
                .Property(v => v.FuelType)
                .IsRequired();

            modelBuilder.Entity<Vehicle>()
                .Property(v => v.VehicleType)
                .IsRequired();

            modelBuilder.Entity<Vehicle>()
                .Property(v => v.Mileage)
                .IsRequired();

            modelBuilder.Entity<Vehicle>()
               .Property(v => v.ChassisNr)
               .IsRequired();

            modelBuilder.Entity<Vehicle>()
                .Property(v => v.ActiveLicensePlateId)
                .IsRequired();
        }
    }
}

License plate model constraints:

using Microsoft.EntityFrameworkCore;
using Models;

namespace ReadRepositories.Mappings
{
    public static class LicensePlateModelConstraints
    {
        public static void OnModelCreatingLicensePlate(this ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<LicensePlate>()
               .HasKey(l => l.Id);

            modelBuilder.Entity<LicensePlate>()
                .Property(l => l.Id)
                .ValueGeneratedOnAdd();

            modelBuilder.Entity<LicensePlate>()
                .Property(l => l.LicensePlateCharacters)
                .IsRequired();
        }
    }
}

Migration generated:

using Microsoft.EntityFrameworkCore.Migrations;

namespace Repositories.Migrations
{
    public partial class ActiveLicensePlate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<long>(
                name: "ActiveLicensePlateId",
                table: "Vehicle",
                type: "bigint",
                nullable: false,
                defaultValue: 0L);

            migrationBuilder.AddColumn<long>(
                name: "ActiveLicensePlateId1",
                table: "Vehicle",
                type: "bigint",
                nullable: true);

            migrationBuilder.CreateIndex(
                name: "IX_Vehicle_ActiveLicensePlateId1",
                table: "Vehicle",
                column: "ActiveLicensePlateId1");

            migrationBuilder.AddForeignKey(
                name: "FK_Vehicle_LicensePlate_ActiveLicensePlateId1",
                table: "Vehicle",
                column: "ActiveLicensePlateId1",
                principalTable: "LicensePlate",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(
                name: "FK_Vehicle_LicensePlate_ActiveLicensePlateId1",
                table: "Vehicle");

            migrationBuilder.DropIndex(
                name: "IX_Vehicle_ActiveLicensePlateId1",
                table: "Vehicle");

            migrationBuilder.DropColumn(
                name: "ActiveLicensePlateId",
                table: "Vehicle");

            migrationBuilder.DropColumn(
                name: "ActiveLicensePlateId1",
                table: "Vehicle");
        }
    }
}

As you can see in the migration generated, a field named ActiveLicensePlateId is created, but as well is a field named ActiveLicensePlateId1 (which contains the foreign key that I want to be added).

My database tables before the migration:

vehicle table:

license plate table:

So, briefly put, the 'only' thing I want is for a field to be added in the vehicle table, a field named activeLicensePlate. This field should reference a license plates' id, with a foreign key to the license plate table.

Does anyone have an idea what is going wrong?

question from:https://stackoverflow.com/questions/66061517/double-id-field-being-generated-in-ef-core-code-first-when-adding-foreign-key

Answer 1

I am not sure, but I think your explicit property definition is making ef core ignoring your foreign key since you didn't mark it, so maybe just removing this should work since you already follow naming conventions

           //remove this 
            modelBuilder.Entity<Vehicle>()
                .Property(v => v.ActiveLicensePlateId)
                .IsRequired();

or a better way to do it is using fluent api to define the relation

    modelBuilder.Entity<Vehicle>()
        .HasOne(p => p.ActiveLicensePlate)
        .WithOne()
        .HasForeignKey(p => p.ActiveLicensePlateId);