Entity Framework Core returning entities that has been marked for deletion

Question

First time posting around here. When dealing with EF Core in my current project, I saw something funny on a specific scenario I have to implement.

The main deal goes as the following:

1. Query a set of entities via a specific criteria
2. Delete those entities
3. Go to parent entity to see if we can delete it because it has no children

The issue I've found is that I have to perform that logic before calling SaveChanges(), so when I queried the parent entity, the child entities that were flagged for deletion are still within the reach of the parent entity, and as a result of not being detected as empty it can't be deleted.

So moving to the second scenario, I decided to try this only with the child entities.

So if I query a set of entities, I delete them and I try to retrieve it again before SaveChanges, I get the same list. Is there a way to avoid this behavior? Is it normal? What are my options? So far we can't use the TransactionScope (mostly due to design issues) or EF's transaction.

Here is a bit of a sample code I made that reproduces this behavior.

// Test class
namespace EfCoreTest
{
    [Table("MyEntity")]
    public class MyEntity
    {
        [Required]
        public Guid Id { get; set; }
    }
}

// DB Context
namespace EfCoreTest
{
    public class EfCoreTestDbContext : DbContext
    {
        public static string SQL_CONNECTION_STRING = @"<YOUR_DB_STRING_HERE>";
        
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(SQL_CONNECTION_STRING);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Seed();
        }

        public DbSet<MyEntity> MyEntities { get; set; }
    }
}

// Program.cs, it's a net core 3.1 console application
namespace EfCoreTest
{
    public static class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EfCoreTestDbContext()) 
            {
                context.Set<MyEntity>().AddRange(new[]
                {
                    new MyEntity { Id = Guid.NewGuid() },
                    new MyEntity { Id = Guid.NewGuid() }
                });

                var entitiesBeforeConfirmation = context.Set<MyEntity>().ToList();
                context.SaveChanges();
                var entitiesAfterConfirmation = context.Set<MyEntity>().ToList();

                Console.WriteLine($"Entities retrieved before SaveChanges: { entitiesBeforeConfirmation.Count }"); // 0
                Console.WriteLine($"Entities retrieved after SaveChanges: { entitiesAfterConfirmation.Count }"); // 2

                var entities = context.Set<MyEntity>().ToList();
                context.Set<MyEntity>().RemoveRange(entities);
                var entitiesAgain = context.Set<MyEntity>().ToList();

                Console.WriteLine($"Entities retrieved: { entities.Count }"); // 2
                Console.WriteLine($"Entities retrieved after deletion without SaveChanges: { entitiesAgain.Count }"); // 2

                context.SaveChanges();

                var entitiesAgainAfterConfirmation = context.Set<MyEntity>().ToList();
                Console.WriteLine($"Entities retrieved after SaveChanges: { entitiesAgainAfterConfirmation.Count }"); // 0
            }

            Console.WriteLine("Press any key to continue...");
            Console.ReadKey();
        }
    }
}

Answer 1

You have a few options:

1. Call SaveChanges in-between your deletions. "Queries" in EF are sent to the DB and not checked against the currently tracked entities, for good reason: you can never be sure that another process have not changed data in the DB which means the change tracker will never give you the full picture.
2. Check the change tracker yourself as well as querying the database and combine the results.
3. Load all rows from the table initially so you have the full structure of parents/children in one query.
   • Why? If you remove a parent because all children are removed, then you need to check the grandparent and so on...
   • You probably don't want to load the full entity if you do this so you can do something like context.MyEntity.Select(me => new { me.Id, me.ParentId }) to only include those columns/properties. But still, if you have a very large table, this is probably still problematic.
   • Do note, that this solution is not safe from race conditions: if a separate thread/process updates the database in the meantime (e.g. adding a child to another row) you may end up removing (or forgetting to) something inadvertently.
4. Database trigger.
   • Instead of implementing the logic about "cascading" deletes of parents with no children in the application, you can implement the logic in a database trigger. This means, when you delete the initial list of children with a call to SaveChanges the database trigger will recursively check if a parent should also be deleted

I believe option 4 will be the most performant if that's you focus.