Apply deletes during custom seed of DbSet

c# entity-framework-core generics linq reflection

Question

I am trying to extend my custom seed routine (I know EF Core 2.1 supports seeding natively, but I have a blocker in converting) to apply deletions. If a record exists in the database, but no longer in the seed data, I want to delete it. Rather than writing a custom delete routine for each DbSet, I am trying to implement with generics (and possibly reflection if needed).

My first attempt:

private static void Delete<TEntity>(DbContext dbContext, IEnumerable<TEntity> seedRows) where TEntity : class, IBaseEntity
{
    var toRemove = dbContext.Set<TEntity>().Except(seedRows);
    dbContext.RemoveRange(toRemove);
    dbContext.SaveChanges();
}

However, since TEntity contains some properties that are null in the seed data (such as timestamps generated on add), I can't compare the entire entities in the Except() call (with the default equality comparer, anyway). I really only care about comparing the primary key.

My work in progress for addressing this issue is below. TEntity could have a primary key of a simple Id column, or it could be a many-to-many mapping with a complex primary key of two <EntityName>Ids. IBaseEntity currently does not have any Id/primary key information since it is implemented both by basic entities as well as many-to-many/junction entities.

private static void Delete<TEntity>(DbContext dbContext, IEnumerable<TEntity> seedRows) where TEntity : class, IBaseEntity
{
    var idProperties = typeof(TEntity).GetProperties().Where(p => p.Name.Contains("Id"));
    var toRemove = dbContext.Set<TEntity>().Select(s => idProperties).Except(seedRows.Select(s => idProperties));
    dbContext.RemoveRange(toRemove);
    dbContext.SaveChanges();
}

full source/context

The two instances of .Select(s => idProperties), however, obviously do not work. Is there a way to select the Id properties (or, alternatively, the primary key) of a DbSet<T> to be used in the Except() comparer? I am open to a completely different approach as well, since I feel like I am off in the weeds.

1
1
9/10/2018 6:35:15 PM

Accepted Answer

EF Core metadata provides all the necessary information needed.

Instead of reflection, you can use Expression class to dynamically build criteria like this (pseudocode):

(seedRows1.Key1 == e.Key1 && seedRows1.Key2 == e.Key2 ... && seeedRows1.KeyM == e.KeyM)
||
(seedRows2.Key1 == e.Key1 && seedRows2.Key2 == e.Key2 ... && seeedRows2.KeyM == e.KeyM)
...
||
(seedRowsN.Key1 == e.Key1 && seedRowsN.Key2 == e.Key2 ... && seeedRowsN.KeyM == e.KeyM);

which would return the matching items from the db. To get the non matching items, the criteria can simply be inverted and used as predicate for deleting. Note that for the single PK this would translate to NOT IN (...) SQL criteria.

Putting it into action:

private static void Delete<TEntity>(DbContext dbContext, IEnumerable<TEntity> seedRows)
    where TEntity : class//, IBaseEntity
{
    var entityType = dbContext.Model.FindEntityType(typeof(TEntity));
    var entityPK = entityType.FindPrimaryKey();
    var dbEntity = Expression.Parameter(entityType.ClrType, "e");
    Expression matchAny = null;
    foreach (var entity in seedRows)
    {
        var match = entityPK.Properties
            .Select(p => Expression.Equal(
                Expression.Property(dbEntity, p.PropertyInfo),
                Expression.Property(Expression.Constant(entity), p.PropertyInfo)))
            .Aggregate(Expression.AndAlso);
        matchAny = matchAny != null ? Expression.OrElse(matchAny, match) : match;
    }
    var dbQuery = dbContext.Set<TEntity>().AsQueryable();
    if (matchAny != null)
    {
        var predicate = Expression.Lambda<Func<TEntity, bool>>(Expression.Not(matchAny), dbEntity);
        dbQuery = dbQuery.Where(predicate);
    }
    var dbEntities = dbQuery.ToList();
    if (dbEntities.Count == 0) return;
    dbContext.RemoveRange(dbEntities);
    dbContext.SaveChanges();
}
1
9/10/2018 7:43:47 PM


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