EF Core 6 Invalid column name CustomerId1

Question

I see a lot of people with very similar issues but can't seem to figure out the answer that works for me. Not sure what I am doing wrong and have looked at it so long know that I fear I am just twisting in the wind. I have two classes:

public class Customer
{
    [Key]
    public int Id {get; set;}
    [Required(ErrorMessage = "Name is required")]
    [MaxLength(100, ErrorMessage="Name cannot be longer than 100 characters")]
    public string Name {get; set;}
    public IEnumerable<CustomerLink> CustomerLinks{get; set;} 
    [MaxLength(200, ErrorMessage="Create User cannot be longer than 200 characters")]
    public string CreateUser {get; set;}
    public DateTime? CreateDate {get; set;}
    [Required(ErrorMessage = "Modify User is required")]  
    [MaxLength(200, ErrorMessage="Modify User cannot be longer than 200 characters")]
    public string ModifyUser {get; set;}
    public DateTime? ModifyDate {get; set;}     
}

public class CustomerLink
{
    [Key]
    public int Id {get; set;}
    [Required(ErrorMessage = "Customer is required")]
    public Customer Customer {get; set;}
    [Required(ErrorMessage = "Child is required")]
    public virtual Customer Child {get; set;}
    [MaxLength(200, ErrorMessage="Create User cannot be longer than 200 characters")]
    public string CreateUser {get; set;}
    public DateTime? CreateDate {get; set;}
    [Required(ErrorMessage = "Modify User is required")]  
    [MaxLength(200, ErrorMessage="Modify User cannot be longer than 200 characters")]
    public string ModifyUser {get; set;}
    public DateTime? ModifyDate {get; set;}     
}

I am sure it is obvious to most who are reading but just in case. In the above scenario a Customer has many Links. When I retrieve a customer I need to get all of it's links. I am basically building up a parent child relationship amongst my Customers. When I run this I get the following:

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'CustomerId1'.

I currently have this in my Context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<CustomerLink>()
        .HasOne(l => l.Customer)
        .WithMany()
        .OnDelete(DeleteBehavior.Restrict);                  

    modelBuilder.Entity<CustomerLink>()
        .HasOne(l => l.Child)
        .WithMany()
        .OnDelete(DeleteBehavior.Restrict);   
}

I think that I need to add something to this that will assist EF in understanding the relationship better but can't figure it out. I have tried:

modelBuilder.Entity<Customer>()
    .HasMany(x => x.CustomerLinks)
        .WithOne(x => x.Child)
            .HasForeignKey(x => x.Customer);

That resulted in:

'Customer' cannot be used as a property on entity type 'CustomerLink' because it is configured as a navigation.

So then I thought maybe I am doing it backwards and need to add something to the CustomerLink definition so I tried the following:

modelBuilder.Entity<CustomerLink>()
    .HasOne(x => x.Child)
        .HasForeignKey(x => x.CustomerId);

But that tells me:

'ReferenceNavigationBuilder<CustomerLink, Customer>' does not contain a definition for 'HasForeignKey' and no accessible extension method 'HasForeignKey' accepting a first argument of type 'ReferenceNavigationBuilder<CustomerLink, Customer>' could be found (are you missing a using directive or an assembly reference?)

I have tried some other things that I just can't remember at this point. I spent some time trying solutions that were documented for EF6 and not EF6-core before I figured out I was looking at the wrong answers. :-(

Edit

Based on recommendations I have changed the models to as follows:

public class Customer
{
    [Key]
    public int Id {get; set;}
    [Required(ErrorMessage = "Name is required")]
    [MaxLength(100, ErrorMessage="Name cannot be longer than 100 characters")]
    public string Name {get; set;}
    public List<CustomerLink> CustomerLinks{get; set;} 
    [MaxLength(200, ErrorMessage="Create User cannot be longer than 200 characters")]
    public string CreateUser {get; set;}
    public DateTime? CreateDate {get; set;}
    [Required(ErrorMessage = "Modify User is required")]  
    [MaxLength(200, ErrorMessage="Modify User cannot be longer than 200 characters")]
    public string ModifyUser {get; set;}
    public DateTime? ModifyDate {get; set;}     
}

