.NET 8 EF数据种子填充遇DbUpdateException外键冲突及TeamLeader字段缺失问题

问题背景 #

基于.NET 8和Entity Framework Core开发大学课程考核用的假期申请系统，已完成实体、枚举、基础接口定义，控制器仅实现了Person类的Create方法。在数据种子填充环节遇到两个问题：

1. 插入第一个Person时触发DbUpdateException，提示违反外键约束FK_People_Teams_Id
2. Team表中未生成TeamLeader的引用字段

错误信息 #

Microsoft.EntityFrameworkCore.DbUpdateException: '保存实体变更时出错，请查看内部异常详情。
SqlException: INSERT语句与外键约束"FK_People_Teams_Id"冲突。冲突发生在数据库"HolidayAppDb"，表"dbo.Teams"，列'TeamID'。

代码片段 #

Person种子类 #

public async Task Seed()
{
    if (await _dbContext.Database.CanConnectAsync())
    {
        if (!_dbContext.HolidayRequests.Any())
        {
            var teamExists = await _dbContext.Teams.AnyAsync();
            if (!teamExists)
            {
                throw new Exception("The team does not exist.");
            }

            var approvedByFirstLeader = _dbContext.People.OrderBy(p => p.Id).First();
            var approvedBySecondLeader = _dbContext.People.OrderByDescending(p => p.Id).First();

            var firstPerson = new Person()
            {
                Id = Guid.NewGuid(),
                FirstName = "Jacek",
                LastName = "Magiera",
                DateOfBirth = new DateTime(1980, 1, 1),
                Position = "Developer",
                StartDate = new DateTime(2020, 1, 1),
                IsFullTime = true,
                ContactDetails = new()
                { City = "Warsaw", Street = "Miodowa", Email = "exampleemail@gmail.com", PhoneNumber = "+48878453875", PostalCode = "42-290" },
                HolidayTimeRemain = 10,
                TeamId = _dbContext.Teams.OrderBy(t => t.TeamID).First().TeamID,
                Team = _dbContext.Teams.OrderBy(t => t.TeamID).First()
            };

            _dbContext.People.Add(firstPerson);
            await _dbContext.SaveChangesAsync();

            firstPerson.HolidayRequests = new List<HolidayRequest>()
                    {
                        new HolidayRequest()
                        {
                            Description = "Winter Vacation",
                            CreatedAt = new DateTime(2022, 1, 1),
                            StartDate = new DateTime(2022, 1, 10),
                            EndDate = new DateTime(2022, 1, 20),
                            Status = HolidayRequestStatusEnum.Completed,
                            Type = HolidayTypeEnum.PaidLeave,
                            IsApproved = true,
                            ApprovedDate = new DateTime(2021, 12, 1),
                            ApprovedBy = approvedByFirstLeader
                        }
                    };

            _dbContext.HolidayRequests.Add(firstPerson.HolidayRequests.First());

            var secondPerson = new Person()
            {
                Id = Guid.NewGuid(),
                FirstName = "Adam",
                LastName = "Nowak",
                DateOfBirth = new DateTime(1985, 1, 1),
                Position = "Developer",
                StartDate = new DateTime(2021, 1, 1),
                IsFullTime = true,
                ContactDetails = new()
                { City = "Krakow", Street = "Zielona", Email = "exampleemail2@gmail.com", PhoneNumber = "+48888453875", PostalCode = "30-300" },
                HolidayTimeRemain = 15,
                TeamId = _dbContext.Teams.OrderByDescending(t => t.TeamID).First().TeamID,
                Team = _dbContext.Teams.OrderByDescending(t => t.TeamID).First()
            };

            _dbContext.People.Add(secondPerson);
            await _dbContext.SaveChangesAsync();

            secondPerson.HolidayRequests = new List<HolidayRequest>()
                    {
                        new HolidayRequest()
                        {
                            Description = "Summer Vacation",
                            CreatedAt = new DateTime(2022, 1, 1),
                            StartDate = new DateTime(2022, 7, 10),
                            EndDate = new DateTime(2022, 7, 20),
                            Status = HolidayRequestStatusEnum.Completed,
                            Type = HolidayTypeEnum.PaidLeave,
                            IsApproved = true,
                            ApprovedDate = new DateTime(2021, 12, 1),
                            ApprovedBy = approvedBySecondLeader
                        }
                    };

            _dbContext.HolidayRequests.Add(secondPerson.HolidayRequests.First());
            await _dbContext.SaveChangesAsync();
        }
    }
}

Team种子类 #

