(616) 371-1037

[email protected]

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

May 28, 2018 - John Waters

9 Comments

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!

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!

 

John Waters

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

    Reply
  • James Taylor

    October 5, 2018 at 9:33 am

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

    Thanks

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

      services.AddScoped();

      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?

      Cheers

      John

      Reply
  • Tom

    October 9, 2018 at 11:20 am

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

    Reply
    • 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?

      Reply
  • 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.

    Thanks!
    Happy Coding!

    Reply
    • 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

      Reply
  • 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..

    Reply
    • 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.

      Reply

Leave a comment

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