public class CustomerLink
{
    [Key]
    public int Id {get; set;}
    public int CustomerId {get; set;}
    [Required(ErrorMessage = "Customer is required")]
    public Customer Customer {get; set;}
    public int ChildId {get; set;}
    [Required(ErrorMessage = "Child is required")]
    public virtual Customer Child {get; set;}
    [MaxLength(200, ErrorMessage="Create User cannot be longer than 200 characters")]
    public string CreateUser {get; set;}
    public DateTime? CreateDate {get; set;}
    [Required(ErrorMessage = "Modify User is required")]  
    [MaxLength(200, ErrorMessage="Modify User cannot be longer than 200 characters")]
    public string ModifyUser {get; set;}
    public DateTime? ModifyDate {get; set;}     
}

I then updated my context to have the following

modelBuilder.Entity<CustomerLink>()
    .HasOne(l => l.Customer)
    .WithMany(c => c.CustomerLinks)
        .HasForeignKey(l => l.CustomerId);


modelBuilder.Entity<CustomerLink>()
    .HasOne(l => l.Child)
    .WithMany(c => c.CustomerLinks)
        .HasForeignKey(l => l.ChildId);     

This is what I thought the documentation sent to me was telling me to do but I am now getting the following when I attempt to generate the migrations (By the way I am using Code first against SQL Server)

Cannot create a relationship between 'Customer.CustomerLinks' and 'CustomerLink.Child' because a relationship already exists between 'Customer.CustomerLinks' and 'CustomerLink.Customer'. Navigations can only participate in a single relationship. If you want to override an existing relationship call 'Ignore' on the navigation 'CustomerLink.Child' first in 'OnModelCreating'.

My DB Schema looks like this

CREATE TABLE [QMR].[Customers](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [CreateUser] [nvarchar](200) NULL,
    [CreateDate] [datetime2](7) NULL,
    [ModifyUser] [nvarchar](200) NOT NULL,
    [ModifyDate] [datetime2](7) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [QMR].[CustomerLinks](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL,
    [ChildId] [int] NOT NULL,
    [CreateUser] [nvarchar](200) NULL,
    [CreateDate] [datetime2](7) NULL,
    [ModifyUser] [nvarchar](200) NOT NULL,
    [ModifyDate] [datetime2](7) NULL,
 CONSTRAINT [PK_CustomerLinks] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [QMR].[CustomerLinks]  WITH CHECK ADD  CONSTRAINT [FK_CustomerLinks_Customers_ChildId] FOREIGN KEY([ChildId])
REFERENCES [QMR].[Customers] ([Id])
GO

ALTER TABLE [QMR].[CustomerLinks] CHECK CONSTRAINT [FK_CustomerLinks_Customers_ChildId]
GO

ALTER TABLE [QMR].[CustomerLinks]  WITH CHECK ADD  CONSTRAINT [FK_CustomerLinks_Customers_CustomerId] FOREIGN KEY([CustomerId])
REFERENCES [QMR].[Customers] ([Id])
GO

ALTER TABLE [QMR].[CustomerLinks] CHECK CONSTRAINT [FK_CustomerLinks_Customers_CustomerId]
GO

I assume I did not follow the directions correctly or the documentation but am somewhat swimming at this point.

Answer 1

EF can't decide what FK it has to use (on the database side) for the following two properties:

public Customer Customer { get; set; }
        
public virtual Customer Child { get; set; }

And to add onto what @IvanGechev said in his comment:

Add the FKs manually to your model (on the side of CustomerLink) so EF knows what column refers to which navigation property. EF defaults to adding a 1 suffix to the implicit FK name when there is two navigation properties.

If a property with the same name already exists, then the shadow property name will be suffixed with a number.

Source: https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#no-foreign-key-property

The linked source should also explain how you can configure such navigation properties correctly, e.g. using HasForeignKey.

---

I guess the answer in your specific case boils down to - what does the database schema actually look like? Do you have code first or db first?

---

There's something else going on I think - please don't use IEnumerable for 1..* navigation properties - use something like IReadOnlyCollection or List. This might be the reason for your error - I'm not sure.