How would I go about implementing something like Entity Framework's Global Filter - https://docs.microsoft.com/en-us/ef/core/querying/filters - specifically for handling multiple tenants in a single database schema?

Ideally I'd be able to inspect the entities in the query and see if they have an interface like IMustHaveTenant and either throw an exception if there is no predicate on a named column/property like 'TenantId', or better still automatically add a predicate on TenantId, using the current tenant id from some context somewhere.

We didn't implement a mechanism for this in particular (appending fixed filters based on entity types) because it's very brittle and in practice cumbersome to use: data of multiple distinct sets is merged together to one big set with multiple views on top based on predefined predicates.

Our runtime has multi-tenant support through schema-per-tenant or catalog-per-tenant, through catalog or schema overwriting at runtime (per call, which can be done automatically), which has the advantage that the distinct sets of data (one per tenant) aren't merged together and it's therefore not brittle nor error prone.

If you really want to proceed and have all instances for all tenants in one schema, for Adapter, there are several ways to append your predicates to queries (e.g. override FetchEntityCollection()), but it's not that simple, as complex queries where entities are ending up deep inside a query could be cumbersome to adjust (as you have to append the filter deep inside the query).

There are ways to append a filter to a linq or queryspec query on an entity. E.g.

var md = new LinqMetaData(adapter);
var q = md.Customer.Where(c=>c.Country == myCountry);

here, md.Customer is an IQueryable<Customer>. You can create your own class which either derives from LinqMetaData or wraps it, and where you expose Customer but also append the tenantid filter. So yourClass.Customer is really md.Customer.Where(c=>c.TenantId==myTenantId).

However this isn't 100% failsafe. Every filter you append which e.g. does a contains or an in query has to filter on tenantid.

The same goes for QuerySpec, which has a QueryFactory class (generated) which exposes for every entity an EntityQuery<T>, to which you can append a where clause. This too isn't 100% failsafe.

For deletes and updates, you can use a ConcurrentPredicateFactory and auto-append a predicate based on the tenantid. (ref

As the EF Core documentation also says, it's not really great, and can lead to unforeseen situations. Our runtime has a way to directly update/delete entities too, you have to make sure the predicates specified there are also filtering on tenants.

Not sure if you can change your design still, but I'd opt for the way more simpler setup of 1 schema or catalog per tenant. This is in use by many customers of ours without problems.

Thank you for the informative reply!

We currently run different Azure SQL databases (catalogs), one for each tenant. They're accessed by a group of services (kubernetes pods) per tenant. I was just looking for ideas about how we might bring them all together if required in the future. We've got around 100 tables with some pretty complicated relations, filtering , paging etc. so I can see it's going to be a big task to get tenants sharing tables! More thinking required...but I think the schema-per-tenant method looks good.