Why is EntityFrameWork adding a extra column to my table?

Question

So I have a Db with 2 tables with schemas as such:

CREATE TABLE Portfolios (
  Id INTEGER NOT NULL PRIMARY KEY,
  CurrentTotalValue FLOAT NOT NULL DEFAULT 0
);

CREATE TABLE Stocks (
  Id INTEGER NOT NULL PRIMARY KEY, 
  Portfolio_id INTEGER NOT NULL,
  Ticker VARCHAR(20) NOT NULL,
  BaseCurrency VARCHAR(5) NOT NULL,
  NumberOfShares INTEGER NOT NULL,
  CONSTRAINT fk_stocks_portfolios 
      FOREIGN KEY(Portfolio_id) REFERENCES portfolios(Id)
);

And two Db models as such:

public class Portfolio
{
    [Required]
    public int Id { get; set; }
    [Required]
    public double CurrentTotalValue { get; set; } = 0;
    [Required]
    public bool IsDeleted { get; set; } = false;
    public ICollection<Stock> Stocks { get; set; }
}

    public class Stock
    {
        [Required]
        public int Id { get; set; }
        [Required]
        public int Portfolio_id { get; set; }
        [Required]
        public string Ticker { get; set; }
        [Required]
        public string BaseCurrency { get; set; }
        [Required]
        public int NumberOfShares { get; set; }
    }

Now every time I try to get a portfolio I get this error message: "An exception occurred while iterating over the results of a query for context type

'PortfolioService.DataAcess.CurrencyDbContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'PortfolioId'."

I don't even have a column named "PortfolioId" I have "Portfolio_Id". I also notice just above this error message that EF seems to be inserting the extra column into the query as such:

SELECT [s].[Id], [s].[BaseCurrency], [s].[NumberOfShares], [s].[PortfolioId], [s].[Portfolio_id], [s].[Ticker]
      FROM [Stocks] AS [s]
      WHERE [s].[Portfolio_id] = @__id_0

Now my question is why does it do this and how do I fix it?

Answer 1

EF's convention most likely isn't recognizing the "_Id" naming convention you are using as the FK. You have nominated a One-to-many between Portfolio and Stocks which EF will be expecting to find a FK back to Portfolio on the Stock entity.

If there was a navigation property on Stock you would most likely had to explicitly set up the FK property to use:

public int Portfolio_Id { get; set; }
[ForeignKey(nameof(Portfolio_Id))]
public virtual Portfolio Portfolio { get; set; }

Since you don't have navigation property back we need to tell EF what property to use for the FK. This can be done either in the OnModelCreating or via EntityTypeConfiguration:

modelBuilder.Entity<Portfolio>()
    .HasMany(p => p.Stocks)
    .WithRequired()
    .HasForeignKey(s => s.Portfolio_Id);