CODEWITHSUNDEEP
entity-framework-corenpgsql
user3953989
user3953989

Reputation: 1941

EF generating query with invalid column resulting in PostgresException "column does not exist"

Trying to do a basic one-to-many relationship but getting the below error. It's adding another accountstatusid column in the query but the duplicate has an additional 1 at the end.

Error: Npgsql.PostgresException (0x80004005): 42703: column a.accountstatusid1 does not exist

Generated Query

SELECT a.accountid, a.accountstatusid, a.accountstatusid1, a.name
FROM account AS a  
WHERE a.accountid = @__accountId_0  
LIMIT 2

Database

CREATE TABLE accountstatus
(
    accountstatusid smallint NOT NULL GENERATED ALWAYS AS IDENTITY,
    name character varying(128) NOT NULL,
    CONSTRAINT accountstatus_pkey PRIMARY KEY (accountstatusid)
)

CREATE TABLE account
(
    accountid integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    accountstatusid smallint NOT NULL,
    name character varying(256) NOT NULL,
    CONSTRAINT account_pkey PRIMARY KEY (accountid),
    CONSTRAINT fk_accountstatusidid FOREIGN KEY (accountstatusid)
)

Models

public class Account
{
    public int AccountId { get; set; }
    public string Name { get; set; }
    public short AccountStatusId { get; set; }

    public virtual AccountStatus AccountStatus { get; set; }
}

public class AccountStatus
{
    public int AccountStatusId { get; set; }
    public string Name { get; set; }
}

DataContext

public class DataContext : DbContext
{
    public DbSet<AccountStatus> AccountStatuses { get; set; }
    public DbSet<Account> Accounts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<AccountStatus>().ToTable("accountstatus");
        modelBuilder.Entity<Account>(x => 
        {
            x.ToTable("account");
            x.HasOne(x => x.AccountStatus);
        }); 
    }
}

Upvotes: 1

Views: 343

Answers (1)

Serhii
Serhii

Reputation: 753

The reason is that AccountStatus has Key of type int and your foreign key(Account.AccountStatusId) is of type short and the EF is not recognized Account.AccountStatusId as a foreign key to AccountStatus table and creates a new field.

If this won`t help you could try to set up a foreign key inside your model configuration.

Upvotes: 1

Related Questions