Error when accessing data, EF Core seems to create its own column

Question

I have a project that has uses a database-first approach. I have used db-scaffold to create the models and have also created 'custom' models to contain extra info and code.

I have a model class VoteQuestion:

using System;
using System.Collections.Generic;

namespace BRPA_CORE_MVC.Models;

public partial class VoteQuestion
{
    public int QuestionId { get; set; }
    public int MeetingId { get; set; }
    public int QuestionSortNumber { get; set; }

    public string QuestionText { get; set; } = null!;
    public string AnswerType { get; set; } = null!;

    public string? IsOpen { get; set; }
}

And an accompanying model class VotePossibleAnswer that can have multiple possible answers to a question:

using System;
using System.Collections.Generic;

namespace BRPA_CORE_MVC.Models;

public partial class VotePossibleAnswer
{
    public int AnswerId { get; set; }
    public int QuestionId { get; set; }

    public string AnswerText { get; set; } = null!;
    public string IncludeInResult { get; set; } = null!;
    public string IncludeInAttendance { get; set; } = null!;
}

In the custom model for VotePossibleAnswer, I have the following code:

public static List<VotePossibleAnswer> GetAllVotePossibleAnswersListByQuestionID(int QuestionId)
{
    using (var ctx = new brpaContext())
    {
        List<VotePossibleAnswer> answerList = new List<VotePossibleAnswer>();
        // answerList = (from a in ctx.VotePossibleAnswers where a.QuestionId == QuestionId select a).OrderBy(a => a.AnswerText).ToList();
        // answerList = (from a in ctx.VotePossibleAnswers where a.QuestionId == QuestionId select a).ToList();
        // answerList = (from a in ctx.VotePossibleAnswers select a).ToList();
        VotePossibleAnswer votePossibleAnswer = (from a in ctx.VotePossibleAnswers select a).FirstOrDefault();
        answerList = (from a in ctx.VotePossibleAnswers where a.QuestionId == QuestionId select a).ToList();

        return answerList;
    }
}

As you can see from the commented out lines, I have tried a few versions...

The solution builds correctly, but when I try to get the PossibleAnswers for a VoteQuestion I get this run-time error:

Microsoft.Data.SqlClient.SqlException: Invalid column name 'VoteQuestionQuestionId'

The dbContext looks like this

modelBuilder.Entity<VotePossibleAnswer>(entity =>
{
    entity.HasKey(e => e.AnswerId);

    entity.ToTable("VotePossibleAnswer");

    entity.Property(e => e.AnswerId).HasColumnName("AnswerID");
    entity.Property(e => e.IncludeInAttendance).HasMaxLength(1);
    entity.Property(e => e.IncludeInResult).HasMaxLength(1);
    entity.Property(e => e.QuestionId).HasColumnName("QuestionID");
});

modelBuilder.Entity<VoteQuestion>(entity =>
{
    entity.HasKey(e => e.QuestionId);

    entity.ToTable("VoteQuestion");

    entity.Property(e => e.QuestionId).HasColumnName("QuestionID");
    entity.Property(e => e.AnswerType).HasMaxLength(50);
    entity.Property(e => e.IsOpen)
        .HasMaxLength(1)
        .IsUnicode(false);
    entity.Property(e => e.MeetingId).HasColumnName("MeetingID");
});

I have searched for a solution, but cannot find any.

VoteQuestionQuestionId as a text string.

Any suggestions would be most welcome.

