Entity Framework Core 2.1 – Automate all that boring boiler plate!

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!
 

Related Blog Posts

We hope you’ve found this to be helpful and are walking away with some new, useful insights. If you want to learn more, here are a couple of related articles that others also usually find to be interesting:

Our Gear Is Packed and We're Excited to Explore with You

Ready to come with us? 

Together, we can map your company’s tech journey and start down the trails. If you’re set to take the first step, simply fill out the form below. We’ll be in touch – and you’ll have a partner who cares about you and your company. 

We can’t wait to hear from you! 

This field is for validation purposes and should be left unchanged.

Together, we can map your company’s tech journey and start down the trails. If you’re set to take the first step, simply fill out the form below. We’ll be in touch – and you’ll have a partner who cares about you and your company. 

We can’t wait to hear from you! 

Montage Portal

Montage Furniture Services provides furniture protection plans and claims processing services to a wide selection of furniture retailers and consumers.

Project Background

Montage was looking to build a new web portal for both Retailers and Consumers, which would integrate with Dynamics CRM and other legacy systems. The portal needed to be multi tenant and support branding and configuration for different Retailers. Trailhead architected the new Montage Platform, including the Portal and all of it’s back end integrations, did the UI/UX and then delivered the new system, along with enhancements to DevOps and processes.

Logistics

We’ve logged countless miles exploring the tech world. In doing so, we gained the experience that enables us to deliver your unique software and systems architecture needs. Our team of seasoned tech vets can provide you with:

Custom App and Software Development

We collaborate with you throughout the entire process because your customized tech should fit your needs, not just those of other clients.

Cloud and Mobile Applications

The modern world demands versatile technology, and this is exactly what your mobile and cloud-based apps will give you.

User Experience and Interface (UX/UI) Design

We want your end users to have optimal experiences with tech that is highly intuitive and responsive.

DevOps

This combination of Agile software development and IT operations provides you with high-quality software at reduced cost, time, and risk.

Trailhead stepped into a challenging project – building our new web architecture and redeveloping our portals at the same time the business was migrating from a legacy system to our new CRM solution. They were able to not only significantly improve our web development architecture but our development and deployment processes as well as the functionality and performance of our portals. The feedback from customers has been overwhelmingly positive. Trailhead has proven themselves to be a valuable partner.

– BOB DOERKSEN, Vice President of Technology Services
at Montage Furniture Services

Technologies Used

When you hit the trails, it is essential to bring appropriate gear. The same holds true for your digital technology needs. That’s why Trailhead builds custom solutions on trusted platforms like .NET, Angular, React, and Xamarin.

Expertise

We partner with businesses who need intuitive custom software, responsive mobile applications, and advanced cloud technologies. And our extensive experience in the tech field allows us to help you map out the right path for all your digital technology needs.

  • Project Management
  • Architecture
  • Web App Development
  • Cloud Development
  • DevOps
  • Process Improvements
  • Legacy System Integration
  • UI Design
  • Manual QA
  • Back end/API/Database development

We partner with businesses who need intuitive custom software, responsive mobile applications, and advanced cloud technologies. And our extensive experience in the tech field allows us to help you map out the right path for all your digital technology needs.

Our Gear Is Packed and We're Excited to Explore with You

Ready to come with us? 

Together, we can map your company’s tech journey and start down the trails. If you’re set to take the first step, simply fill out the contact form. We’ll be in touch – and you’ll have a partner who cares about you and your company. 

We can’t wait to hear from you! 

This field is for validation purposes and should be left unchanged.

Thank you message.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.