Issue with POST method of Auto generated Controller for Dotnet Web API project

Question

I am trying to learn the Dotnet 5 Web API by creating a Controllers for my Entity framework generated models of DB Table. The DB structure is pretty simple and contains 3 Tables (PaymentDetail, CustomerDetail and ClubStateDetail). The DBContext file generated through EF has the constructor as seen below :

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

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

        public virtual DbSet<ClubStateDetail> ClubStateDetails { get; set; }
        public virtual DbSet<CustomerDetail> CustomerDetails { get; set; }
        public virtual DbSet<PaymentDetail> PaymentDetails { 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("Server=.;Database=PaymentDetailDB;Trusted_Connection=True;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "SQL_Latin1_General_CP1_CI_AS");

            modelBuilder.Entity<ClubStateDetail>(entity =>
            {
                entity.HasKey(e => e.ClubStateId);

                entity.ToTable("ClubStateDetail");

                entity.Property(e => e.ClubStateId)
                    .ValueGeneratedNever()
                    .HasColumnName("ClubStateID");

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(50);
            });

            modelBuilder.Entity<CustomerDetail>(entity =>
            {
                entity.HasKey(e => e.CardOwnerId);

                entity.ToTable("CustomerDetail");

                entity.Property(e => e.CardOwnerId)
                    .ValueGeneratedNever()
                    .HasColumnName("CardOwnerID");

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(100);

                entity.HasOne(d => d.ClubStateNavigation)
                    .WithMany(p => p.CustomerDetails)
                    .HasForeignKey(d => d.ClubState)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_CustomerDetail_ClubStateDetail");
            });

            modelBuilder.Entity<PaymentDetail>(entity =>
            {
                entity.ToTable("PaymentDetail");

                entity.Property(e => e.PaymentDetailId).HasColumnName("PaymentDetailID");

                entity.Property(e => e.CardNumber).HasMaxLength(15);

                entity.Property(e => e.CardOwnerId).HasColumnName("CardOwnerID");

                entity.Property(e => e.Cvv)
                    .HasMaxLength(6)
                    .HasColumnName("CVV");

                entity.Property(e => e.Expirationdate).HasMaxLength(6);
            });

            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }

So to begin with since each customer has to have a ClubState, I created the ClubState controller who's POST method looks like this :

// POST: api/ClubStateDetails
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<ActionResult<ClubStateDetail>> PostClubStateDetail(ClubStateDetail clubStateDetail)
        {
            _context.ClubStateDetail.Add(clubStateDetail);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetClubStateDetail", new { id = clubStateDetail.ClubStateId }, clubStateDetail);
        }

Now, I head over to Swagger UI to test out this API and it required the request body JSON data to be of the format below(Please note that I have already put values for my clubStateId and name) :

{
  "clubStateId": 1,
  "name": "Gold",
  "customerDetails": [
    {
      "cardOwnerId": 0,
      "name": "string",
      "clubState": 0
    }
  ]
}

Since I do not have any customers as of now, I have left those values unchanged, they remain as they are in the sample JSON data provided by Swagger(I realize, this might be an issue but since I dont have any customers as of now, I am not sure what to put in there).

Now when I try to execute this POST request I get an error saying

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'ClubStateNavigationClubStateId'.

ClubStateNavigationClubStateId is not a column in my ClubState table, however when I look at the JSON data expected for the CustomerDetail Controller, I do see this :

{
  "cardOwnerId": 0,
  "name": "string",
  "clubState": 0,
  "clubStateNavigation": {
    "clubStateId": 0,
    "name": "string",
    "customerDetails": [
      null
    ]
  }
}

So my question is, how do I POST data using my API when I do not have any customers ready yet?

Answer 1

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'ClubStateNavigationClubStateId'.

This means that the database does not match what EF DbContext configuration. EF thinks there's a column called ClubStateNavigationClubStateId that doesn't exist in the database.

Did you Reverse Engineer the model from the database?