Composite alternate unique key

[DataTowerSystems]

Hi all,
I have a case which I need to composite an alternate unique key
for example I need to combine the Brand entity alternate key (Name and TenantId) because i want the name for each brand to be unique in its tenant
I added the TenantId property to the Brand entity but when I am saving the new row the database gives me an error which say that the AK must not be null because the TenantId is not assigned.
Is this case possible to achieve, if so Can any one kindly please show mw how to solve this case.
Thank you very much for your help.

[DataTowerSystems]

Hi all, I have a case which I need to composite an alternate unique key for example I need to combine the Brand entity alternate key (Name and TenantId) because i want the name for each brand to be unique in its tenant I added the TenantId property to the Brand entity but when I am saving the new row the database gives me an error which say that the AK must not be null because the TenantId is not assigned. Is this case possible to achieve, if so Can any one kindly please show mw how to solve this case. Thank you very much for your help.

Please any clue on this issue.
Thanks a lot.

[fretje]

You're gonna have to be more specific... maybe add some code snippets, actual error messages?

The way brands are implemented now (and products as well for that matter), they already have a unique name per tenant using fluent validation rules:

    public CreateBrandRequestValidator(IReadRepository<Brand> repository, IStringLocalizer<CreateBrandRequestValidator> T) =>
        RuleFor(p => p.Name)
            .NotEmpty()
            .MaximumLength(75)
            .MustAsync(async (name, ct) => await repository.GetBySpecAsync(new BrandByNameSpec(name), ct) is null)
                .WithMessage((_, name) => T["Brand {0} already Exists.", name]);

The TenantId property is added and handled (using a queryfilter) automatically by finbuckle.

Finbuckle does have some methods "AdjustKey", "AdjustIndexes", ... (see https://www.finbuckle.com/MultiTenant/Docs/v6.7.2/EFCore under "Keys and Indexes"), which might be what you're after?

[DataTowerSystems]

Firstly thank you very much for your reply and sorry for being not specific.
Let me clarify my case
I have 2 tables (Entities) Product and Category
The product in domain:

public class Product : AuditableEntity, IAggregateRoot
{
    public string ProductCode { get; set; } = string.Empty;
    public string ProductDescription { get; set; } = string.Empty;
    public string ProductCategoryCode { get; set; } = string.Empty;
    public Category Category { get; set; } = default!;

    [NotMapped]
    public string? TenantId { get; set; }

    public Product ClearImagePath()
    {
        ImagePath = string.Empty;
        return this;
    }
}

And the Category in Domain:

public class Category : AuditableEntity, IAggregateRoot
{
    public string CategoryCode { get; set; } = string.Empty;
    public string CategoryDescriptionArabic { get; set; } = string.Empty;
    public string CategoryDescriptionEnglish { get; set; } = string.Empty;
    [NotMapped]
    public string? TenantId { get; set; }
    public ICollection<Product> Products { get; set; } = default!;
}

And in Infrastructure/ Persistence/ Configuration
The fluent API

public class CategoryConfig : IEntityTypeConfiguration<Category>
{
    public void Configure(EntityTypeBuilder<Category> builder)
    {
        builder.IsMultiTenant();

        builder
            .HasIndex(b => new { b.CategoryCode, b.TenantId })
            .IsUnique(true)
            .HasDatabaseName("IX_CategoryCode_TenantId");

        builder
            .Property(b => b.CategoryCode)
            .IsUnicode(false)
            .HasMaxLength(50)
     }
}

public class ProductConfig : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.IsMultiTenant();

        builder
        .HasIndex(b => new { b.ProductCode, b.TenantId })
        .IsUnique(true);

       <---- This is the relation between the Product and the Category ------->
          **builder
            .HasOne(b => b.Category)
            .WithMany(b => b.Products)
            .HasForeignKey(b => new { b.ProductCategoryCode, b.TenantId })
            .HasPrincipalKey(b => new { b.CategoryCode, b.TenantId })
            .IsRequired();**
       <---- This is the relation between the Product and the Category ------->
    }
}

And this is how it looks in the Database

Until now everything is Ok, the problem is when I create new Category and try to save it I got the following error:
“Unable to track an entity of type 'Category' because alternate key property 'TenantId' is null. If the alternate key is not used in a relationship, then consider using a unique index instead. Unique indexes may contain nulls, while alternate keys may not.”

I hope this is clear now.
Can you please help to solve this case error.
Again Thank you very much.

[fretje]

Hmm... Pretty sure you don't need the [NotMapped] attribute on TenantId... Not sure that's the only problem though... How exactly are you creating the new category? You might have to fill out the tenantId property yourself. In my project, I have created a "CurrentTenantService" (in infrastructure) and a corresponding "ICurrentTenantService" (in application) to be able to retrieve the current TenantId from the Application project (as FSHTenantInfo is a infrastructure-only thing) exactly for a similar purpose (where I bulk insert a lot of records at once into a table that also needs a TenantId set for each record):

public interface ICurrentTenantService
{
    string Id { get; }
    string Identifier { get; }
}

internal class CurrentTenantService : ICurrentTenantService
{
    private readonly FSHTenantInfo _currentTenant;

    public CurrentTenantService(FSHTenantInfo currentTenant) => _currentTenant = currentTenant;

    public string Id => _currentTenant.Id;
    public string Identifier => _currentTenant.Identifier;
}

Also, have you checked the "AdjustKey" and "AdjustIndexes" methods from finbuckle... they might do what you need wrt. adding the TenantId to the key and/or index of the entity in question.

[DataTowerSystems]

Dear fretje,
Thank you so much for your valuable help.
I followed your steps as

public interface ICurrentTenantService
{
    string Id { get; }
    string Identifier { get; }
}

internal class CurrentTenantService : ICurrentTenantService
{
    private readonly FSHTenantInfo _currentTenant;

    public CurrentTenantService(FSHTenantInfo currentTenant) => _currentTenant = currentTenant;

    public string Id => _currentTenant.Id;
    public string Identifier => _currentTenant.Identifier;
}

And then in the CreateCategoryRequest class in the application I added the TenantId manually like

category.TenantId = _currentTenantService.Id;
await _repository.AddAsync(category, cancellationToken);

just before I call the AddAsync method and everything works perfect and smoothly.
Again thank you very much for your co-operate.
Your help is very highly appreciated and wish you all the best.
Best Regards