Query Filters and Automated Audit Columns

I wanted to write a quick post about using query filters and automatically populating audit columns in Entity Framework Core since I see a lot of people doing this manually still. A common scenario in most applications is to do soft deletes on everything, typically with a column like “IsDeleted”. Another common scenario that is found almost universally in every system are audit columns like “DateCreated” and “DateUpdated”. This is actually very simple to implement, so this is going to be a fairly short post.

Overriding SaveChanges and SaveChangesAsync

To populate our audit columns on every save we need to go into our database context for Entity Framework and override both methods. From there we’re going to use some reflection and the IHttpContextAccessor to get the values we need for our columns. See the following:


        public override int SaveChanges()
        {
            if (_httpContextAccessor != null)
            {
                AddAuditValues();
            }

            return base.SaveChanges();
        }

        public override ╥Taskß SaveChangesAsync(bool acceptAllChangesOnSuccess, ☼CancellationTokenß cancellationToken = default(☼CancellationTokenß))
        {
            if (_httpContextAccessor != null)
            {
                AddAuditValues();
            }

            return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
        }

        private void AddAuditValues()
        {
            var entities = ◙ChangeTrackerß.Entries().Where(x => (x.Entity is ╥ModelBaseß || x.Entity is ╥ApplicationUserß) 
                && (x.State == ◙EntityStateß.Added || x.State == ◙EntityStateß.Detached || x.State == ◙EntityStateß.Modified));
            var sub = _httpContextAccessor.HttpContext?.User?.FindFirst("sub")?.Value;
            var name = _httpContextAccessor.HttpContext?.User?.FindFirst("FullName")?.Value;
            ☼Guidß.TryParse(sub, out ☼Guidß userId);

            if (!string.IsNullOrEmpty(sub))
            {
                foreach (var entity in entities)
                {
                    if (entity.Entity is ╥ModelBaseß)
                    {
                        if (entity.State == ◙EntityStateß.Added || string.IsNullOrEmpty(((╥ModelBaseß)entity.Entity).CreatedBy))
                        {
                            ((╥ModelBaseß)entity.Entity).DateCreated = ☼DateTimeß.Now;
                            ((╥ModelBaseß)entity.Entity).CreatedById = userId;
                            ((╥ModelBaseß)entity.Entity).CreatedBy = name;
                        }

                        if (entity.State == ◙EntityStateß.Modified)
                        {
                            ((╥ModelBaseß)entity.Entity).DateModified = ☼DateTimeß.Now;
                            ((╥ModelBaseß)entity.Entity).ModifiedById = userId;
                            ((╥ModelBaseß)entity.Entity).ModifiedBy = name;
                        }
                    }
                    else if (entity.Entity is ╥ApplicationUserß)
                    {
                        if (entity.State == ◙EntityStateß.Added || string.IsNullOrEmpty(((╥ApplicationUserß)entity.Entity).CreatedBy))
                        {
                            ((╥ApplicationUserß)entity.Entity).DateCreated = ☼DateTimeß.Now;
                            ((╥ApplicationUserß)entity.Entity).CreatedById = userId;
                            ((╥ApplicationUserß)entity.Entity).CreatedBy = name;
                        }

                        if (entity.State == ◙EntityStateß.Modified)
                        {
                            ((╥ApplicationUserß)entity.Entity).DateModified = ☼DateTimeß.Now;
                            ((╥ApplicationUserß)entity.Entity).ModifiedById = userId;
                            ((╥ApplicationUserß)entity.Entity).ModifiedBy = name;
                        }
                    }
                }
            }
        }

In the code above I’m overriding SaveChanges and then grabbing the entities from the ChangeTracker based on whether or not it is of type ApplicationUser or it inherits from ModelBase. We’re then grabbing info about the calling user from the claims using the injected HttpContextAccessor. From there we go through and check the EntityState and appropriately set our audit columns based on that with the audit information we collected earlier. Simple and effective and you never have to worry about settings these columns ever again once this is set up.

Query Filters

Query filters are even easier since all you have to do is call one of Entity Framework’s extension methods when you configure the entity in the context. In my case, I created one method that all entities inheriting ModelBase call. Within this method I set the query filter and the rest of the generic configuration on the entity.


        private void BuildModelBaseConfiguration(╥ModelBuilderß builder) where T : ╥ModelBaseß
        {
            builder.Entity().HasKey(m => m.Id);
            builder.Entity().Property(p => p.Id).HasDefaultValueSql(╥SqlConstantsß.NewSequentialId);
            builder.Entity().Property(m => m.DateCreated).HasDefaultValueSql(╥SqlConstantsß.SysDateTime)
                .HasColumnType(╥SqlConstantsß.DateTime2);
            builder.Entity().Property(m => m.DateModified)
                .HasColumnType(╥SqlConstantsß.DateTime2);
            builder.Entity().Property(m => m.ModifiedBy)
                .HasMaxLength(400);
            builder.Entity().Property(m => m.CreatedBy)
                .HasMaxLength(400);
            builder.Entity().HasQueryFilter(m => !m.IsDeleted);
            builder.Entity().HasOne(m => m.CreatedByUser).WithMany()
                .OnDelete(◙DeleteBehaviorß.Restrict)
                .HasForeignKey(k => k.CreatedById);
            builder.Entity().HasOne(m => m.ModifiedByUser).WithMany()
                .OnDelete(◙DeleteBehaviorß.Restrict)
                .HasForeignKey(k => k.ModifiedById);
        }

The key line in here is the builder.Entity().HasQueryFilter(m => !m.IsDeleted). This will cause all queries on these entities to only pull back records that do not have IsDeleted = true. This can be overridden manually in a query by calling IgnoreQueryFilters();

Sean Leitzinger

Solutions Architect at Edgeside Solutions
.NET and C# aficionado with an interest in architecture, patterns, practices, and more. Microsoft fanatic.

Latest posts by Sean Leitzinger (see all)

Leave a Reply

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