EF Core creating "Id1" shadow property in migration

Question

I have an abstract class called Device. It is extended by the two classes Machine and Line.

Before the migration
The Device has a string property called TenantId.

The migration
I am introducing a class called Tenant. Every device has a Tenant. I change the type of the Device.TenantId from string to int. It is now the foreign key to the Tenant table.

Here is the code (classes and DB context configuration):

public abstract class Device
    {
        public int Id { get; set; }

        public int TenantId { get; set; }

        public Tenant Tenant { get; set; } // Navigation property

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

    public string Name { get; set; }

    public List<Device> Devices { get; set; } = []; // Navigation property

    public List<Line> Lines { get; set; } = []; // Navigation property

    public List<Machine> Machines { get; set; } = []; // Navigation property

modelBuilder.Entity<Device>(builder =>
            {
                builder.ToTable("Devices").HasKey(d => new { d.Id });
                builder.HasDiscriminator(d => d.DeviceType)
                    .HasValue<Machine>(DeviceType.Machine)
                    .HasValue<Line>(DeviceType.Line);

                builder.HasOne(device => device.Tenant)
                    .WithMany(tenant => tenant.Devices)
                    .HasForeignKey(device => device.TenantId);
            });

            modelBuilder.Entity<Machine>(builder =>
            {
                builder.HasBaseType<Device>();
                builder.HasOne(m => m.Line)
                    .WithMany(dl => dl.Machines)
                    .HasForeignKey(m => m.LineId);

                builder.HasOne<Tenant>()
                    .WithMany(tenant => tenant.Machines)
                    .HasForeignKey(device => device.TenantId);

                
            });

            modelBuilder.Entity<Line>(builder =>
            {
                builder.HasBaseType<Device>();

                builder.HasOne<Tenant>()
                    .WithMany(tenant => tenant.Lines)
                    .HasForeignKey(device => device.TenantId);
            });

Problem
When I create the migration, it outputs: The foreign key property 'Device.TenantId1' was created in shadow state because a conflicting property with the simple name 'TenantId' exists in the entity type, but is either not mapped, is already used for another relationship, or is incompatible with the associated primary key type. See https://aka.ms/efcore-relationships for information on mapping relationships in EF Core.

Then, it alters the type of the TenantId column from text to int (correct), but also adds a new column:

migrationBuilder.AlterColumn<int>(
                name: "TenantId",
                table: "Devices",
                type: "integer",
                nullable: false,
                defaultValue: 0,
                oldClrType: typeof(string),
                oldType: "text",
                oldNullable: true);

            migrationBuilder.AddColumn<int>(
                name: "TenantId1",
                table: "Devices",
                type: "integer",
                nullable: true);

My expectation is that there is no need for TenantId1. I don't know why EF adds it and I don't know how I can get rid of it.

I am lost on this one, I was working on this problem for a few hours now but I can't find a soution.

Answer 1

As Machine extends Device, remove:

public List<Machine> Machines { get; set; } = []; // Navigation property

along with the configuration:

builder.HasOne<Tenant>()
    .WithMany(tenant => tenant.Machines)
    .HasForeignKey(device => device.TenantId);

This is doubling up the relationship so EF will want a second FK on the Machine/Device table (whether TPT/C or TPH). If for convenience you want your tenant to filter "Machines" at runtime:

[NotMapped]
public IEnumerable<Machine> Machines => Devices.OfType<Machine>();

However you cannot use that in a query expression. For instance this won't work:

var tenant = _context.Tenants
    .Where(x => x.Machines.Any(m => /* some criteria */)
    .FirstOrDefault();

You would have to inspect through Devices to find machines:

var tenant = _context.Tenants
    .Where(x => x.Devices.OfType<Machine>().Any(m => /* some criteria */))
    .FirstOrDefault();

Note: Also remove setters from collection navigation properties. EF collections should never be re-initialized, public setters are dangerous as they can end up breaking change tracking leading to errors when saving.

public List<Device> Devices { get; } = []; 

Any code attempting to set the collection is a potential problem.