Stan Posts - Answer Overflow

Stan

•Created by Stan on 2/7/2023 in #help

❔ Error adding new model in EF Core

I'm trying to add a new model "Tickets" in my ASP.NET Core web API. After trying to update the database with a migration, it slaps me in the face with

Introducing FOREIGN KEY constraint 'FK_Tickets_Zitplaatsen_ZitplaatsId' on table 'Tickets' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

Introducing FOREIGN KEY constraint 'FK_Tickets_Zitplaatsen_ZitplaatsId' on table 'Tickets' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

These are the (hopefully) relevant currently existing models I have:

public class Ticket
{
    public int Id { get; set; }
    
    public Uitvoering? Uitvoering { get; set; }
    public Zitplaats? Zitplaats { get; set; }
    public IdentityUser? Klant { get; set; }
}

public class Ticket
{
    public int Id { get; set; }
    
    public Uitvoering? Uitvoering { get; set; }
    public Zitplaats? Zitplaats { get; set; }
    public IdentityUser? Klant { get; set; }
}

public class Zitplaats
{
    public int Id { get; set; }
    
    public int X { get; set; }
    public int Y { get; set; }
    
    public int Rang { get; set; }
    
    public Zaal? Zaal { get; set; }
    
    public List<Ticket> Tickets { get; set; }
}

public class Zitplaats
{
    public int Id { get; set; }
    
    public int X { get; set; }
    public int Y { get; set; }
    
    public int Rang { get; set; }
    
    public Zaal? Zaal { get; set; }
    
    public List<Ticket> Tickets { get; set; }
}

public class Zaal
{
    public int Id { get; set; }
    public List<Zitplaats> Zitplaatsen { get; set; }
    public List<Uitvoering> Uitvoeringen { get; set; }
}

public class Zaal
{
    public int Id { get; set; }
    public List<Zitplaats> Zitplaatsen { get; set; }
    public List<Uitvoering> Uitvoeringen { get; set; }
}

public class Uitvoering
{
    public int Id { get; set; }
    
    public DateTime StartTijd { get; set; }
    
    public Voorstelling Voorstelling { get; set; }
    public Zaal? Zaal { get; set; }
    
    public List<Ticket> Tickets { get; set; }
}

public class Uitvoering
{
    public int Id { get; set; }
    
    public DateTime StartTijd { get; set; }
    
    public Voorstelling Voorstelling { get; set; }
    public Zaal? Zaal { get; set; }
    
    public List<Ticket> Tickets { get; set; }
}

I've looked around and tried adding this in my OnModelCreating:

        builder.Entity<Ticket>()
            .HasOne(x => x.Zitplaats)
            .WithMany(x => x.Tickets)
            .HasForeignKey(x => x.ZitplaatsId)
            .OnDelete(DeleteBehavior.Restrict);

        builder.Entity<Ticket>()
            .HasOne(x => x.Zitplaats)
            .WithMany(x => x.Tickets)
            .HasForeignKey(x => x.ZitplaatsId)
            .OnDelete(DeleteBehavior.Restrict);

which gave the same error. Any help would be appreciated :)

8 replies

CC#

•Created by Stan on 1/29/2023 in #help

❔ EF Core tries to query a non-existant column for no apparent reason

I am making a simple web API in ASP.NET with MVC. I have the following model Event:

using System.ComponentModel.DataAnnotations.Schema;

namespace web_api.Models;

[Table("event")]
public class Event
{
    [Column("id")]
    public int Id { get; set; }
    public string? Name { get; set; }
    
    [Column("team_size")]
    public int TeamSize { get; set; }
    
    [Column("playing_team_size")]
    public int PlayingTeamSize { get; set; }
    
    [Column("entry_fee")]
    public int EntryFee { get; set; }
    
    [Column("lower_rank_limit")]
    public int LowerRankLimit { get; set; }
    
    [Column("upper_rank_limit")]
    public int? UpperRankLimit { get; set; }
    
    [Column("challonge_id")]
    public string? ChallongeId { get; set; }
}

using System.ComponentModel.DataAnnotations.Schema;

namespace web_api.Models;

[Table("event")]
public class Event
{
    [Column("id")]
    public int Id { get; set; }
    public string? Name { get; set; }
    
    [Column("team_size")]
    public int TeamSize { get; set; }
    
