FREE WEBINAR: "It's Time to Migrate Your Team to .NET Core" Register Now!

(616) 371-1037

[email protected]

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

May 28, 2018 - John Waters

30 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:

    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!

 

John Waters

30 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
  • Ruben GUINEZ GUTIERREZ

    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:

    builder.Entity().HasOne().WithMany().HasForeignKey(“CreatedById”).OnDelete(DeleteBehavior.Restrict);
    builder.Entity().HasOne().WithMany().HasForeignKey(“ModifiedById”).OnDelete(DeleteBehavior.Restrict);

    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.

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

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

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

    Reply
    • John Waters

      January 29, 2019 at 5:14 pm

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

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

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

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

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

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

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

        Reply
        • David S.

          November 13, 2019 at 7:39 am

          A follow up. We have been using this now for a few months and it works great with one caveat.

          After this implementation, we implemented static code analysis tools to help maintain code quality and consistency. Immediately the tools alerted to the use of empty interfaces.After some discussion on the issue it was decided that empty interfaces are less than ideal and we moved to Attributes.

          Using attributes seemingly provides all the benefits of your implementation without the empty interfaces that could be mistakenly deleted or flagged by code analysis and it is just as easy to decorate a class with an Attribute as it is to implement the interface.

          Seems simple, but turned out to be a nice improvement.

          Reply
          • John Waters

            November 13, 2019 at 10:40 am

            That sounds like a great improvement – do you want to share the code you wrote to detect the attributes and apply the query filters?

  • Kay Bar

    July 15, 2019 at 11:31 pm

    Great blog, but I’m struggling grasping the parts about generic methodinfo & invoke. Is this a required approach to setting up the shadow properties?

    Would this not work:: ?

    public static class ModelBuilderExtensions
    {
    public static void ShadowProperties(this ModelBuilder modelBuilder)
    {
    foreach (var tp in modelBuilder.Model.GetEntityTypes())
    {
    var t = tp.ClrType;
    // ….
    // set soft delete property
    if (typeof(ISoftDelete).IsAssignableFrom(t))
    {
    builder.Entity().Property(“IsDeleted”);
    }

    Reply
  • Edward

    July 21, 2019 at 4:56 pm

    I’m not sure whether I’m just tired or am completely missing something, but I got completely lost on ITenantEntity and ITenant. I think in future it would be better to include some sort of a Github link.

    Reply
  • Nick

    July 22, 2019 at 12:35 pm

    Can you please post ITenant, ITenantEntity and ITenantId interfaces?

    Reply
    • John Waters

      September 10, 2019 at 1:47 pm

      These are marker interfaces, they don’t have any properties
      The code in the appdbcontexts examines them to see which models it should add TenantId to, and for TenantEntity, it also adds a clustered index on TenantId.

      Reply
  • Keith Fimreite

    August 20, 2019 at 3:30 pm

    Great article. I am trying to implement this for .NET Core 3 for a public repo: https://github.com/enkodellc/blazorboilerplate Do you have a repo to view the code as a whole? I am new to .NET Core and Entity Framework and this is very cool but also finding the missing pieces is a challenge.

    Reply
  • J. Tower

    August 20, 2019 at 4:25 pm

    Keith,

    I’ll be able to find a simplified version of what we use at Trailhead in this GitHub repo:
    https://github.com/jonathantower/aspnet-multitenant

    This is from a conference talk I do that’s inspired by John’s blog post. Good luck; your Blazor project looks very cool!

    Reply
  • Fawaz

    September 16, 2019 at 7:52 am

    Hey John, wonderful code and makes it much cleaner and easier to filter. However, I am confused on one thing. You have TenantId filter inside the If condition for IsSoftDelete

    if (typeof(ISoftDelete).IsAssignableFrom(t))
    {
    if (typeof(ITenantEntity).IsAssignableFrom(t))

    Wouldn’t this cause the ITenantEntity entity not to filter if ISoftDelete is not inherited? Should we have a separate If condition for just TenantEntity?

    Reply
    • John Waters

      September 16, 2019 at 1:16 pm

      Yes, good eye. That is a bug, and was fixed in our code base later. Our code looks more like this now:

      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))
      {
      // soft deletable and tenant entity (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
      {
      // soft deletable
      var method = SetGlobalQueryForSoftDeleteMethodInfo.MakeGenericMethod(t);
      method.Invoke(this, new object[] { modelBuilder });
      }
      }
      else
      {
      if (typeof(ITenant).IsAssignableFrom(t))
      {
      var method = SetGlobalQueryForTenantMethodInfo.MakeGenericMethod(t);
      method.Invoke(this, new object[] { modelBuilder });
      }
      }
      }
      }

      Reply

Leave a comment to J. Tower Cancel reply

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