How to Limit One To Many Relationship Count In Entitiy Framework Core

Question

I have Team and Player tables. A team can have maximum of 6 players. This implemented in business logic. But if concurrent requests occurs teams might ended up with 7 players. What is correct way to avoid this in ef core.

Changing concurrency model only for this feels like overkill.

HasCheckConstraint promising but requires writing raw sql in efcore not feels like idiomatic. Also not sure about correct syntax. This gives

42703: column "Players" does not exist error.

modelBuilder.Entity<Team>()
    .HasCheckConstraint("CK_Team_MaxPlayers", $"COUNT(\"Players\") <= 6");

Another solution implement transaction for every player add. But not sure about correct method.

await using var transaction =
    await context.Database.BeginTransactionAsync();

try
{
    var newPlayer = new TeamPlayer
    {
        UserId = user.Id,
    };
    
    team.Players.Add(newPlayer);
    await transaction.CommitAsync();
    
    //then what? check member count and rollback?
    //can i rollback committed transaction?
}
catch (Exception)
{
    // TODO: Handle failure
}