Entity Framework Core not updating its cache after object is updated in the database

Question

I have an ASP.NET Core Web API project that has a Category object that has a parent child relationship with itself.

I have the following parent child relationship created:

"Pets" -> "Pet Food" -> "Dog Food" -> "Ugly Dog Food"

I want to delete "Dog Food" but first I programmatically make "Pet Food" the parent category of "Ugly Dog Food" so that "Ugly Dog Food" is not orphaned.

I do the update successfully which then triggers the Web API call for the deletion of "Dog Food".

When I attempt to do so I get the following error:

DETAIL: Key (Id)=(0c6b1a97-d035-40ac-aa1f-ceef7144a236) is still referenced from table "Category".

at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

Exception data:
Severity: ERROR
SqlState: 23503
MessageText: update or delete on table "Category" violates foreign key constraint "FK_Category_Category_ParentId" on table "Category"
Detail: Key (Id)=(0c6b1a97-d035-40ac-aa1f-ceef7144a236) is still referenced from table "Category".

But I check the database and "Ugly Dog Food" has been updated to have the ParentId of "Pet Food" and no other category entry in the table has the id of "Dog Food" as their ParentId so this must be Entity Framework Core's cache.

Why is the cache not updated when I update the parentId of "Ugly Dog Food"?

Here is my CategoryService class. For the update I call the UpdateCategory method here with "Ugly Dog Food" and then after I get my successful update returned I initiate the DeleteCategory method on "Dog Food":

using ShopBack.Database.Repositories;
using ShopBack.Database;

namespace ShopBack.Services;

public class CategoryService
{
    private readonly ICategoryRepository _categoryRepository;
    private readonly IShopUnitOfWork _unitOfWork;

    public CategoryService(IShopUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
        _categoryRepository = unitOfWork.Categories;
    }

    /// <summary>
    /// Call to create category in the CategoryRepository
    /// </summary>
    /// <param name="category">The category entity</param>
    /// <returns>void</returns>
    public async Task CreateCategory(Category category)
    {
        await _categoryRepository.AddAsync(category);
        await _unitOfWork.SaveAsync();
    }

    /// <summary>
    /// Call to get all categories in the CategoryRepository
    /// </summary>
    /// <returns>void</returns>
    public async Task<IEnumerable<Category>> GetAllCategories()
    {
        return await _categoryRepository.GetAllAsync();
    }

    /// <summary>
    /// Call to get all categories in the CategoryRepository
    /// </summary>
    /// <returns>void</returns>
    public async Task<IEnumerable<Category>> GetAllTopCategories()
    {
        return await _categoryRepository.getAllTopLevelCategories();
    }

    /// <summary>
    /// Call to update Category in the CategoryRepository
    /// </summary>
    /// <param name="Category">The Category entity</param>
    /// <returns>void</returns>
    public async Task UpdateCategory(Category category)
    {
        _categoryRepository.Update(category);
        await _unitOfWork.SaveAsync();
    }

    /// <summary>
    /// Call to get a single Category in the CategoryRepository
    /// </summary>
    /// <param name="categoryId">The Category Id</param>
    /// <returns>Category</returns>
    public async Task<Category?> GetCategory(Guid categoryId)
    {
        return await _categoryRepository.GetByIdAsync(categoryId);
    }

    /// <summary>
    /// Call to delete Category in the CategoryRepository
    /// </summary>
    /// <param name="category">The Category entity</param>
    /// <returns>void</returns>
    public async Task DeleteCategory(Category category)
    {
        //todo what if the category has already been deleted? What happens?

        _categoryRepository.Delete(category);
        await _unitOfWork.SaveAsync();
    }
}

Here is my BaseRepository class:

using Microsoft.EntityFrameworkCore;

namespace ShopBack.Database.Repositories
{
    public class BaseRepository<T> : IRepositoryBase<T> where T : ModelBase
    {
        protected readonly LibraryContext _context;

        public BaseRepository(LibraryContext context)
        {
            //context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
            _context = context;
        }

        /// <summary>
        /// Gets all entities for this particular repository
        /// </summary>
        public virtual async Task<IEnumerable<T>> GetAllAsync()
        {
            return await _context.Set<T>().ToListAsync();
        }

