DbUpdateException while seeding initial data
I'm trying to make a project for a holiday application to pass a course in college. Using the latest versions of .net (8) and entity framework, I have already created entities, enums, basic interfaces, etc. In the controller so far there is only the create method for the person class. But I have a problem with data seeding. Below are code snippets from dbcontext, as well as data seeding classes and a screenshot from the database. I tried doing different variations with seeding, but it always throws an error somewhere.
Error message:
Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while saving the entity changes. See the inner exception for details. SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_People_Teams_Id". The conflict occurred in database "HolidayAppDb", table "dbo.Teams", column 'TeamID'.
Person seeder, the problem occurs when saving first 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 = "[email protected]", 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 = "[email protected]", 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 seeder:
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 = "[email protected]", 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 = "[email protected]", PhoneNumber = "+48555456675", PostalCode = "00-100" },
HolidayTimeRemain = 14,
TeamId = secondTeam.TeamID
};
_dbContext.Teams.Update(firstTeam);
_dbContext.Teams.Update(secondTeam);
await _dbContext.SaveChangesAsync();
}
}
}
dbcontext class:
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 table
Team table
PS: Also why I'm missing the team leader in team table having a reference to this? Any ideas? Am I missing something?
You are setting the TeamLeader property for the teams, but you are not setting the TeamLeaderId property for the teams would be my guess.
The standard way of doing this is by having a TeamLeaderId Guid property and a TeamLeader Object property which is virtual most of the time. In the standard way, if you set only the Object property nothing happens because there is no field for it in the database.
Set the TeamLeaderId property to the Id of the Person. But if you have auto generated Ids, then you need to create the persons first, call save changes and get them from the database, after which you can update the team leaders of the teams.