使用EF插入数据报错：INSERT语句中列名重复

Hey there, let's break down why you're running into this Entity Framework insert error and how to fix it quickly.

First, let's unpack the error message you're seeing:

The column name '' is specified more than once in the SET clause or column list of an INSERT

This means Entity Framework is generating an INSERT statement that includes the same column (or an empty column reference) multiple times. Looking at your entity class and partial save method, the root cause is almost certainly how you're handling the auto-increment id column.

Why This Happens #

Your QUESTION entity marks the id property with [DatabaseGenerated(DatabaseGeneratedOption.Identity)]—this tells EF that the database will automatically generate a value for this column (like SQL Server's IDENTITY feature). But in your Save method, you're trying to assign a value to dbquest.id manually. This causes EF to include the id column twice in the INSERT statement: once for the auto-generated value, and once for the value you're setting, triggering the duplicate column error.

Fixes to Try #

1. Remove manual assignment of the auto-increment id column
   Since the database handles generating the id value, you don't need to set it explicitly. Update your Save method to only populate the other properties:

   public string Save(QUESTION newquestion) {
       try {
           using (dbcontextdata Context = new dbcontextdata()) {
               QUESTION dbquest = new QUESTION();
               // Skip assigning id entirely—let the database handle it
               dbquest.q_id = newquestion.q_id;
               dbquest.questname = newquestion.questname;
               dbquest.optionname = newquestion.optionname;
               
               Context.QUESTION.Add(dbquest);
               Context.SaveChanges();
               return "Save successful";
           }
       } catch (Exception ex) {
           return $"Save failed: {ex.Message}";
       }
   }
   

2. Verify database and entity mapping match

   • Double-check that the id column in your QUESTION database table is actually configured as an auto-increment column (e.g., IDENTITY(1,1) in SQL Server).
   • Ensure the [Table("QUESTION")] attribute matches your database table name exactly (case sensitivity depends on your database system—Oracle is case-sensitive, while SQL Server is not by default).

3. Check for duplicate column mappings (for future changes)
   If you add [Column] attributes to your entity properties later, make sure you don't accidentally map two properties to the same database column—this would also trigger the duplicate column error.

Edge Case: If You Need to Manually Set the Auto-Increment ID #

If you have a rare scenario where you need to insert a custom value into the id column, you'll need to:

• Update the entity's DatabaseGenerated option to None:

  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int id { get; set; }
  

• Enable identity insert for your table (example for SQL Server):

  SET IDENTITY_INSERT QUESTION ON;
  

  Don't forget to disable it after your insert operation:

  SET IDENTITY_INSERT QUESTION OFF;
  

内容的提问来源于stack exchange，提问作者pammu