1

I'm trying to remove an object and all its related objects without retrieving the whole entity from the database first using Entity Framework 6.2.0.

The object is a team. A team can have many users as well as many players. The relation to a single player is m:n and realised through an intermediate table called tblPlayerInTeam. So I want to remove the intermediate entry but not the tblPlayer-entry.

This is what my code currently looks like:

var teamIds = new[] { new Guid("2007d05f-d61d-428b-b21f-0e8a9ff660a2") };

foreach (var teamId in teamIds)
{
    var teamToDelete = new tblTeam { TeamId = teamId };

    // mark the team as Deleted
    dbContext.Entry(teamToDelete).State = System.Data.Entity.EntityState.Deleted;

    // get the primary keys of tblPlayerInTeam
    var teamPlayerIds = dbContext.tblPlayerInTeam.Where(pit => pit.PlayerInTeamTeamId == teamId).Select(pit => pit.PlayerInTeamId);
    foreach (var teamPlayerId in teamPlayerIds)
    {
        var playerToDelete = new tblPlayerInTeam { PlayerInTeamId = teamPlayerId };

        // mark the playerInTeam as Deleted
        dbContext.Entry(playerToDelete).State = System.Data.Entity.EntityState.Deleted;
    }

    // get the primary keys of tblTeamUser
    var teamUserIds = dbContext.tblTeamUser.Where(tu => tu.TeamUserTeamId == teamId).Select(tu => tu.TeamUserId);
    foreach (var teamUserId in teamUserIds)
    {
        var userToDelete = new tblTeamUser { TeamUserId = teamUserId };

        // mark the teamUser as Deleted
        dbContext.Entry(userToDelete).State = System.Data.Entity.EntityState.Deleted;
    }
}

SaveChanges(); // <-- here I get the error

The DELETE statement conflicted with the REFERENCE constraint "FK_tblTeamUser_tblTeam".

From the generated SQL I can tell, that the tblPlayerInTeam entries are going to be deleted but tblTeamUser-entries are completetly ignored. If I only run the last part (for the team-users) the SQL generated is fine. I don't see why it's ignored otherwise. Same when I remove the playerInTeam part. It only generates the DELETE-statement for the team but not for the team-users.

I already tried to change the order so the team would be the last one to be removed (marked as Deleted). But still the same error.

KingKerosin
  • 3,639
  • 4
  • 38
  • 77
  • Are you looking for Cascade delete? https://stackoverflow.com/a/34038321/2946329 – Salah Akbari Jan 31 '18 at 10:37
  • @S.Akbari That's not what the OP asked. He asked how to remove __only the children__ without removing the parent. – Balázs Jan 31 '18 at 10:38
  • @S.Akbari The edmx is created from the database and `ON CASCADE DELETE` is not an option for me at all. – KingKerosin Jan 31 '18 at 10:39
  • @Balázs No. I also want to remove the parent. See `dbContext.Entry(teamToDelete).State = System.Data.Entity.EntityState.Deleted;` – KingKerosin Jan 31 '18 at 10:40
  • @KingKerosin You can do this in the database also Check here https://stackoverflow.com/a/37459049/2946329 – Salah Akbari Jan 31 '18 at 10:41
  • @S.Akbari This is not an option for me. The code above is only an example to show my issue. The real-life application is more complex than that but currently this specific part is stopping me from going further – KingKerosin Jan 31 '18 at 10:43
  • @KingKerosin Oh. Then I've misunderstood you. – Balázs Jan 31 '18 at 10:45
  • 1
    If you want EF to do this you have no other option than to retrieve the team-player and team-user records from the database. How else would EF know the player/user IDs for which it should build DELETE statements? Another option is to execute SQL statements from your code, or use EntityFramework.Extended (NuGet). – Gert Arnold Jan 31 '18 at 11:02
  • @GertArnold `var teamPlayerIds = dbContext.tblPlayerInTeam.Where(pit => pit.PlayerInTeamTeamId == teamId).Select(pit => pit.PlayerInTeamId)`. These are the primary keys for `tblPlayerInTeam` – KingKerosin Jan 31 '18 at 11:03
  • this might help: https://visualstudiomagazine.com/blogs/tool-tracker/2015/12/entity-framework-database-deletes.aspx – Amr Elgarhy Jan 31 '18 at 11:07
  • @AmrElgarhy Already tried this. Same issue – KingKerosin Jan 31 '18 at 11:17
  • The problem is that EF doesn't know the relationships between `Team` and the junction records, so it's not able to determine a valid ordering of the DELETE statements. Either retrieve them from the database, or build a Team stub entity, fill its teamuser/player collections with stubs and *then* mark the team as deleted. – Gert Arnold Jan 31 '18 at 12:17

0 Answers0