One of the problems with ORMs (and with posting the code that ORMs are running) is that we can't see the actual SQL it generated and sent to SQL Server. The error is coming from a SQL query that EF manufactured. If there a way to show what that string is? Is might give clues about where this column name came from. — Aaron Bertrand
– Aaron Bertrand, Commented Oct 16 at 12:27
Hi Aaron, I'm not sure if this is possible, but I'm fairly new to EF Core. It would be great though! Lewies has provided me with the path to the solution (but I'm not exactly sure which now it is as I made three changes before testing again - a mistake I know... — Paul
– Paul, Commented Oct 16 at 13:26

Lewie · Accepted Answer · 2025-10-16 09:53:40Z

3

Why you're getting this error:

Entity Framework Core is detecting a relationship between VotePossibleAnswer and VoteQuestion (either through navigation properties or by convention), but since you haven't explicitly configured the relationship in your DbContext, EF Core is trying to auto-generate a foreign key column.

EF Core follows the naming convention: [NavigationPropertyName][PrimaryKeyName]. So it's attempting to create a column called VoteQuestionQuestionId to link the two tables. However, this column doesn't exist in your actual database - you already have QuestionId as the foreign key.

The solution is to explicitly tell EF Core to use your existing QuestionId property as the foreign key, rather than letting it try to create its own:

Update - You will need to delete the Db I presume or find a way to update it -- maybe!

public partial class VoteQuestion
{
    public int QuestionId { get; set; }
    public int MeetingId { get; set; }
    public int QuestionSortNumber { get; set; }
    public string QuestionText { get; set; } = null!;
    public string AnswerType { get; set; } = null!;
    public string? IsOpen { get; set; }
    
    // Navigation property
    public virtual ICollection<VotePossibleAnswer> PossibleAnswers { get; set; } 
        = new List<VotePossibleAnswer>();
}


public partial class VotePossibleAnswer
{
    public int AnswerId { get; set; }
    public int QuestionId { get; set; }
    public string AnswerText { get; set; } = null!;
    public string IncludeInResult { get; set; } = null!;
    public string IncludeInAttendance { get; set; } = null!;
    
    // Navigation property
    public virtual VoteQuestion Question { get; set; } = null!;
}

public partial class brpaContext : DbContext
{
    public brpaContext()
    {
    }

    public brpaContext(DbContextOptions<brpaContext> options)
        : base(options)
    {
    }

    public virtual DbSet<VoteQuestion> VoteQuestions { get; set; }
    public virtual DbSet<VotePossibleAnswer> VotePossibleAnswers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<VotePossibleAnswer>(entity =>
        {
            entity.HasKey(e => e.AnswerId);
            entity.ToTable("VotePossibleAnswer");

            entity.Property(e => e.AnswerId).HasColumnName("AnswerID");
            entity.Property(e => e.QuestionId).HasColumnName("QuestionID");
            entity.Property(e => e.IncludeInAttendance).HasMaxLength(1);
            entity.Property(e => e.IncludeInResult).HasMaxLength(1);
            
            // Configure the relationship
            entity.HasOne(p => p.Question)
                .WithMany(q => q.PossibleAnswers)
                .HasForeignKey(p => p.QuestionId)
                .HasConstraintName("FK_VotePossibleAnswer_VoteQuestion")
                .OnDelete(DeleteBehavior.Restrict);
        });

        modelBuilder.Entity<VoteQuestion>(entity =>
        {
            entity.HasKey(e => e.QuestionId);
            entity.ToTable("VoteQuestion");

            entity.Property(e => e.QuestionId).HasColumnName("QuestionID");
            entity.Property(e => e.AnswerType).HasMaxLength(50);
            entity.Property(e => e.IsOpen)
                .HasMaxLength(1)
                .IsUnicode(false);
            entity.Property(e => e.MeetingId).HasColumnName("MeetingID");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }

If that works, you can then update the query command you have like so

public static List<VotePossibleAnswer> GetAllVotePossibleAnswersListByQuestionID(int questionId)
{
    using (var ctx = new brpaContext())
    {
        return ctx.VoteQuestions
            .Where(q => q.QuestionId == questionId)
            .SelectMany(q => q.PossibleAnswers)
            .OrderBy(a => a.AnswerText)
            .ToList();
    }
}

edited Oct 16 at 9:53

answered Oct 16 at 9:28

Lewie

13211 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Paul Oct 16 at 9:35

Hi Lewie, thanks for the answer, I'm afraid adding the code you've listed hasn't worked. Do I also need the relationship to the VoteQuestion entity as a one-to-many?

Lewie Oct 16 at 9:37

Same error? or new one

Paul Oct 16 at 9:41

Hi Lewie - same error I'm afraid... Invalid column name 'VoteQuestionQuestionId'.

Paul Oct 16 at 9:57

Hi Lewie, again, thanks for your effort. I can't seem to get rid of the error, but you've given me a path to follow in terms of foreign key relationships, and the way EF Core creates them in the background. The foreign-keys are not specifically set up in the database, as I prefer to manage these in code. I'll update the database at source with these, re-scaffold it and go from there. Cheers for your quick responses.

Lewie Oct 16 at 10:02

No worries mate- Sorry it did not work, Someone will get to you soon no doubt! Best of luck

Aaron Bertrand Oct 16 at 13:11

The right place for table-level constraints is in the database. This makes sure that data changes implemented outside of your code still obey the constraints you want.

Paul Oct 16 at 13:24

Afternoon Lewie. I made the cardinal sin of changing more than one thing before testing again :-( and it works. I'm marking your answer as correct, as this led me directly to getting it working. Thanks for your help.

Lewie Oct 16 at 13:38

No worries and that is great to hear! haha we all do things like that too. Thank you the accepting the answer too

|

Mohammad Diwan · Accepted Answer · 2025-10-16 09:21:01Z

-2

The error is EF Core trying to infer a foreign key that doesn’t exist.
Add navigation properties or define the relationship explicitly in OnModelCreating.

answered Oct 16 at 9:21

Mohammad Diwan

1

1 Comment

Dale K Oct 16 at 9:23

Can you actually provide the code required for a complete answer

Collectives™ on Stack Overflow

Error when accessing data, EF Core seems to create its own column

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related