I have an asp.net core Web API that servers multiple tenants. I have a single PostgreSQL database for all tenants. Tenants are separated by a TenantId column which is applied using EF Cores Query Filter. I am using a code-first approach with migrations. All of this works as expected.

However, I am stumped on how to best manage sequences per tenant. For example, each tenant can have a list of jobs, each job taking the next incremental value. How would I implement this so that each tenant has their own set of incremental job numbers?

Ideally, I do not want to have to manage a sequence per tenant, as that would soon get unmanageable.

Is there a 'best practice' way of achieving this using EF core? or would I have to do something manually/outside of the migration?

Thanks for any help/ideas.

CodePudding user response：

I tend to create my own Sequences table that's managed with the DbContext like any other entity. Then I create some ISequenceService that manages getting the next sequence value. Lastly, I implement an IStartupFilter that would insert all the sequences the app needs for each tenant. This pattern would fit nicely with your discriminator column pattern.

My sequence object would look something like:

public class Sequence: BaseEntity {
    public string name {get;set;}
    public int nextValue {get;set;} = 1;
    public string prefix {get;set;}
    public string postfix {get;set;}
}

I prefer this route because it provides a more database agnostic implementation. It also allows for more tenant customization around sequences, such as where each tenant wants their sequences to start, their pre/post-fix or whatever else your users can dream up.

A downside to this approach is with concurrency. If you have two users trying to increment the sequence at the same time, it might be possible to end up with dupe sequence values. An actual database sequence will manage this problem better, but it's not impossible to do with EF.

If you're interested in this approach, check out the docs on handling concurrency.