In any real world enterprise application, you end up writing a lot of similar, tedious boilerplate code for bookkeeping . This logic results in a data model where each table is adorned with a lot of non-domain specific columns – does this look familiar?
There are three common patterns at work here:
Auditing – columns CreatedBy/CreatedOn that record when a record was created and by whom. Columns ModifiedOn/ModifiedBy that track the latest change. We want to automate how this happens.
Soft Delete – Marking a record (and it’s children) with IsDeleted rather than actually deleting the record. We dont want to see deleted data when fetching active records, so that ends up being in each WHERE clause.
Multi Tenant – Having data for multiple tenants in a shared database, with data being partitioned by a Tenant Id. We don’t want a user to see data from a different tenant, so that ends up in each WHERE clause for each table.
Sadly, this ends up also drowning your classes in attributes:
To make things worse, the logic to manage these fields ends up spread all over your application logic.
What if I said you could make your class look like this?
But still have the same database model, and as a bonus put all that bookkeeping code in one, reusable place, out of sight from your regular business logic?
With Entity Framework Core 2.1, that is exactly what I did!
Shadow Properties
In Entity Framework Core, you can add properties to your model and database, without actually spelling them out in your code. These properties are called Shadow Properties. They behave much like regular class properties – they are represented as fields in the database, they can be used in queries, and you can get and set their values.
The key to automating shared logic is to use interfaces to tag your classes with the kind of behavior you want them to have. To that end, the three key patterns above are represented in my code by the interfaces ISoftDelete, IAuditedEntityBase and ITenant (the ITenantId you see above is derived from ITenant, and adds some other features, to be explained).
Now, “in the past”, these interfaces would also carry with them properties, which would be implemented in the respective domain classes:
public interface ITenant { int TenantId { get; set; } Tenant Tenant { get; set; } }
But with the approach I am taking, they are just pure markers, no fields:
public interface ITenant { }
In the Application DbContext, I examine my entities, and when I see these marker interfaces, I add the appropriate Shadow Properties.
protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.ShadowProperties(); base.OnModelCreating(modelBuilder); }
Here is that ShadowProperties method, which I wrote as an Extension Method, so I can reuse it in other projects:
public static class ModelBuilderExtensions { public static void ShadowProperties(this ModelBuilder modelBuilder) { foreach (var tp in modelBuilder.Model.GetEntityTypes()) { var t = tp.ClrType; // set auditing properties if (typeof(IAuditedEntityBase).IsAssignableFrom(t)) { var method = SetAuditingShadowPropertiesMethodInfo.MakeGenericMethod(t); method.Invoke(modelBuilder, new object[] { modelBuilder }); } // set tenant properties if (typeof(ITenant).IsAssignableFrom(t)) { var method = SetTenantShadowPropertyMethodInfo.MakeGenericMethod(t); method.Invoke(modelBuilder, new object[] { modelBuilder }); } // set soft delete property if (typeof(ISoftDelete).IsAssignableFrom(t)) { var method = SetIsDeletedShadowPropertyMethodInfo.MakeGenericMethod(t); method.Invoke(modelBuilder, new object[] { modelBuilder }); } } } private static readonly MethodInfo SetIsDeletedShadowPropertyMethodInfo = typeof(ModelBuilderExtensions).GetMethods(BindingFlags.Public | BindingFlags.Static) .Single(t => t.IsGenericMethod && t.Name == "SetIsDeletedShadowProperty"); private static readonly MethodInfo SetTenantShadowPropertyMethodInfo = typeof(ModelBuilderExtensions).GetMethods(BindingFlags.Public | BindingFlags.Static) .Single(t => t.IsGenericMethod && t.Name == "SetTenantShadowProperty"); private static readonly MethodInfo SetAuditingShadowPropertiesMethodInfo = typeof(ModelBuilderExtensions).GetMethods(BindingFlags.Public | BindingFlags.Static) .Single(t => t.IsGenericMethod && t.Name == "SetAuditingShadowProperties"); public static void SetIsDeletedShadowProperty<T>(ModelBuilder builder) where T : class, ISoftDelete { // define shadow property builder.Entity<T>().Property<bool>("IsDeleted"); } public static void SetTenantShadowProperty<T>(ModelBuilder builder) where T : class, ITenant { // define shadow property builder.Entity<T>().Property<int>("TenantId"); // define FK to Tenant builder.Entity<T>().HasOne<Tenant>().WithMany().HasForeignKey("TenantId").OnDelete(DeleteBehavior.Restrict); } public static void SetAuditingShadowProperties<T>(ModelBuilder builder) where T : class, IAuditedEntityBase { // define shadow properties builder.Entity<T>().Property<DateTime>("CreatedOn").HasDefaultValueSql("GetUtcDate()"); builder.Entity<T>().Property<DateTime>("ModifiedOn").HasDefaultValueSql("GetUtcDate()"); builder.Entity<T>().Property<int>("CreatedById"); builder.Entity<T>().Property<int>("ModifiedById"); // define FKs to User builder.Entity<T>().HasOne<User>().WithMany().HasForeignKey("CreatedById").OnDelete(DeleteBehavior.Restrict); builder.Entity<T>().HasOne<User>().WithMany().HasForeignKey("ModifiedById").OnDelete(DeleteBehavior.Restrict); }
There are a few non-obvious things going on here, so let me break it down a little.
First, I want to iterate over all the Entities in my model, so
foreach (var tp in modelBuilder.Model.GetEntityTypes()) { var t = tp.ClrType;
You could use other strategies to iterate over your classes, getting them from an assembly, or all assemblies referenced by your project, etc. In my case, it’s enough to just look at the classes in the model.
For each pattern (soft delete, multi tenant, auditing), I look for the corresponding marker interface, and if present, I Invoke a method to set up the Shadow Properties for the type being inspected:
// set auditing properties if (typeof(IAuditedEntityBase).IsAssignableFrom(t)) { var method = SetAuditingShadowPropertiesMethodInfo.MakeGenericMethod(t); method.Invoke(modelBuilder, new object[] { modelBuilder }); }
To do this, I call MakeGenericMethod on a static MethodInfo class that was initialized as such:
private static readonly MethodInfo SetAuditingShadowPropertiesMethodInfo = typeof(ModelBuilderExtensions).GetMethods(BindingFlags.Public | BindingFlags.Static) .Single(t => t.IsGenericMethod && t.Name == "SetAuditingShadowProperties");
The call above finds this generic method in my extension class:
public static void SetAuditingShadowProperties<T>(ModelBuilder builder) where T : class, IAuditedEntityBase { // define shadow properties builder.Entity<T>().Property<DateTime>("CreatedOn").HasDefaultValueSql("GetUtcDate()"); builder.Entity<T>().Property<DateTime>("ModifiedOn").HasDefaultValueSql("GetUtcDate()"); builder.Entity<T>().Property<int>("CreatedById"); builder.Entity<T>().Property<int>("ModifiedById"); // define FKs to User builder.Entity<T>().HasOne<User>().WithMany().HasForeignKey("CreatedById").OnDelete(DeleteBehavior.Restrict); builder.Entity<T>().HasOne<User>().WithMany().HasForeignKey("ModifiedById").OnDelete(DeleteBehavior.Restrict); }
This is where the shadow properties are added to the model. Note that they are typed, have names, and can be given default values. For CreatedOn and ModifiedOn, I set SQL Server default values of GetUtcDate (this ends up in SQL generated that sets these defaults for those columns on each affected table). Note that I also define the Foreign Key relationships to the User table for the CreatedBy and ModifiedBy properties.
Looking at the full code above you can see the same pattern defining the TenantId field and relationship to Tenant, and the IsDeleted field. If you look at the Entity Framework migration for this, you see it all set up:
And further down in the code you’ll find statements for index, foreign keys and so on:
migrationBuilder.AddForeignKey( name: "FK_Location_User_ModifiedById", table: "Location", column: "ModifiedById", principalTable: "User", principalColumn: "Id", onDelete: ReferentialAction.Restrict);
So to recap – with this one call to modelBuilder.ShadowState(), we have added all these boiler plate properties to our model and to our database tables! All we have to do is mark our Entities with the appropriate interfaces (and you could save even more here if you used common base classes for your entities that define those basic tenets).
Part two of the magic is getting these properties to be used in all generated WHERE clause. Enter the Global Query Filter!
Global Query Filters
The next really useful innovation in Entity Framework is Global Query Filters. Again, I am going to do this in a generic way on all classes that have the appropriate marker interfaces:
public class AppDbContext : DbContext { private readonly IUserSession _userSession; public AppDbContext(DbContextOptions options, IUserSession userSession) : base(options) { _userSession = userSession; } private void SetGlobalQueryFilters(ModelBuilder modelBuilder) { foreach (var tp in modelBuilder.Model.GetEntityTypes()) { var t = tp.ClrType; // set global filters if (typeof(ISoftDelete).IsAssignableFrom(t)) { if (typeof(ITenantEntity).IsAssignableFrom(t)) { // softdeletable and tenant (note do not filter just ITenant - too much filtering! // just top level classes that have ITenantEntity var method = SetGlobalQueryForSoftDeleteAndTenantMethodInfo.MakeGenericMethod(t); method.Invoke(this, new object[] {modelBuilder}); } else { // softdeletable var method = SetGlobalQueryForSoftDeleteMethodInfo.MakeGenericMethod(t); method.Invoke(this, new object[] {modelBuilder}); } } } } private static readonly MethodInfo SetGlobalQueryForSoftDeleteMethodInfo = typeof(AppDbContext).GetMethods(BindingFlags.Public | BindingFlags.Instance) .Single(t => t.IsGenericMethod && t.Name == "SetGlobalQueryForSoftDelete"); private static readonly MethodInfo SetGlobalQueryForSoftDeleteAndTenantMethodInfo = typeof(AppDbContext).GetMethods(BindingFlags.Public | BindingFlags.Instance) .Single(t => t.IsGenericMethod && t.Name == "SetGlobalQueryForSoftDeleteAndTenant"); public void SetGlobalQueryForSoftDelete<T>(ModelBuilder builder) where T : class, ISoftDelete { builder.Entity<T>().HasQueryFilter(item => !EF.Property<bool>(item, "IsDeleted")); } public void SetGlobalQueryForSoftDeleteAndTenant<T>(ModelBuilder builder) where T : class, ISoftDelete, ITenant { builder.Entity<T>().HasQueryFilter( item => !EF.Property<bool>(item, "IsDeleted") && (_userSession.DisableTenantFilter || EF.Property<int>(item, "TenantId") == _userSession.TenantId)); } public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken()) { ChangeTracker.SetShadowProperties(_userSession); return await base.SaveChangesAsync(cancellationToken); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.ShadowProperties(); SetGlobalQueryFilters(modelBuilder); base.OnModelCreating(modelBuilder); }
There are quite a few bits to this puzzle:
First, note that I inject a IUserSession into the constructor of the AppDbContext:
public class AppDbContext : DbContext { private readonly IUserSession _userSession; public AppDbContext(DbContextOptions options, IUserSession userSession) : base(options) { _userSession = userSession; }
This is using regular dotnetcore dependency injection. For my multitenant filters and auditing to work, the DbContext needs to know who the current caller is, and which Tenant they belong to. This is in the injected session:
public interface IUserSession { int UserId { get; set; } int TenantId { get; set; } List<string> Roles { get; set; } string UserName { get; set; } bool DisableTenantFilter { get; set; } }
The session is set up in the Authentication pipeline, and that’s a topic for a different blog, but it’s enough for now to know that for each WebAPI call, a separate instance of DbContext is created, and in that instance, the current user session is injected.
The call to SetGlobalQueryFilters follows the same basic pattern, iterating over the types in the model and calling generic methods on those classes satisfying the marker interface requirements:
private void SetGlobalQueryFilters(ModelBuilder modelBuilder) { foreach (var tp in modelBuilder.Model.GetEntityTypes()) { var t = tp.ClrType; // set global filters if (typeof(ISoftDelete).IsAssignableFrom(t)) { if (typeof(ITenantEntity).IsAssignableFrom(t)) { // softdeletable and tenant (note do not filter just ITenant - too much filtering! // just top level classes that have ITenantEntity var method = SetGlobalQueryForSoftDeleteAndTenantMethodInfo.MakeGenericMethod(t); method.Invoke(this, new object[] {modelBuilder}); } else { // softdeletable var method = SetGlobalQueryForSoftDeleteMethodInfo.MakeGenericMethod(t); method.Invoke(this, new object[] {modelBuilder}); } } } }
There are two cases: the class needs to be filtered by IsDeleted, or it needs both IsDeleted and TenantId. Let’s look at the simpler case:
public void SetGlobalQueryForSoftDelete<T>(ModelBuilder builder) where T : class, ISoftDelete { builder.Entity<T>().HasQueryFilter(item => !EF.Property<bool>(item, "IsDeleted")); }
The call to HasQueryFilter sets up the filter that gets added to any calls that fetch from the corresponding table. Since IsDeleted is a Shadow Property, I can’t just reference it as item.IsDeleted, or even through the interface ISoftDelete, as there is no ISoftDelete.IsDeleted – it is a pure marker interface. Instead, the syntax EF.Property<type>( item, “propertyname”) is used:
!EF.Property<bool>(item, "IsDeleted")
In the more complex case of both soft delete and multi tenant, the filter is:
builder.Entity<T>().HasQueryFilter( item => !EF.Property<bool>(item, "IsDeleted") && (_userSession.DisableTenantFilter || EF.Property<int>(item, "TenantId") == _userSession.TenantId));
Note the special case _userSession.DisableTenantFilter above. In some cases, for instance when the user is logging in, I don’t know the Tenant of the user, so I can’t filter by tenant. So I have to be able to disable that filter, and I chose to do it in the session. You could also make it a a property of the context.
Here is the code that turns the filter off for logging in:
public async Task<IActionResult> Token([FromBody]TokenRequestDTO req) { // we won't know which tenant the user belongs to until found CurrentSession.DisableTenantFilter = true; var user = await _userRepo.GetByEmailAsync(req.Username); if (user == null) { return ValidationError("Invalid username/password combination."); } var entry = _userRepo.Context.Entry(user); var tenantId = entry.Property<int>("TenantId").CurrentValue; // now we can set the tenant and user for this session CurrentSession.DisableTenantFilter = false; CurrentSession.TenantId = tenantId; CurrentSession.UserId = user.Id;
You will also see above how to get a shadow property in code. First, you find the Entry in the ChangeTracker with Context.Entry(user). Then, you get the current value with entry.Property<int>(“TenantId”).CurrentValue.
You might want to add the same kind of ability to turn off IsDeleted filtering, for retrieving historical data for reports or logs.
With these query filters in place, all calls to the database will be automatically and safely filtered by multitenancy and soft delete dimensions. Your code can just query tables without worrying about adding these conditions, Entity Framework sees where it needs to add them for you.
A final note on this part: many models have an ownership hierarchy, where some base class has children (master detail), several levels deep. For instance, an Invoice with InvoiceDetails, or in my case, a Trip has Stops, which have Legs, which have Maneuvers (4 levels). Having the Tenant filter added to the SQL at each level becomes very verbose and degrades performance. Since the children are safely joined to their parents via other properties, we really don’t need to filter them by Tenant once we have the correct parents. For this reason, I have two interfaces. ITenant causes a TenantId field to be added to the model and database table, but only the derived interface ITenantEntity causes the Global Query Filter to be added. Only my root classes have this interface. I also use this logic to create a clustered index on TenantId for these root classes (rather than the default clustered index on Id). I hid this code in the listing above, but here it is:
public static class ModelBuilderExtensions { public static void ShadowProperties(this ModelBuilder modelBuilder) { foreach (var tp in modelBuilder.Model.GetEntityTypes()) { var t = tp.ClrType; // set clustered tenant index if (typeof(ITenantEntity).IsAssignableFrom(t)) { var method = SetTenantIdClusteredIndexsMethodInfo.MakeGenericMethod(t); method.Invoke(modelBuilder, new object[] { modelBuilder }); } } } private static readonly MethodInfo SetTenantIdClusteredIndexsMethodInfo = typeof(ModelBuilderExtensions).GetMethods(BindingFlags.Public | BindingFlags.Static) .Single(t => t.IsGenericMethod && t.Name == "SetTenantIdClusteredIndex"); public static void SetTenantIdClusteredIndex<T>(ModelBuilder builder) where T : class, ITenantEntity { builder.Entity<T>().HasKey(item => item.Id).ForSqlServerIsClustered(false); builder.Entity<T>().HasIndex("TenantId").ForSqlServerIsClustered().IsUnique(false); } }
Now we have efficient and automatic filtering, but what about that the Auditing of who changed a record?
Automatic Auditing
It turns out you can centralize the auditing too! Just override the SaveChanges of your context:
public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken()) { ChangeTracker.SetShadowProperties(_userSession); return await base.SaveChangesAsync(cancellationToken); }
I added an Extension Method to ChangeTracker that does the work:
public static class ChangeTrackerExtensions { public static void SetShadowProperties(this ChangeTracker changeTracker, IUserSession userSession) { changeTracker.DetectChanges(); var timestamp = DateTime.UtcNow; foreach (var entry in changeTracker.Entries()) { if (entry.Entity is IAuditedEntityBase) { if (entry.State == EntityState.Added || entry.State == EntityState.Modified) { entry.Property("ModifiedOn").CurrentValue = timestamp; entry.Property("ModifiedById").CurrentValue = userSession.UserId; } if (entry.State == EntityState.Added) { entry.Property("CreatedOn").CurrentValue = timestamp; entry.Property("CreatedById").CurrentValue = userSession.UserId; if (entry.Entity is ITenant) { entry.Property("TenantId").CurrentValue = userSession.TenantId; } } } if (entry.State == EntityState.Deleted && entry.Entity is ISoftDelete) { entry.State = EntityState.Modified; entry.Property("IsDeleted").CurrentValue = true; } } } }
Some notes about this code:
First, I call DetectChanges to have the ChangeTracker find all the changed entities in the DbContext. Then, I iterate of these changed entries, and inspect the corresponding Entity for the IAuditedEntityBase marker interface. If present, I handle the Added, Modified and Deleted states:
- For Added or Modified I set ModifiedOn and ModifiedBy (again using the session object)
- For Added, I set CreatedOn and CreatedBy, as well as the correct Tenant if it is an ITenant class (from the session).
- For Deleted, if a SoftDelete entity, I undo the delete operation, and instead set IsDeleted.
I use the same timestamp DateTime.UtcNow for all the modifications, I find that helps when you query the data to see what was changed in the same call.
So now you can just fetch, modify, add and remove entities from your context as usual, without worrying about these fields, and when you call SaveChanges, this code kicks in and sets up all the correct bookkeeping for you!
Summary
I hope you will find these techniques as useful as I have! At Trailhead we have packaged these, along with a lot of other useful reusable components of enterprise development, in the Trailhead Technology Framework, which allows us to jump start our client projects with a lot of out of the box functionality. I will be blogging about some other framework components in the future. Enjoy!