K
J.D. Mentioned Row Level Security in a comment and it seems very promising for people who need to do what I want but only have SQL to work with. If they post an answer I will give them credit for this question.
Though it is possible to create migrations in EFCore that allows for custom SQL to be executed when the database is deployed, it felt forced. But still probably a valid solution
https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/operations
https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/managing?tabs=dotnet-core-cli
https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15
Based on the new terms in my vocabulary, I found global query filter from EFCore and I went with that.
https://docs.microsoft.com/en-us/ef/core/querying/filters
Though you do need to be a bit careful with how you set it up.
Using required navigation to access entity which has global query filter defined may lead to unexpected results.
But in essence it boils down to adding something like this to the code first configuration.
modelBuilder.Entity().HasMany(b => b.Posts).WithOne(p => p.Blog).IsRequired();
modelBuilder.Entity().HasQueryFilter(b => b.Url.Contains("fish"));
modelBuilder.Entity().HasQueryFilter(p => p.Blog.Url.Contains("fish"));
https://www.rubberchickenparadise.com/blog/2020-04-01-handling-row-level-security-with-entity-framework/ , where they discuss injecting user JWT token info into the context to allow personal filtering for an individual.
public class Context : DbContext
{
private readonly IClaimsProvider _claimsProvider;
private int UserId => _claimsProvider.UserId;
private IEnumerable<int> AccessibleClientIds => _claimsProvider.AccessibleClientIds;
public Context(DbContextOptions<Context> options, IClaimsProvider claimsProvider) : base(options)
{
_claimsProvider = claimsProvider;
}
...
}
modelBuilder.Entity(entity =>
{
entity.HasQueryFilter(x => AccessibleClientIds.Contains(x.Id));
entity.HasKey(x => x.Id);
entity.HasMany(x => x.UserClientAccess)
.WithOne(x => x.Client)
.HasForeignKey(x => x.ClientId);
});
modelBuilder.Entity<UserOptions>(entity =>
{
entity.HasQueryFilter(x => x.UserId == UserId);
entity.HasKey(x => x.Id);
});
https://spin.atomicobject.com/2019/01/29/entity-framework-core-soft-delete/ talks about overriding the default savechanges functionality to always soft delete, but this is not what I want, but some might find it helpful.
public override int SaveChanges()
{
UpdateSoftDeleteStatuses();
return base.SaveChanges();
}
public override Task SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
{
UpdateSoftDeleteStatuses();
return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
}
private void UpdateSoftDeleteStatuses()
{
foreach (var entry in ChangeTracker.Entries())
{
switch (entry.State)
{
case EntityState.Added:
entry.CurrentValues["isDeleted"] = false;
break;
case EntityState.Deleted:
entry.State = EntityState.Modified;
entry.CurrentValues["isDeleted"] = true;
break;
}
}
}
Eventual implementation
I have all the relevant models inherit from Deactivateable to add the property/col to the database.
Then because of the simplicity of the filter I simply created this config in the context:
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
if (entity.ClrType.IsSubclassOf(typeof(Deactivatable)))
{
var dateTimeOffsetDefaultValue = new DateTimeOffset(new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified), new TimeSpan(0, 0, 0, 0, 0));
modelBuilder.Entity(entity.ClrType, o =>
{
o.Property(nameof(Deactivatable.DeactivatedDate)).HasDefaultValue(dateTimeOffsetDefaultValue);
});
var deactivatedDate = entity.FindProperty(nameof(Deactivatable.DeactivatedDate)).PropertyInfo;
var parameterExpression = Expression.Parameter(entity.ClrType);
var propertyExpression = Expression.Property(parameterExpression, deactivatedDate);
var constExpression = Expression.Constant(dateTimeOffsetDefaultValue);
var equalExpression = Expression.Equal(propertyExpression, constExpression);
var filter = Expression.Lambda(equalExpression, parameterExpression);
modelBuilder.Entity(entity.ClrType).HasQueryFilter(filter );
}
}
Based on the inspiration from this https://github.com/dotnet/EntityFramework.Docs/issues/2820#issuecomment-803489089 made by https://github.com/stevendarby . I couldn't figure out where they got .Model.FindLeastDerivedEntityTypes from, so I simply chose to iterate the models myself and look at the subclass, based on an idea I saw in a related SO post, which I can no longer find and reference.
