C#•2y ago

❔ 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?

12 Replies

nickk•2y ago

help him.

lycian•2y ago

If you comment out the Game model/context, does it work? What does the Game model look like?

Angius•2y ago

[Column("challonge_id")] hurts me on a physical level in more ways than one Seems something was misconfigured at some point Maybe GameId prop doesn't have the column name specified And the database has some game_id column

StanOP•2y ago

This is the Game model:

public class Game
{
    [Column("id")]
    public int Id { get; set; }
    public string Name { get; set; }
    
    public List<Event> Events { get; set; }
}

public class Game
{
    [Column("id")]
    public int Id { get; set; }
    public string Name { get; set; }
    
    public List<Event> Events { get; set; }
}

could you explain what's bad about this? this is just a column in the database relating each event to a bracket made in challonge.com

Angius•2y ago

Ah, well, for the relationship between the game and the event to exist, the event has to have game ID

lycian•2y ago

Angius•2y ago

You don't have that set up explicitly it seems, so EF tries to generate it I just see specifying names as useless. Just let EF handle it and name everything however it sees fit

lycian•2y ago

unless you're going to be writing queries outside of EF, hard agree.

StanOP•2y ago

ah like that ill likely end up doing that with migrations yeah

Angius•2y ago

class Event
{
    public int Id { get; set; }
    //...
    public Game Game { get; set; }
    public int GameId { get; set; }
}

class Game
{
    public int Id { get; set; }
    //...
    public List<Event> Events { get; set; }
}

class Event
{
    public int Id { get; set; }
    //...
    public Game Game { get; set; }
    public int GameId { get; set; }
}

class Game
{
    public int Id { get; set; }
    //...
    public List<Event> Events { get; set; }
}

is how I'd set it up You have both the nav property and the ID if need be, and everything is convention-based so zero-config

StanOP•2y ago

alright i got something working thank you guys :)

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

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