.NET System.InvalidCastException异常：无需转换却出现类型转换错误

C#实体模型 #

[Table("league_seasons")]
public class LeagueSeason
{
    [Key]
    [Column("league_season_id")]
    public required int Id { get; set; }

    [ForeignKey("League")]
    [Column("league_id")]
    public required int LeagueId { get; set; }

    [ForeignKey("Season")]
    [Column("season_id")]
    public required int SeasonId { get; set; }

    [Column("yahoo_league_season_id")]
    public string? YahooLeagueSeasonId { get; set; }

    [Column("num_teams")]
    public required int NumTeams { get; set; }

    [Column("num_playoff_teams")]
    public int? NumPlayoffTeams { get; set; }

    [Column("num_weeks")]
    public required int NumWeeks { get; set; }

    [Column("rivalry_weeks")]
    public string? RivalryWeeks { get; set; }

    [Column("nut_cup_week")]
    public required int NutCupWeek { get; set; }

    [Column("avg_points")]
    public required double AvgPoints { get; set; }

    [Column("avg_moves")]
    public required double AvgMoves { get; set; }

    [Column("completed")]
    public required int Completed { get; set; }

    [Column("create_date")]
    public required DateTime CreateDate { get; set; }

    [Column("modify_date")]
    public required DateTime ModifyDate { get; set; }


    // 关联关系
    public virtual required League League { get; set; }
    public virtual required Season Season { get; set; }
    public virtual required ICollection<Team> Teams { get; set; }
}

[Table("seasons")]
public class Season
{
    [Key]
    [Column("season_id")]
    public required int Id { get; set; }

    [Column("yahoo_season_id")]
    public int? YahooSeasonId { get; set; }

    [Column("name")]
    public required string Name { get; set; }

    [Column("start_date")]
    public required DateOnly StartDate { get; set; }

    [Column("end_date")]
    public required DateOnly EndDate { get; set; }

    [Column("create_date")]
    public required DateTime CreateDate { get; set; }

    [Column("modify_date")]
    public required DateTime ModifyDate { get; set; }

    // 关联关系
    public virtual ICollection<LeagueSeason> LeagueSeasons { get; set; }
}

对应数据库表结构 #

CREATE TABLE `league_seasons` (
    `league_season_id` INT(10) NOT NULL AUTO_INCREMENT,
    `league_id` INT(10) NOT NULL,
    `season_id` INT(10) NOT NULL,
    `yahoo_league_season_id` VARCHAR(50) NULL DEFAULT NULL COMMENT 'Classified as their league id' COLLATE 'utf8mb3_general_ci',
    `num_teams` INT(10) NOT NULL,
    `num_playoff_teams` INT(10) NULL DEFAULT NULL,
    `num_weeks` INT(10) NOT NULL,
    `rivalry_weeks` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `nut_cup_week` INT(10) NULL DEFAULT NULL,
    `avg_points` DECIMAL(20,6) NULL DEFAULT NULL,
    `avg_moves` DECIMAL(20,6) NULL DEFAULT NULL,
    `completed` TINYINT(3) NOT NULL DEFAULT '0',
    `create_date` DATETIME NULL DEFAULT 'CURRENT_TIMESTAMP',
    `modify_date` DATETIME NULL DEFAULT 'CURRENT_TIMESTAMP',
    PRIMARY KEY (`league_season_id`) USING BTREE,
    UNIQUE INDEX `league_id_season_id` (`league_id`, `season_id`) USING BTREE,
    INDEX `league_id` (`league_id`) USING BTREE,
    INDEX `season_id` (`season_id`) USING BTREE,
    INDEX `yahoo_league_season_id` (`yahoo_league_season_id`) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB;

CREATE TABLE `seasons` (
    `season_id` INT(10) NOT NULL AUTO_INCREMENT,
    `yahoo_season_id` INT(10) NULL DEFAULT NULL,
    `name` YEAR NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `create_date` DATETIME NULL DEFAULT 'CURRENT_TIMESTAMP',
    `modify_date` DATETIME NULL DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE (CURRENT_TIMESTAMP),
    PRIMARY KEY (`season_id`) USING BTREE,
    UNIQUE INDEX `yahoo_season_id` (`yahoo_season_id`) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB;

报错代码与异常 #

调用以下方法时：

/// <summary>
/// 计算指定赛季的联盟实力排名，返回经理ID与对应排名分数的字典
/// </summary>
/// <param name="selectedLeague"></param>
/// <param name="year"></param>
/// <returns></returns>
public static Dictionary<int, double> CalculateLeaguePowerRankings(int selectedLeague, int? year = null)

执行这段代码时抛出异常：

int current_league_season_id = context.LeagueSeasons 
                .Include(ls => ls.Season)
                .Where(ls => ls.LeagueId == selectedLeague)
                .OrderByDescending(ls => ls.Season.Name)
                .Select(ls => ls.Id)
                .First();

Dictionary<int, double> power_rankings = Calculations.CalculateLeaguePowerRankings(selectedLeague, context.LeagueSeasons.Find(current_league_season_id).Season.Name);

异常信息：

System.InvalidCastException: 'Unable to cast object of type 'System.Int32' to type 'System.String'.'

尝试的排查操作 #

添加验证代码确认ID有效性，但问题依旧：

int current_season_id = context.LeagueSeasons
    .Include(ls => ls.Season)
    .Where(ls => ls.Id == current_league_season_id)
    .Select(ls => ls.SeasonId)
    .First();
    
Season current_season = context.Seasons.Find(current_season_id);

问题原因与解决方案 #

原因 #

数据库seasons表的name字段类型是YEAR（本质为整数类型，存储2024这类年份值），但C#实体Season的Name属性定义为string类型，EF Core读取数据时无法直接将整数类型的YEAR值转换为string，导致抛出类型转换异常。

解决方案 #

有三种可行修复方式：

1. 修改实体属性类型匹配数据库
   将Season类的Name属性类型改为int，与数据库YEAR类型对应：

   [Column("name")]
   public required int Name { get; set; }
   

   调用时若需字符串形式，手动转换：

   Calculations.CalculateLeaguePowerRankings(selectedLeague, current_season.Name.ToString());
   

2. 使用ValueConverter处理自动转换
   保持Name为string类型，通过EF Core的ValueConverter实现整数与字符串的自动转换：
   在DbContext的OnModelCreating中配置：

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
       modelBuilder.Entity<Season>()
           .Property(s => s.Name)
           .HasConversion(
               // 写入数据库时将string转为int
               v => int.Parse(v),
               // 从数据库读取时将int转为string
               v => v.ToString()
           );
   }
   

3. 查询时手动转换
   在获取Season.Name时直接转换为字符串，避免EF自动转换出错：

   var leagueSeason = context.LeagueSeasons 
       .Include(ls => ls.Season)
       .Where(ls => ls.LeagueId == selectedLeague)
       .OrderByDescending(ls => ls.Season.Name)
       .First();
   
   Dictionary<int, double> power_rankings = Calculations.CalculateLeaguePowerRankings(selectedLeague, leagueSeason.Season.Name.ToString());
   

内容的提问来源于stack exchange，提问作者Blake Wewer