Entity framework core multi tenant : auto increment column based in another column value

Question

Using entity framework core 6 with sample class

public class Sample
{
  [Key]
  public long Id { get; set; }
  public long TenantId { get; set; }
  public long PartitionId { get; set; } 
}

I need to make auto increment of partition id based in brand id.

First I add index mapper between tenantId and partitionId to make sure never duplicated than i used in partitionId ValueGeneratedOnAdd() function, my missing part how i can make auto generation of partitionId foreach tenantId in sequence.

        modelBuilder.Entity<Sample>(builder =>
        {

            // enable unique partion id based in tenant level
            builder.HasIndex(r => new { r.PartitionId, r.TenantId }).HasDatabaseName("IX_Sample_PartitionId").IsUnique();

            builder.ToTable("Sample").Property(p => p.Id).HasColumnOrder(0);

            builder.ToTable("Sample").Property(p => p.TenantId ).HasColumnOrder(1);

            // create auto increment base in brand id
            builder.ToTable("Sample").Property(p => p.PartitionId).ValueGeneratedOnAdd().HasColumnOrder(2);

       });

Required result should be like this

Id | TenantId | PartitionId 
1  | 1        | 1
2  | 1        | 2
3  | 2        | 1
4  | 2        | 2

Answer 1

You can achieve this by having a difference sequence for each tenant and you script it as part of the tenant onboarding.

ex: When you create a tenant, you run the following script:

CREATE SEQUENCE [dbo].[Tenant{0}_PartionIdSequence] AS [int] START WITH 1 INCREMENT BY 1

When you insert ar ecord to the sample table, you will have to call this script tp get the next value for PartitionId

SELECT NEXT VALUE FOR dbo.[Tenant{0}_PartionIdSequence]

This way every tenant should have its own sequence

Answer 2

Not working, sequence has been created please note i'm using Postgres SQL, SELECT NEXT VALUE FOR statement not correct syntax in my case.

Please find below my changes.

   // create sequence for partition id and  map it in tenant level
   modelBuilder.HasSequence<long>("Tenant{0}_PartitionId_seq")
               .StartsAt(1)
               .IncrementsBy(1);

   modelBuilder.Entity<Sample>(builder =>
   {
        // enable unique partion id based in tenant level
        builder.HasIndex(r => new { r.PartitionId, r.TenantId })
               .HasDatabaseName("IX_Sample_PartitionId")
               .IsUnique();

       builder.ToTable("Sample").Property(p => p.Id).HasColumnOrder(0);
       builder.ToTable("Sample").Property(p => p.TenantId).HasColumnOrder(1);

       // mapping sequence table which consider tenant id
       builder.ToTable("Sample").Property(p => p.PartitionId)
             .HasDefaultValueSql("nextval('\"Tenant{0}_PartitionId_seq\"')")
             .HasColumnOrder(2);
   });