EF Core 8创建Room时误创关联Player引发MySQL报错求助

使用EF Core 8创建Room实体时出现以下错误：

Microsoft.EntityFrameworkCore.DbUpdateException: 保存实体变更时发生错误，请查看内部异常详情。
MySqlConnector.MySqlException (0x80004005): Field 'password' doesn't have a default value

经排查，问题出在Room关联Player实体时，EF Core自动尝试创建新的Player记录，但实际需求是仅创建Room并关联已存在的Player，而非新建Player。

执行的SQL语句 #

INSERT INTO `players` (`id`, `created_at`, `email`, `username`)
VALUES (@p0, @p1, @p2, @p3);
INSERT INTO `player_avatar_data` (`id`, `chat_bubble_id`, `figure_code`, `gender`, `motto`, `player_id`)
VALUES (@p4, @p5, @p6, @p7, @p8, @p9);
INSERT INTO `player_data` (`id`, `achievement_score`, `allow_friend_requests`, `credit_balance`, `gotw_points`, `home_room_id`, `is_online`, `last_online`, `pixel_balance`, `player_id`, `respect_points`, `respect_points_pet`, `seasonal_balance`)
VALUES (@p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22);
INSERT INTO `player_game_settings` (`id`, `block_camera_follow`, `block_room_invites`, `furniture_volume`, `player_id`, `prefer_old_chat`, `show_notifications`, `system_volume`, `trax_volume`, `ui_flags`)
VALUES (@p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32);
INSERT INTO `player_navigator_settings` (`id`, `open_searches`, `player_id`, `window_height`, `window_width`, `window_x`, `window_y`)
VALUES (@p33, @p34, @p35, @p36, @p37, @p38, @p39);
INSERT INTO `rooms` (`created_at`, `description`, `is_muted`, `layout_id`, `max_users_allowed`, `name`, `owner_id`)
VALUES (@p40, @p41, @p42, @p43, @p44, @p45, @p46);
SELECT `id`
FROM `rooms`
WHERE ROW_COUNT() = 1 AND `id` = 449989;

INSERT INTO `rooms` (`id`, `created_at`, `description`, `is_muted`, `layout_id`, `max_users_allowed`, `name`, `owner_id`)
VALUES (@p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54),
(@p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62);
INSERT INTO `room_settings` (`id`, `access_type`, `allow_pets`, `can_pets_eat`, `can_users_overlap`, `floor_thickness`, `hide_walls`, `password`, `room_id`, `trade_option`, `walk_diagonal`, `wall_thickness`, `who_can_ban`, `who_can_kick`, `who_can_mute`)
VALUES (@p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77),
(@p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92);

Room实体定义 #

public class Room
{
    public int Id { get; init; }
    public string? Name { get; set; }
    public int LayoutId { get; init; }
    public RoomLayout? Layout { get; init; }
    public int OwnerId { get; init; }
    public Player? Owner { get; init; }
    public int MaxUsersAllowed { get; set; }
    public string? Description { get; set; }
    public bool IsMuted { get; init; }
    public RoomSettings? Settings { get; set; }
    public RoomPaintSettings? PaintSettings { get; set; }
    public RoomChatSettings? ChatSettings { get; set; }
    public DateTime CreatedAt { get; init; }
}

创建Room的代码 #

var newRoom = new Room
{
    Name = eventParser.Name,
    LayoutId = layout.Id,
    Layout = layout,
    OwnerId = client.Player.Id,
    Owner = client.Player,
    MaxUsersAllowed = 50,
    Description = eventParser.Description,
    CreatedAt = DateTime.Now
};

newRoom.Settings = new RoomSettings
{
    RoomId = newRoom.Id,
    Password = ""
};

newRoom.ChatSettings = new RoomChatSettings
{
    RoomId = newRoom.Id
};

newRoom.PaintSettings = new RoomPaintSettings
{
    RoomId = newRoom.Id,
};

dbContext.Rooms.Add(newRoom);
roomRepository.AddRoom(newRoom);
client.Player.Rooms.Add(newRoom);

await dbContext.SaveChangesAsync();

可以通过以下几种方式实现创建Room时仅关联已存在的Player，避免EF Core自动新建Player：

1. 仅设置外键ID，不赋值导航属性 #

EF Core会自动通过外键OwnerId关联已存在的Player，无需赋值Owner导航属性。修改创建Room的代码：

var newRoom = new Room
{
    Name = eventParser.Name,
    LayoutId = layout.Id,
    Layout = layout,
    OwnerId = client.Player.Id, // 只保留外键ID
    // 移除 Owner = client.Player;
    MaxUsersAllowed = 50,
    Description = eventParser.Description,
    CreatedAt = DateTime.Now
};

2. 将Player实体附加到上下文（标记为已存在） #

如果必须保留Owner导航属性的赋值，需要将client.Player附加到当前DbContext，告知EF Core该实体已存在于数据库中，无需创建新记录：

// 在创建Room前执行
dbContext.Attach(client.Player);

var newRoom = new Room
{
    // ... 其他属性
    OwnerId = client.Player.Id,
    Owner = client.Player
};

// ... 后续代码不变

3. 配置EF Core关系为无外键级联创建 #

在DbContext的OnModelCreating方法中，显式配置Room与Player的关系，设置不级联创建关联实体：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Room>()
        .HasOne(r => r.Owner)
        .WithMany(p => p.Rooms) // 假设Player有Rooms集合导航属性
        .HasForeignKey(r => r.OwnerId)
        .OnDelete(DeleteBehavior.Restrict) // 可选，根据需求设置删除行为
        .IsRequired()
        .WillCascadeOnDelete(false); // 禁用级联创建
}

4. 检查Repository的AddRoom方法 #

确保roomRepository.AddRoom方法没有额外将Owner标记为需要新增的实体，避免干扰EF Core的实体跟踪状态。

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