(616) 371-1037

[email protected]

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

May 28, 2018 - John Waters


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:

But with the approach I am taking, they are just pure markers, no fields:

In the Application DbContext, I examine my entities, and when I see these marker interfaces, I add the appropriate Shadow Properties.

Here is that ShadowProperties method, which I wrote as an Extension Method, so I can reuse it in other projects:

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

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:

To do this, I call MakeGenericMethod on a static MethodInfo class that was initialized as such:

The call above finds this generic method in my extension class:

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:

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:

There are quite a few bits to this puzzle:

First, note that I inject a IUserSession into the constructor of the AppDbContext:

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:

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:

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:

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:

In the more complex case of both soft delete and multi tenant, the filter is:

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:

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:

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:

I added an Extension Method to ChangeTracker that does the work:

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!


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!


John Waters

20 thoughts on “Entity Framework Core 2.1 – Automate all that boring boiler plate!

  • David

    August 8, 2018 at 1:22 am

    Awesome article! Thanks for pulling all the pieces together in a functional way. Have been using the Global Query Filters and Shadow Properties but not in such a neat way.

  • James Taylor

    October 5, 2018 at 9:33 am

    Could you provide an example on how you are injecting IUserSession.


    • John Waters

      November 12, 2018 at 10:19 pm

      In ConfigureServices, I register with a scoped lifetime, so when one is injected into a controller or anywhere else, it lives for the lifetime of the WebRequest (API call).


      Here, Session is just a POCO with fields for whatever I want to track in the session (UserId, TenantId, etc).

      Then I inject it where I need it. For instance most of my ApiControllers inherit from AuthControllerBase, which does:

      public AuthControllerBase
      IUserSession currentSession,
      IHubHelper hubHelper,
      IMapper mapper) : base(mapper)
      HubHelper = hubHelper;
      CurrentSession = currentSession;

      I my middleware, I also set up a dependency injection and use it to populate the session fields with data from the claims of the current claimsprincipal (parsed from the bearer token). I think this is all in the article, did I miss something?



  • Tom

    October 9, 2018 at 11:20 am

    Great article! Are you able to share how you manage the current session in the pipeline?

    • John Waters

      October 9, 2018 at 11:45 am

      I use DI -> I have a scoped session object, which is populated by a delegating handler based on the claims of the current principal (after authentication). Then, any other code in the pipeline that needs session info just asks for the IUserSession to be injected into its constructor. That session has user id, roles, tenant id etc. Is that what you were wondering?

  • Martin

    October 17, 2018 at 8:49 am

    Great stuff dude!!

    A question though. I have 4 methods of SaveChanges in my DbContext (2 overloads of SaveChanges and 2 of SaveChanges Async)
    If i want to be 100% sure to always audit (the Automatic Auditing part which is great) do I have to override the 4 methods or only the second overload of both?

    I am currently only overriding these 2 and they are always called:
    public override int SaveChanges(bool acceptAllChangesOnSuccess)
    public override Task SaveChangesAsync(CancellationToken cancellationToken = default(CancellationToken))

    Just to be sure.

    Happy Coding!

    • John Waters

      November 12, 2018 at 10:12 pm

      I overrode 2 of them, but in each override I call my same change tracking extension method

  • Filip H

    December 7, 2018 at 12:10 pm

    Clustered index

    Nice article, thanks for sharing.

    I just wonder if creating clustered index on TenantId is a good idea. It is candidate for table partitioning that’s for sure. But clustered index means physically storing the data ordered by the column so if you insert data let’s say uniformly distributed across tenants it would cause frequent page splits..

    • John Waters

      December 7, 2018 at 12:52 pm

      My reasoning behind the clustered index is you want to retrieve all the records for a given tenant in one sweep, good candidate for a cluster.
      I only set these for the top level objects
      For detail tables, the clustered index goes on the parent id
      I separated it into two TenantId related interfaces: ITenantId for filtering, ITenantEntity for creating a clustered index and filter; that way I can pick and choose which get a clustered index.


    December 17, 2018 at 1:38 am

    Great article!

    I’ve a problem when I try to configure ModelBuilderExtensions class in the SetAuditingShadowProperties method with this 2 lines:


    The User table could be the AspNetUsers table? If so, I can’t see this table because are created in another dbcontext.
    What do you suggest me how fix it?

    Thanks in advance.

    • John Waters

      January 17, 2019 at 11:14 am

      In this codebase, I didn’t use AspNetUsers, i.e. AspNetCore Identity, but in another project I did. I just switched my DbContext class to inherit from IdentityDbContext. I also override some of the AspNetIdentity related types (TUser, TRole, int). I got it all working with the AspNetCore Identity tables. I also was able to rename those tables, add additional fields, change the Id from Guid to int, and still have this codebase work the same way. It extends the AspNetIdentity tables with the Tenant, Auditing and SoftDelete fields.

      public class AppDbContextBase : IdentityDbContext
      where TTenant : class
      where TUser : IdentityUser
      where TRole : IdentityRole

      • Travis

        March 9, 2019 at 9:55 am

        Any chance you’d be willing to share that code using AspNetCore Identity? Thanks for the great article!

  • Nicholas

    January 29, 2019 at 3:49 pm

    Hello John,
    I implemented your suggestion, which by the day is awesome, but i’m having some problems when adding migrations.

    i’m receiving the error below:

    > System.TypeInitializationException: The type initializer for ‘myApp.Infrastructure.Data.DataContext’ threw an exception. —> System.InvalidOperationException: Sequence contains no matching element

    I see some reports that i could use the .SingleOrDefault instead of .Single on the Step Below.

    > private static readonly MethodInfo SetGlobalQueryForSoftDeleteMethodInfo = typeof(DbContext).GetMethods(BindingFlags.Public | BindingFlags.Instance)
    .Single(t => t.IsGenericMethod && t.Name == “SetGlobalQueryForSoftDelete”);

    But when i try this i receive:

    > System.NullReferenceException: Object reference not set to an instance of an object.
    at myApp.Infrastructure.Data.DataContext.SetGlobalQueryFilters(ModelBuilder modelBuilder)

    Could you please shed some light?

    Thanks in advance!

    • John Waters

      January 29, 2019 at 5:14 pm

      It seems you might have multiple methods called SetGlobalQueryForSoftDelete, possibly with different signatures?

  • Alireza

    February 12, 2019 at 2:17 am

    Great article!
    right now I’m working on a project whitch must deal with multiple tenants
    it helps me to much

  • Viktoras

    March 26, 2019 at 1:12 am

    Wow. This is amazing! Really a saver for not to forget to add columns to models and not to forget to filter by them. Just apply interface – and you have it all! Amazing!

    I am stuck with one thing – can’t get it to work with the abstract class. Shadow properties sets properly, but global query filters raises error. I set ISoftDelete for the abstract class (e.g. Link), but when I try to create migration I get: “The filter expression ‘item => Property(item, “IsValid”)’ cannot be specified for entity type ‘BottomLink’. A filter may only be applied to the root entity type in a hierarchy”.

    I read your note about children classess, but not seeing an example can’t figure it out when ITenant should be used and when ITenantEntity should be used.

    Can you guide me how to apply global query filters to models with abstract class?

    • John Waters

      April 3, 2019 at 12:55 pm

      I try to avoid inheritance with EF. Relational databases don’t do well with it. There is some limited support for it, but i would just stay clear. Instead of an abstract class that has any common properties you might want to use in different bits of code, create one or more interfaces for those, and then add a partial class for each of the classes that should implement that interface.
      For instance, if you have a BottomLink and a TopLink class with common properties, create an ILink. Then add two partial classes to your project ( class BottomLink : ILink and class TopLink : ILink). This will allow your code to use instance of Top and Bottom through ILink just as easily as inheriting from an abstract base class. The AppDbContext would have DbSet and DbSet. These two classes could also support ITenant or ITenantEntity. I just use two different Tenant marker interfaces. If it’s either of them, I add the Shadow TenantId property, but I only add the clustered index for ITenantEntity. Hope that helps.

  • David S.

    June 4, 2019 at 4:01 pm

    A bit late to the party but Thanks for the post. I stumbled across the post at the perfect time working on our auditing in a new API and this was exactly what I was trying to do.

    Everything seems to work perfectly except that it seems the audit shadow properties changes do not persist after the DBConect changes are saved to the database.

    It isn’t critical but the use case is we return the entity that is created/updated/retrieved in the response body and the audit fields are wanted so they can be displayed in the UI in certain cases.

    At this point I might have to revert to using actual fields to meet this feature unless there is something that I might be doing by mistake that is causing my issue…

    • John Waters

      June 4, 2019 at 4:49 pm

      How are you trying to access the auditing fields?

      I use helper methods like this (assuming the entity is known to you current DbContext – make sure it is the same instance of DbContext!):

      public int CreatedByIdFromEntity(IAudited entity)
      if (entity == null)
      Logger.LogWarning(“Asking for CreatedById from null IAudited interface”);
      var entry = Context.Entry(entity);
      if (entry == null)
      Logger.LogWarning(“Asking for CreatedById from null context entry”);
      return entry.Property(“CreatedById”).CurrentValue;

      • David S.

        June 6, 2019 at 8:36 am

        Thanks once again.

        I was missing entry.Property(“CreatedById”).CurrentValue;

        That got me the value of the Shadow Properties but it turned out that AutoMapper (sigh) was my biggest problem.

        With those bits fixed everything is working as expected and the Shadow Properties are easily added and set on my entities as well as the Audit Shadow Properties being returned in API responses.


Leave a comment

Your email address will not be published. Required fields are marked *