Skip to content

EF Core Multi-Tenancy: Query Filter

Sponsor: Do you build complex software systems? See how NServiceBus makes it easier to design, build, and manage software systems that use message queues to achieve loose coupling. Get started for free.

Learn more about Software Architecture & Design.
Join thousands of developers getting weekly updates to increase your understanding of software architecture and design concepts.


EF Core Multi-tenancy: Query FilterThere are many different ways to handle multi-tenancy.  This blog post will cover one approach to EF Core Multi-Tenancy that will work if you are using a shared database approach, meaning you use the same database for multiple tenants, that are disambiguated using tenant ID column. If you want more details on Multi-Tenancy, check out the Microsoft Docs on the topic, related to designing multi-tenant apps using Azure SQL Database.

Entity

Let’s jump right into some sample code of a simple Entity that represents a customer.  Notice the TenantId.

Filter

The approach we are going to use is to pre-filter any DbSet in our DbContext.  We can do this by using the EntityFramework-Plus package. It provides us the ability to specify per context instance how to pre-filter our DbSets.  We can do this by adding our TenantId as a ctor parameter and use the Filter<T> extension method.

Example Usage

Here’s a small console app that adds two new customer records both with the same CustomerId = 1.  When creating the MyDbContext, we the TenantId we want to pre-filter on.

Source

All the source code for my demo is available on GitHub if you want to try it yourself. If anyone has any other suggestions or recommendations about blog topics, please leave comment or let me know on twitter.

8 thoughts on “EF Core Multi-Tenancy: Query Filter”

      1. “`
        var summary =
        (from customer in db.Customers
        select new {
        customerName = customer.Name,
        numberOfOrders = customer.Orders.Count()
        })
        .ToList()
        “`

        Is the filter applied when I call `db.Customers` in the LINQ query? I would assume so. Also, what about?

        “`
        var summary =
        (from order in db.Orders
        select new {
        customerName = order.Customer.Name,
        total = order.Total,
        tax = order.Tax
        })
        .ToList()
        “`

        1. Correct, it’s applied when calling the db.Customers or db.Orders, whichever you have the filter specified for.

          SQL produced is:

          SELECT [o].[OrderId], [o.Customer].[Name] AS [customerName]
          FROM [Orders] AS [o]
          LEFT JOIN [Customers] AS [o.Customer] ON [o].[CustomerId] = [o.Customer].[CustomerId]
          WHERE [o].[TenantId] = @__TenantId_0

  1. Had forgotten about the EF Plus package. Thanks for that! Lots of good stuff there.

    In this scenario – any benefit of using that package over EF Core 2’s HasQueryFilter option?

    1. Wow! I never even knew this existed. Thanks for letting me know. I was just checking it out and it looks like this is equivalent to EF Plus package. Basically add the tenant filter in the OnModelCreating.

  2. How does this dffer from using EFCore’s modelBuilder:

    builder.Entity(entity =>
    {
    entity.HasQueryFilter(x => x.Where(q => q.TenantId == TenantId));
    });

Leave a Reply

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