Unique index on 2 FKs

[fdonnet]

I cannot find a way to place a unique index on 2 FKs

        public void Configure(EntityTypeBuilder<UserRoleByTenant> builder)
        {
            builder.ToTable("users_roles_by_tenant");

            builder.HasIndex(a => new { a.UserTenantId, a.RoleId })
            .IsUnique();

            builder
            .HasOne(e => e.UserTenant)
            .WithMany()
            .HasForeignKey(e => e.UserTenantId).OnDelete(DeleteBehavior.Cascade)
            .IsRequired();

            builder
           .HasOne(e => e.Role)
           .WithMany()
           .HasForeignKey(e => e.RoleId).OnDelete(DeleteBehavior.Cascade)
           .IsRequired();

        }

This code generate 2 unique indexes not an index with 2 fields in it ?

Maybe I m doing someting wrong but it seems correct.

It's very strange.

EDIT : FKs are Guid

[roji]

Can you please post a minimal, runnable repro for this?

[fdonnet]

yes, you can try here: https://github.com/fdonnet/postgres_ef

For the "UserRoleByTenant" model configured in "UserRoleByTenantConfiguration" it generates something like that

CREATE INDEX IF NOT EXISTS ix_user_roles_by_tenants_role_id
    ON public.user_roles_by_tenants USING btree
    (role_id ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: ix_user_roles_by_tenants_user_tenant_id

-- DROP INDEX IF EXISTS public.ix_user_roles_by_tenants_user_tenant_id;

CREATE INDEX IF NOT EXISTS ix_user_roles_by_tenants_user_tenant_id
    ON public.user_roles_by_tenants USING btree
    (user_tenant_id ASC NULLS LAST)
    TABLESPACE pg_default;

2 possible causes:

• it's an associative entity/table of an already associative entity/table
• I use EfCore NammingConvention nugget, to be compatible with dapper and for ez home made queries.

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)
                .UseSnakeCaseNamingConvention();
        }

Hope it helps.

[fdonnet]

For the UserTenant model, it has another behavior. It generates:

• one unique index on tenant_id
• and one other unique index with the 2 fields "user_id" and "tenant_id" Fks. (correct index)

For the UserRoleByTenant, it generates 2 unique indexes for both FKs.

Not the same behavior. Something trigger weird things when FKs are implicated.

In the config, I tried to put the requiered annotation on the Property itself or on the FK declaration, or both. It produces the same results.

EDIT: For entity/table Role, it seems to work, even the FK is nullable (Code field + tenant_id with tenant_id can be null). (tenant_id == null => base/template roles not linked to a tenant)

[roji]

@fdonnet in your repro project, UserRoleByTenantConfiguration doesn't seem to be called at all... Here's your DbContext's OnModelCreating (code:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{

	//Configure
	new TenantConfiguration().Configure(modelBuilder.Entity<Tenant>());
	new UserConfiguration().Configure(modelBuilder.Entity<User>());
	new UserTenantConfiguration().Configure(modelBuilder.Entity<UserTenant>());
	new RoleConfiguration().Configure(modelBuilder.Entity<Role>());

	base.OnModelCreating(modelBuilder);
}

[roji]

As an aside, when submitting a code repro, please take the time to make it minimal, i.e. removing anything that isn't strictly necessary to reproduce the problem - this makes it much easier for maintainers to understand things and focus on what's important (keep in mind that we generally get dozens of issues every day).

[fdonnet]

Oh, big sry, really confused about that... I checked a lot of things before submitting the issue but I didn't even think to look at my configuration calls. Thx for the detection and sry again.