EF Core Multi-Tenancy: Query Filter

Sponsor: Using RabbitMQ or Azure Service Bus in your .NET systems? Well, you could just use their SDKs and roll your own serialization, routing, outbox, retries, and telemetry. I mean, seriously, how hard could it be?

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

	public class Customer
	{
	public Guid TenantId { get; set; }
	public int CustomerId { get; set; }
	public string Name { get; set; }
	}

view raw Customer.cs hosted with ❤ by GitHub

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.

	public class MyDbContext : DbContext
	{
	public Guid TenantId { get; }
	public string Connection { get; }
	public DbSet<Customer> Customers { get; set; }

	public MyDbContext(Guid tenantId, string connection)
	{
	TenantId = tenantId;
	Connection = connection;

	this.Filter<Customer>(x => x.Where(q => q.TenantId == TenantId));
	}

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
	optionsBuilder.UseSqlServer(Connection);
	}
	}

view raw DbContext.cs hosted with ❤ by GitHub

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.

	class Program
	{

	static void Main(string[] args)
	{
	var tenant1 = Guid.NewGuid();
	var tenant2 = Guid.NewGuid();
	int customerId = 1;

	Seed(tenant1, customerId, "Derek Comartin");
	Seed(tenant2, customerId, "CodeOpinion.com");

	using (var db = DbFactory(tenant1))
	{
	var customer = db.Customers.Single(x => x.CustomerId == customerId);
	Console.WriteLine($"Hello {customer.Name}");
	}

	using (var db = DbFactory(tenant2))
	{
	var customer = db.Customers.Single(x => x.CustomerId == customerId);
	Console.WriteLine($"Hello {customer.Name}");
	}

	Console.ReadKey();
	}

	private static MyDbContext DbFactory(Guid tenantId)
	{
	var connection = @"Server=(localdb)\mssqllocaldb;Database=EFCoreMultiTenant;Trusted_Connection=True;";
	return new MyDbContext(tenantId, connection);
	}

	static void Seed(Guid tenantId, int customerId, string name)
	{
	using (var db = DbFactory(tenantId))
	{
	db.Customers.Add(new Customer
	{
	CustomerId = customerId,
	TenantId = tenantId,
	Name = name
	});
	db.SaveChanges();
	}
	}
	}

view raw program.cs hosted with ❤ by GitHub

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.

Follow @CodeOpinion