    [Column("playing_team_size")]
    public int PlayingTeamSize { get; set; }
    
    [Column("entry_fee")]
    public int EntryFee { get; set; }
    
    [Column("lower_rank_limit")]
    public int LowerRankLimit { get; set; }
    
    [Column("upper_rank_limit")]
    public int? UpperRankLimit { get; set; }
    
    [Column("challonge_id")]
    public string? ChallongeId { get; set; }
}

and the following EventController:

using Microsoft.AspNetCore.Mvc;
using web_api.Data;
using web_api.Models;

namespace web_api.Controllers;

[ApiController]
[Route("api/[controller]")]
public class EventController : ControllerBase
{
    private readonly CESGamingContext _context;
    
    public EventController(CESGamingContext context)
    {
        _context = context;
    }
    
    [HttpGet]
    public IEnumerable<Event> GetEvents()
    {
        return _context.Events;
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<Event>> GetEvent(int id)
    {
        var searchedEvent = await _context.Events.FindAsync(id);

        if (searchedEvent == null)
        {
            return NotFound();
        }

        return searchedEvent;
    }
    
}

using Microsoft.AspNetCore.Mvc;
using web_api.Data;
using web_api.Models;

namespace web_api.Controllers;

[ApiController]
[Route("api/[controller]")]
public class EventController : ControllerBase
{
    private readonly CESGamingContext _context;
    
    public EventController(CESGamingContext context)
    {
        _context = context;
    }
    
    [HttpGet]
    public IEnumerable<Event> GetEvents()
    {
        return _context.Events;
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<Event>> GetEvent(int id)
    {
        var searchedEvent = await _context.Events.FindAsync(id);

        if (searchedEvent == null)
        {
            return NotFound();
        }

        return searchedEvent;
    }
    
}

and the following DbContext:

public class CESGamingContext : DbContext
{
    public CESGamingContext(DbContextOptions<CESGamingContext> options)
        : base(options)
    {
    }
    
    public DbSet<Event> Events { get; set; }
    public DbSet<Game> Games { get; set; }
    // public DbSet<Map> Maps { get; set; }
    // public DbSet<Match> Matches { get; set; }
    public DbSet<Organisation> Organisations { get; set; }
    // public DbSet<Rating> Ratings { get; set; }
    // public DbSet<Round> Rounds { get; set; }
    // public DbSet<Team> Teams { get; set; }
    // public DbSet<User> Users { get; set; }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

public class CESGamingContext : DbContext
{
    public CESGamingContext(DbContextOptions<CESGamingContext> options)
        : base(options)
    {
    }
    
    public DbSet<Event> Events { get; set; }
    public DbSet<Game> Games { get; set; }
    // public DbSet<Map> Maps { get; set; }
    // public DbSet<Match> Matches { get; set; }
    public DbSet<Organisation> Organisations { get; set; }
    // public DbSet<Rating> Ratings { get; set; }
    // public DbSet<Round> Rounds { get; set; }
    // public DbSet<Team> Teams { get; set; }
    // public DbSet<User> Users { get; set; }


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

I've commented out some of the models which are not in use right now. I am trying to get all events, but when I try to do that, it gives me the following error:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `e`.`id`, `e`.`challonge_id`, `e`.`entry_fee`, `e`.`GameId`, `e`.`lower_rank_limit`, `e`.`Name`, `e`.`OrganisationId`, `e`.`playing_team_size`, `e`.`team_size`, `e`.`upper_rank_limit`
      FROM `event` AS `e`
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'web_api.Data.CESGamingContext'.
      MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'e.GameId' in 'field list'
         at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
         at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
         at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `e`.`id`, `e`.`challonge_id`, `e`.`entry_fee`, `e`.`GameId`, `e`.`lower_rank_limit`, `e`.`Name`, `e`.`OrganisationId`, `e`.`playing_team_size`, `e`.`team_size`, `e`.`upper_rank_limit`
      FROM `event` AS `e`
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'web_api.Data.CESGamingContext'.
      MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'e.GameId' in 'field list'
         at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
         at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
         at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)

with a bunch of further lines (I can show if needed). The error seems clear to me, the column "GameId" doesn't exist, but I don't understand why it would even try to get it in the first place?

21 replies

Gaming

Programming