sql server – EF core Invalid column name after reverse ingeneering database using EF Power Tools

I have an API using ASP.NET Core 6 & EF6 and I reverse engieneer my database (SQL Server) using EF Core Powers Tool extension. When I try to insert a Person entity inside, I got the error Invalid column name ‘TeamName’, but TeamName does not exist in the database and in the model class. I also don’t pass this field in the JSON body to the controller.

Thank you in advance

The exception:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (31ms) [Parameters=[@p0='?' (Size = 255) (DbType = AnsiString), @p1='?' (Size = 255) (DbType = AnsiString), @p2='?' (Size = 255) (DbType = AnsiString), @p3='?' (Size = 255) (DbType = AnsiString), @p4='?' (DbType = Int16), @p5='?' (Size = 255) (DbType = AnsiString), @p6='?' (Size = 255) (DbType = AnsiString)], CommandType="Text", CommandTimeout="30"]
      SET NOCOUNT ON;
      INSERT INTO [Person] ([Email], [FirstName], [LastName], [Password], [Role], [Team], [TeamName])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'pi_gr3_gamejam.Data.PI_G3_GamejamContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'TeamName'.

Person Model (Person.cs)

// <auto-generated> This file has been auto generated by EF Core Power Tools. </auto-generated>
#nullable disable
using System;
using System.Collections.Generic;

namespace pi_gr3_gamejam.Models
{
    public partial class Person
    {
        public string Email { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string Password { get; set; }
        public short Role { get; set; }
        public string Team { get; set; }
    }
}

DB Context

// <auto-generated> This file has been auto generated by EF Core Power Tools. </auto-generated>
#nullable disable
using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using pi_gr3_gamejam.Models;

namespace pi_gr3_gamejam.Data
{
    public partial class PI_G3_GamejamContext : DbContext
    {
        public PI_G3_GamejamContext()
        {
        }

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

        public virtual DbSet<GameJam> GameJams { get; set; }
        public virtual DbSet<Grade> Grades { get; set; }
        public virtual DbSet<Person> People { get; set; }
        public virtual DbSet<Team> Teams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseSqlServer("Data Source=localhost;Initial Catalog=PI_G3_GameJam;User ID=-;Password=-");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<GameJam>(entity =>
            {
                entity.ToTable("GameJam");

                entity.Property(e => e.Id).ValueGeneratedNever();

                entity.Property(e => e.ParamsXml)
                    .IsRequired()
                    .HasColumnType("xml")
                    .HasColumnName("ParamsXML");
            });

            modelBuilder.Entity<Grade>(entity =>
            {
                entity.HasKey(e => new { e.JurorEmail, e.TeamName })
                    .HasName("PK__Grade__7F8D9D9FF0F9D91F");

                entity.ToTable("Grade");

                entity.Property(e => e.JurorEmail)
                    .HasMaxLength(255)
                    .IsUnicode(false);

                entity.Property(e => e.TeamName)
                    .HasMaxLength(255)
                    .IsUnicode(false);

                entity.Property(e => e.Grade1).HasColumnName("Grade");

            });

            modelBuilder.Entity<Person>(entity =>
            {
                entity.HasKey(e => e.Email)
                    .HasName("PK__Person__A9D105351D885BFE");

                entity.ToTable("Person");

                entity.Property(e => e.Email)
                    .HasMaxLength(255)
                    .IsUnicode(false);

                entity.Property(e => e.FirstName)
                    .IsRequired()
                    .HasMaxLength(255)
                    .IsUnicode(false);

                entity.Property(e => e.LastName)
                    .IsRequired()
                    .HasMaxLength(255)
                    .IsUnicode(false);

                entity.Property(e => e.Password)
                    .IsRequired()
                    .HasMaxLength(255)
                    .IsUnicode(false);

                entity.Property(e => e.Team)
                    .HasMaxLength(255)
                    .IsUnicode(false);
            });

            modelBuilder.Entity<Team>(entity =>
            {
                entity.HasKey(e => e.Name)
                    .HasName("PK__Team__737584F786C5BA4A");

                entity.ToTable("Team");

                entity.Property(e => e.Name)
                    .HasMaxLength(255)
                    .IsUnicode(false);

                entity.Property(e => e.GameDescription).HasColumnType("text");

                entity.Property(e => e.GameName)
                    .HasMaxLength(255)
                    .IsUnicode(false);
            });

            OnModelCreatingGeneratedProcedures(modelBuilder);
            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

Controller

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using pi_gr3_gamejam.Data;
using pi_gr3_gamejam.Models;
using pi_gr3_gamejam.Services;
using Microsoft.AspNetCore.Authorization;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace pi_gr3_gamejam.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class RegisterController : ControllerBase
    {
        private readonly PI_G3_GamejamContext _context;
        public RegisterController(PI_G3_GamejamContext context)
        {
            _context = context;
        }

        // POST api/<RegisterController>
        [HttpPost]
        [Route("registerJuror")]
        //[Authorize(Roles = "Organisator")]
        public async Task<ActionResult<Person>> PostPerson([FromBody] Person person)
        {
            RegisterService registerService = new RegisterService();
            if (!registerService.checkPersonRegistration(person)) return BadRequest();

            using (var contextTransaction = _context.Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
            {
                person.Role = 2;
                person.Team = null;
                _context.People.Add(person);

                try
                {
                    await _context.SaveChangesAsync();
                    await contextTransaction.CommitAsync();
                }

                catch (DbUpdateException)
                {
                    contextTransaction.Rollback();
                    return BadRequest();
                }
            }

            return CreatedAtAction("PostPerson", new { id = person.Email }, person);
        }
    }
}

SQL Person code

CREATE TABLE Person(
    Email varchar(255) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Password varchar(255) NOT NULL,
    Role smallint NOT NULL CHECK (Role BETWEEN 1 AND 3), -- CR2, 1=Orga / 2=Juror / 3=Participant
    Team varchar(255),

    CONSTRAINT FK_team FOREIGN KEY (Team)
    REFERENCES Team(Name)
    ON DELETE CASCADE
);
GO

Curl request (generated by swagger UI)

curl -X 'POST' 
  'https://localhost:7140/api/Register/registerJuror' 
  -H 'accept: text/plain' 
  -H 'Content-Type: application/json' 
  -d '{
  "email": "a@a",
  "lastName": "a",
  "firstName": "a",
  "password": "a",
  "role": 0,
  "team": "-"
}'

Leave a Comment