February 18, 2020

Filter user records using Global Query Filters

Hey there 👋 A quick interruption before you start reading.
I've been working on a new project called StellarAdmin that helps ASP.NET Core developers like you rapidly create admin screens for your application's Admin and Support users.
If this sounds like something that will save you time on your projects, please do me a favour and check it out🙏🙏🙏

Introduction

In my last blog post, I demonstrated how you could make use of extension methods to filter EF Core entities belonging to the currently logged-in user.

That technique works great in encapsulating the logic to filter the data, but it still requires developers to call the extension method every time they want to filter data. Failing to call the method can have disastrous effects as you can accidentally make one user’s information visible to someone else.

One way to ensure that you always filter out data belonging to the current user is by making use of Global Query Filter. One of the scenarios described in the documentation for this feature is a multi-tenant scenario, which is very similar to the scenario I demonstrate in this blog post.

Creating a Global Query Filter

Before starting, I suggest you read through the Global Query Filter documentation to familiarise yourself with this feature.

Remember from the previous blog post that we are dealing with the following entity and database context definition:

public class RecordedWeight
{
    public int Id { get; set; }

    [Required]
    public DateTime Date { get; set; }

    [Required]
    public decimal Weight { get; set; }

    public string UserId { get; set; }

    [Required]
    public IdentityUser User { get; set; }
}

public class ApplicationDbContext : IdentityDbContext
{
    public DbSet<RecordedWeight> RecordedWeights { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
}

To filter records for the current user, we need to access the user from the HttpContext. The first change is to inject IHttpContextAccessor into the database context:

public class ApplicationDbContext : IdentityDbContext
{
    private readonly IHttpContextAccessor _httpContextAccessor;

    public DbSet<RecordedWeight> RecordedWeights { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options, IHttpContextAccessor httpContextAccessor)
        : base(options)
    {
        _httpContextAccessor = httpContextAccessor;
    }
}

Next, we define a filter that gets the current user’s information from the HttpContext and uses that to filter all recorded weight entries to only display those belonging to the current user:

public class ApplicationDbContext : IdentityDbContext
{
    // Some code omitted for brevity...

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<RecordedWeight>()
            .HasQueryFilter(weight => weight.UserId == _httpContextAccessor.HttpContext.User.GetUserId());
    }
}

Remember from my previous blog posts that GetUserId() is an extension method that gets the value of the NameIdentifier claim - which is the current user’s ID when using ASP.NET Identity:

public static class ClaimsPrincipalExtensions
{
    public static string GetUserId(this ClaimsPrincipal principal)
    {
        return principal.FindFirstValue(ClaimTypes.NameIdentifier);
    }
}

Confirming this works

To confirm that this works, we can look at the actual SQL queries sent to the database from EF Core. To do that, we first need to enable more verbose logging for the application. In the application settings for my development environment (appsettings.Development.json), I set the log level for “Microsoft” to “Information”.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Information",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  }
}

This logs the SQL queries to the log file, but it hides parameter values, so we cannot confirm that the correct user ID is passed to the query. To enable logging of query parameters, you need to call EnableSensitiveDataLogging() when configuring the database context:

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<ApplicationDbContext>(options =>
        {
            options.UseSqlite(
                Configuration.GetConnectionString("DefaultConnection"));
             options.EnableSensitiveDataLogging();
        });

        // Code omitted for brevity...
    }
}

Now, when I run the application, we can see the user ID passed to the query:

Query parameters logged

And when I log in with a different user, you can see that the correct parameter value is passed for the second user:

Query parameters logged for the second user

Conclusion

In this blog post, I demonstrated how you could make use of Global Query Filters in EF Core to automatically filter entities to only display the data belonging to the currently logged-in user.

You can find a sample application demonstrating this at https://github.com/jerriepelser-blog/filter-user-records-using-efcore-filters.

PS: If you need assistance on any of your ASP.NET Core projects, I am available for hire for freelance work.