        /// <summary>
        /// Gets an entity by Id for this particular repository
        /// </summary>
        public virtual async Task<T?> GetByIdAsync(Guid id)
        {
            return await _context.Set<T>().FindAsync(id);
        }

        /// <summary>
        /// Adds an an entity by for this particular repository
        /// </summary>
        public virtual async Task AddAsync(T entity)
        {
            await _context.Set<T>().AddAsync(entity);
        }

        /// <summary>
        /// Updates an entity by for this particular repository
        /// </summary>
        public virtual void Update(T entity)
        {
            _context.Entry(entity).State = EntityState.Modified;
        }

        /// <summary>
        /// Deletes an entity for this particular repository
        /// </summary>
        public virtual void Delete(T entity)
        {
            _context.Set<T>().Remove(entity);
        }

        /// <summary>
        /// Checks to see if an entity exists for this particular repository
        /// </summary>
        public virtual async Task<bool> ExistsAsync(Guid id)
        {
            return await _context.Set<T>().AnyAsync(x => x.Id == id);
        }
    }
}

Here is my UnitOfWork class:

using ShopBack.Database.Repositories;

namespace ShopBack.Database
{
    public class UnitOfWork : IUnitOfWork
    {
        private readonly LibraryContext _context;
        private Dictionary<Type, object> _repositories;

        /// <summary>
        ///Constructor for our UnitOfWork class
        /// </summary>
        public UnitOfWork(LibraryContext context)
        {
            _context = context;
            _repositories = new Dictionary<Type, object>();
        }

        public void Dispose()
        {
            _context.Dispose();
        }

        TRepository IUnitOfWork.GetRepository<TRepository, TEntity>()
        {
            var type = typeof(TEntity);

            if (_repositories.ContainsKey(type))
            {
                return (TRepository)_repositories[type];
            }
            else
            {
                var repositoryType = typeof(TRepository);
                var repGenType = repositoryType.MakeGenericType(typeof(TEntity));
                var repositoryInstance = Activator.CreateInstance(repGenType, _context);

                _repositories.Add(type, repositoryInstance);
            }

            return (TRepository)_repositories[type];
        }

        /// <summary>
        /// Persists our changes
        /// </summary>
        public async Task SaveAsync()
        {
            await _context.SaveChangesAsync();
        }
    }
}

Shouldn't the Entity Framework Core's cache have been updated when I did my update because I set its state to modified then ran SaveChangesAsync on UnitOfWork?

Answer 1

I strongly recommend ditching the Generic Repository. This is an anti-pattern with EF as EF already provides this in the DbSet and DbContext respectively. Having a repository wrapper that returns IEnumerable<T> means that every query is materializing the entire table to memory with every call, which is extremely wasteful and slow. Instead, work with EF's IQueryable through the DbSet and leverage everything EF provides rather than slapping an abstraction that will cripple performance over-top of it.

EF already manages FK relationships automatically so moving a category is as simple as:

var categoryToDelete = _context.Categories
    .Include(x => x.Parent)
    .Include(x => x.Children)
    .Single(x => x.CategoryId == categoryId);

foreach (var child in categoryToDelete.Children)
    categoryToDelete.Parent.Children.Add(child);

categoryToDelete.Parent.Children.Remove(categoryToDelete);

_context.SaveChanges();
_context.Entry(categoryToDelete.Parent).State = EntityState.Detached;

The step to detach the parent at the end is simply because in this example when we loaded the parent with the item to be removed we didn't load the complete list of children for the parent. That parent's children collection would just contain the items we moved from the deleted item, not any other existing child categories. If you later went to fetch the Parent from the DbContext such as to send to the view then this incomplete parent might get served.

If you want to do something with the parent and have a complete picture of the parent's state:

var categoryToDelete = _context.Categories
    .Include(x => x.Children)
    .Single(x => x.CategoryId == categoryId);
var parent = _context.Categories
    .Include(x => x.Children)
    .Single(x => x.CategoryId == categoryToDelete.ParentId);

foreach (var child in categoryToDelete.Children)
    parent.Children.Add(child);

parent.Children.Remove(categoryToDelete);
_context.SaveChanges();

This ensures the parent's complete children collection is populated.