Entity Framework Core: Global query filter not filtering correctly after data change

Question

Entity Framework Core: Global query filter not filtering correctly after data change

entity-framework-core

Question

We implemented a soft deletion pattern with Entity Framework Core, very similar to this solution, but with a nullable column "DeleteDate" instead of an IsDeleted flag. If the date is filled, the row is considered deleted and filtered out:

modelBuilder.Entity<TEntity>().HasQueryFilter(e => !EF.Property<DateTime?>(e, "SysDeletedOn").HasValue);

The following code bit would update one item while doing so, delete one of its child elements, but in the returned item, the deleted child would still be contained. Only a new query of all items would filter the children collection correctly.

public async Task<Item> UpdateItemAsync(ItemDto itemDto)
{
    var itemEntity = await _context.Items.SingleOrDefaultAsync(i => i.Id == itemDto.Id);

    if(itemEntity != null)
    {
        return;
    }

    // Update item's properties here
    // ...

    foreach (var child in itemDto.Children)
    {
        // Update child here
        // ...
    }

    foreach (var child in itemDto.RemovedChildren)
    {
        var childEntity = await itemEntity.SingleOrDefaultAsync(i => i.Id == child.Id);
        _context.Remove(childEntity);
    }

    await _context.SaveChangesAsync();

    // Read and return updated item
    return await _context.Items.SingleOrDefaultAsync(i => i.Id == itemDto.Id);
}

The question is now: How can I get the correct list in this method after this change (deletion) has been made? Do I have to clear the cache somehow between SaveChangesAsync and reading the context again?

The regular updates (property values) of the item are returned correctly. Also, it works when I query the items with .AsNoTracking() which also turns off caching, obviously.

gridr

View more on Stack Overflow

Moho · Accepted Answer · 9/28/2018 9:58:42 PM

Based on the linked solution, you will also want to detach (i.e. stop tracking) any entities that are successfully saved to the DB w/ a non-null SysDeletedOn value.

public override ...int SaveChanges...
{
    OnBeforeSaving();
    var result = base.SaveChanges(...);
    OnAfterSaving();

    return result;
}

private void OnAfterSaving()
{
    // you want entries for objects that have the soft delete column
    //   and whose current value for said property is not null
    // this is one way to do it if the property name is always the same
    //   but it isn't necessarily the most efficient
    //   (e.g. if there is a base type or interface for the soft delete
    //    property you can check that)
    var entries = this.ChangeTracker.Entries()
        .Where( ee => ee.Properties.Any( prop =>
            prop.Metadata.Name == "SysDeletedOn"
            && prop.CurrentValue != null ) );

    foreach( var entry in entries )
    {
        // tracked entity is soft deleted and we want to remove it from the context
        entry.State = EntityState.Detached;
    }
}

Entity Framework Core: Global query filter not filtering correctly after data change

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related