Entity Framework Core: Transaction between reading and deleting data

.net-core c# entity-framework-core

Question

I am facing a problem with Entity Framework Core in an .NET Core REST API.

Example Data Model:

Table House (id, name) Table Rooms (id, houseId, name)

A house can have multiple rooms and a room can only be in one house.

My problem occurs when I delete a room. Every time the user makes a request to delete a room I check if the room was the last in the house and if it was the last I delete the (empty) house too.

  Room room = dbContext.Room
    .Include(r => r.House).ThenInclude(h => h.Rooms)
    .Where(r => r.id == id)
    .SingleOrDefaultAsync();

  if (room.House.Rooms.Count > 1) {
    // other rooms are present
    dbContext.Room.Remove(room);
  } else {
    // delete house if it is the last room
    dbContext.House.Remove(room.House);
  }

  dbContext.SaveChanges();

The problem is that our UI does not delete a house in one call, it calls delete room for each room in the house (and expects that the house is deleted automatically with the last room). This causes the problem. The calls come short after each other that every room loads the house entity, checks if it is the last room, recognizes other rooms, the room gets deleted, the empty house remains.

Is this anyhow possible to solve (so the empty house does not remain) e.g. with transactions?

1
0
9/12/2018 12:11:29 PM

Accepted Answer

Yes and no. You can wrap it into a transaction - which is trivial.

https://docs.microsoft.com/en-us/ef/core/saving/transactions

show how.

You basically just create a transaction:

using (var transaction = context.Database.BeginTransaction())

Alternatively you can use a DbTransaction on a DbConnection. Same link.

But your code is simply badly organized.

every room loads the house entity, checks if it is the last room, recognizes other rooms, the room gets deleted, the empty house remains.

Sounds like someone abstracted the db totally away behind some of those nice repository antipattern - and THAT (not the transaction) now comes back to bite you. It is likely every deleted room uses a separate repository - and boom, transactions do not help.

Refactoring time.

1
9/12/2018 12:19:28 PM

Popular Answer

The best solution would probably be to create a new separate method of deleting the house that takes care of the whole thing in one call.

If there is really some issue that prevents you from accomplishing the DeleteHouse method, you could solve your issue through locking. I think your fundamental issue is a threading race condition. Add a lock to your delete room code to solve that issue.

lock(_deleteRoomLock) 
{ \\Your delete room code }

The disadvantage of this pattern is that you would end up creating quite a bottleneck in your server side code around deleting rooms.

Link to documentation on locking https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/lock-statement



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow