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": "-"
}'