Apply deletes during custom seed of DbSet<T>

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).

DbSet

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.

TEntity

Except()

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.

TEntity

Id

<EntityName>Id

IBaseEntity

Id

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.

.Select(s => idProperties)

Id

DbSet<T>

Except()

<EntityName>Id

DbContext

1 Answer
1

EF Core metadata provides all the necessary information needed.

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

Expression

(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.

NOT IN (...)

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();



Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.