public async Task Seed()
{
    if (await _dbContext.Database.CanConnectAsync())
    {
        if (!_dbContext.Teams.Any())
        {
            var firstTeam = new Team() { Name = "Team 1", Description = "First team", IsActive = true, Department = "IT" };
            var secondTeam = new Team() { Name = "Team 2", Description = "Second team", IsActive = true, Department = "IT" };

            _dbContext.Teams.Add(firstTeam);
            _dbContext.Teams.Add(secondTeam);
            await _dbContext.SaveChangesAsync();

            firstTeam.TeamLeader = new Person()
            {
                FirstName = "Jan",
                LastName = "Kowalski",
                DateOfBirth = new DateTime(1975, 1, 1),
                Position = "Team Leader",
                StartDate = new DateTime(2019, 1, 1),
                IsFullTime = true,
                ContactDetails = new()
                { City = "Czestochowa", Street = "Miodowa", Email = "exampleemail1@gmail.com", PhoneNumber = "+48522456675", PostalCode = "42-200" },
                HolidayTimeRemain = 5,
                TeamId = firstTeam.TeamID
            };

            secondTeam.TeamLeader = new Person()
            {
                FirstName = "Anna",
                LastName = "Kowalska",
                DateOfBirth = new DateTime(1980, 1, 1),
                Position = "Team Leader",
                StartDate = new DateTime(2020, 1, 1),
                IsFullTime = true,
                ContactDetails = new()
                { City = "Warsaw", Street = "Czerwona", Email = "exampleemail3@gmail.com", PhoneNumber = "+48555456675", PostalCode = "00-100" },
                HolidayTimeRemain = 14,
                TeamId = secondTeam.TeamID
            };

            _dbContext.Teams.Update(firstTeam);
            _dbContext.Teams.Update(secondTeam);
            await _dbContext.SaveChangesAsync();
        }
    }
}

DbContext类 #

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

public DbSet<Person> People { get; set; }
public DbSet<HolidayRequest> HolidayRequests { get; set; }
public DbSet<Team> Teams { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    #region Person
    modelBuilder.Entity<Person>(eb =>
    {
        eb.Property(p => p.Id).ValueGeneratedOnAdd();
        eb.Property(p => p.FirstName).IsRequired().HasMaxLength(50);
        eb.Property(p => p.LastName).IsRequired().HasMaxLength(50);
        eb.Property(p => p.HolidayTimeRemain).IsRequired();
        eb.Property(p => p.DateOfBirth).IsRequired();
        eb.Property(p => p.Position).IsRequired().HasMaxLength(50);
        eb.Property(p => p.StartDate).IsRequired();
        eb.Property(p => p.IsFullTime).IsRequired();
        eb.HasOne(p => p.Team).WithMany(t => t.Persons).HasForeignKey(p => p.TeamId).OnDelete(DeleteBehavior.Restrict).IsRequired();
        eb.HasMany(h => h.HolidayRequests).WithOne(p => p.Person).HasForeignKey(p => p.PersonId);
        eb.OwnsOne(c => c.ContactDetails);
    });
    #endregion

    #region HolidayRequest
    modelBuilder.Entity<HolidayRequest>(eb =>
    {
        eb.Property(hr => hr.Description).IsRequired().HasMaxLength(1000);
        eb.Property(hr => hr.StartDate).IsRequired();
        eb.Property(hr => hr.EndDate).IsRequired();
        eb.Property(hr => hr.Status).IsRequired();
        eb.Property(hr => hr.Type).IsRequired();
        eb.Property(hr => hr.IsApproved).IsRequired();
        eb.Property(hr => hr.CreatedAt).IsRequired();
        eb.Property(hr => hr.UpdatedAt).IsRequired();
        eb.HasOne(hr => hr.ApprovedBy).WithMany().HasForeignKey(hr => hr.ApprovedById);
    });
    #endregion

    #region Team
    modelBuilder.Entity<Team>(eb =>
    {
        eb.Property(t => t.Name).IsRequired().HasMaxLength(50);
        eb.Property(t => t.Description).IsRequired().HasMaxLength(1000);
        eb.Property(t => t.IsActive).IsRequired();
        eb.Property(t => t.Department).IsRequired().HasMaxLength(50);
        eb.Property(t => t.CreatedDate).IsRequired();
        eb.Property(t => t.ModifiedDate);
        eb.HasOne(t => t.TeamLeader).WithOne().HasForeignKey<Person>(p => p.Id).IsRequired();
        eb.HasMany(t => t.Persons).WithOne(p => p.Team).HasForeignKey(p => p.TeamId).IsRequired();
    });
    #endregion
}

数据库表截图 #

Person表 #

Team表 #

疑问 #

1. 插入Person时触发外键约束错误的原因是什么？
2. Team表中未生成TeamLeader的引用字段，是否是EF Core配置有误？

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