Open Closed

How to Generate a sequences for each tenant? #6278


User avatar
0
hussein created
  • ABP Framework version: v8.0.0-rc.2
  • UI Type: Angular / **MVC **/ Blazor WASM / Blazor Server
  • Database System: EF Core (SQL Server, Oracle, MySQL, PostgreSQL, etc..) / MongoDB
  • Tiered (for MVC) or Auth Server Separated (for Angular): yes/no
  • Exception message and full stack trace:
  • Steps to reproduce the issue:

how we can create multiple sequences (One for each tenant)? for eaxample:

| Tenant |AccountNo | | --- | --- | | TenantA | 1000001 |customerX| | TenantA| 1000002 | customerX| | TenantB| 1000001 | customerX| | TenantB | 1000002 | CustomerX |

if we use the following code , it will generate a same sequence for all tenants b.Property(o => o.AccountNo) .HasDefaultValueSql("NEXT VALUE FOR AccountNo").ValueGeneratedOnAdd(); builder.HasSequence<long>("AccountNo").StartsAt(1000001).IncrementsBy(1);


7 Answer(s)
  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Hi,

    What database are you using?

    Note that the specific SQL used to generate a value from a sequence is database-specific; the above example works on SQL Server but will fail on other databases. Consult your specific database's documentation for more information.

    See: https://learn.microsoft.com/en-us/ef/core/modeling/sequences#basic-usage

  • User Avatar
    0
    hussein created

    Hi,

    What database are you using? ** SQL Server**

    Note that the specific SQL used to generate a value from a sequence is database-specific; the above example works on SQL Server but will fail on other databases. Consult your specific database's documentation for more information.

    See: https://learn.microsoft.com/en-us/ef/core/modeling/sequences#basic-usage

    noting i use SQL server , my question here is there any way to achieve the required sequence per tenant?

  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    **YourProjectName**EfCoreEntityExtensionMappings.cs

    ObjectExtensionManager.Instance
        .MapEfCoreProperty<Tenant, int>(
            "AccountNo",
            (entityBuilder, propertyBuilder) =>
            {
                propertyBuilder.HasDefaultValueSql("NEXT VALUE FOR AccountNo");
            }
        );
    

    DbContext

    builder.HasSequence<int>("AccountNo")
            .StartsAt(1000001).IncrementsBy(1);
    

  • User Avatar
    0
    hussein created

    first thx for your replay, but it seems you didn't get my point, the application is multi-tenancy according to Abp.io's practice
    so we have a tenant for each client (shared db), now we have the table name "Account", and we should create Sequences for this table in the field "AccountNo".

    the above code will generate the same sequence for all clients, but it should be for each client(tenant) sequence

  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Sorry for the misunderstanding

    Can you share a test project? my email is shiwei.liang@volosoft.com

  • User Avatar
    0
    hussein created

    sorry I don't have the privilege to send the project code,simpl just add a new table as below:

    public class Person: FullAuditedAggregateRoot<Guid>, IMultiTenant
     {
         public virtual Guid? TenantId { get; set; }
         public virtual long PersonNo { get; set; }
         public virtual string Name { get; set; }
         public virtual bool IsCitizen { get; set; }
         [CanBeNull]
         public virtual string? NationalNo { get; set; }
         protected Person()
         {
         }
     }
    

    the configuration in the dbcontext as below:

    builder.Entity<Person>(b =>
    {
        b.ToTable(BstCoreDbProperties.DbTablePrefix + "Persons", BstCoreDbProperties.DbSchema);
        b.ConfigureByConvention();
        b.Property(x => x.TenantId).HasColumnName(nameof(Person.TenantId));
        b.Property(x => x.Name).HasColumnName(nameof(Person.Name1)).IsRequired();
        b.HasIndex(x => new { x.TenantId, x.PersonNo }).IsUnique();
        b.HasIndex(x => new { x.TenantId, x.Name }).IsUnique();
        b.HasIndex(x => new { x.TenantId, x.NationalNo });//.IsUnique();
            b.Property(o => o.PersonNo)
             .HasDefaultValueSql("NEXT VALUE FOR PersonNo").ValueGeneratedOnAdd();
    });
    builder.HasSequence<long>("PersonNo").StartsAt(100000001).IncrementsBy(1);`
    

    once you created this table, make a test to add two records with different tenant names, and check the generated sequence no (PersonNo) , it should be 100000001 for tenant#1, and 100000001 for tenant#2 but the output is 100000001 for tenant#1 and 100000002 for tenant#2

  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Hi,

    Ok, I understand now.

    It's not related to ABP, after my investigation, there seems to be no way to make it work, because it will not consider multi-tenants.

    You may need something like this: https://stackoverflow.com/questions/73937191/independent-tenant-sequences-in-multi-tenant-single-db

Made with ❤️ on ABP v9.1.0-preview. Updated on December 10, 2024, 06:38