dotnet / dotnet/efcore

EF Adding Alternate Key when Unique Clustered Index already Exists

Open
#28,189 2 comments 3 reactions 0 assignees View on GitHub
area-model-building customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

### Issue Summary
According to the [Microsoft Documents](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.builders.referencereferencebuilder-2.hasprincipalkey?view=efcore-6.0) for `HasPrincipalKey` a new unique constraint should only be introduced if properties do not already have a unique constraint in place.

> Configures the unique property(s) that this relationship targets. Typically you would only call this method if you want to use a property(s) other than the primary key as the principal property(s). If the specified property(s) is not already a unique constraint (or the primary key) then a new unique constraint will be introduced.

However, if I already have a unique clustered index in place on those columns, an additional key is being added in the migration definition. This is a duplicate constraint as the Unique Clustered Index should be enough to handle this use-case and the AlternateKey (`AK_Entitiy1s_TenantId_Entity1Id`) should not be added

### DB Context Definition

```C#
public class Tenant
{
public Guid TenantId { get; set; }
public string Name { get; set; }

public IList Entity1s { get; set; }
public IList Entity2s { get; set; }
}

public class Entity1
{
public Guid Entity1Id { get; set; }
public Guid TenantId { get; set; }

public Tenant Tenant { get; set; }
public IList Entity2s { get; set; }
}

public class Entity2
{
public Guid Entity2Id { get; set; }
public Guid Entity1Id { get; set; }
public Guid TenantId { get; set; }

public Tenant Tenant { get; set; }
public Entity1 Entity1 { get; set; }
}

public class Context : DbContext
{
public DbSet Tenants { get; set; }
public DbSet Entitiy1s { get; set; }
public DbSet Entitiy2s { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("myConnectionString");
base.OnConfiguring(optionsBuilder);

}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasKey(x => x.TenantId)
.IsClustered();

modelBuilder.Entity()
.HasKey(x => x.Entity1Id)
.IsClustered(false);

modelBuilder.Entity()
.HasIndex(x=> new {x.TenantId, x.Entity1Id})
.IsUnique()
.IsClustered();

modelBuilder.Entity()
.HasKey(x => x.Entity2Id)
.IsClustered(false);

modelBuilder.Entity()
.HasIndex(x => new { x.TenantId, x.Entity2Id })
.IsUnique()
.IsClustered();

modelBuilder.Entity()
.HasOne(x => x.Tenant)
.WithMany(x => x.Entity1s);

modelBuilder.Entity()
.HasOne(x => x.Tenant)
.WithMany(x => x.Entity2s);

modelBuilder.Entity()
.HasOne(x => x.Entity1)
.WithMany(x => x.Entity2s)
.HasForeignKey(x=> new {x.TenantId, x.Entity1Id})
.HasPrincipalKey(x=> new {x.TenantId, x.Entity1Id});
}

}
```
### Migration Code

See Comment in code for where I have the issue

```C#
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Tenants",
columns: table => new
{
TenantId = table.Column(type: "uniqueidentifier", nullable: false),
Name = table.Column(type: "nvarchar(max)", nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Tenants", x => x.TenantId)
.Annotation("SqlServer:Clustered", true);
});

migrationBuilder.CreateTable(
name: "Entitiy1s",
columns: table => new
{
Entity1Id = table.Column(type: "uniqueidentifier", nullable: false),
TenantId = table.Column(type: "uniqueidentifier", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Entitiy1s", x => x.Entity1Id)
.Annotation("SqlServer:Clustered", false);
table.UniqueConstraint("AK_Entitiy1s_TenantId_Entity1Id", x => new { x.TenantId, x.Entity1Id }); // Alternate Key being Added even though we have a Unique Clustered Index already defined. This is a duplicate constraint
table.ForeignKey(
name: "FK_Entitiy1s_Tenants_TenantId",
column: x => x.TenantId,
principalTable: "Tenants",
principalColumn: "TenantId",
onDelete: ReferentialAction.Cascade);
});

migrationBuilder.CreateTable(
name: "Entitiy2s",
columns: table => new
{
Entity2Id = table.Column(type: "uniqueidentifier", nullable: false),
Entity1Id = table.Column(type: "uniqueidentifier", nullable: false),
TenantId = table.Column(type: "uniqueidentifier", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Entitiy2s", x => x.Entity2Id)
.Annotation("SqlServer:Clustered", false);
table.ForeignKey(
name: "FK_Entitiy2s_Entitiy1s_TenantId_Entity1Id",
columns: x => new { x.TenantId, x.Entity1Id },
principalTable: "Entitiy1s",
principalColumns: new[] { "TenantId", "Entity1Id" },
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_Entitiy2s_Tenants_TenantId",
column: x => x.TenantId,
principalTable: "Tenants",
principalColumn: "TenantId",
onDelete: ReferentialAction.Cascade);
});

migrationBuilder.CreateIndex(
name: "IX_Entitiy1s_TenantId_Entity1Id",
table: "Entitiy1s",
columns: new[] { "TenantId", "Entity1Id" },
unique: true)
.Annotation("SqlServer:Clustered", true);

migrationBuilder.CreateIndex(
name: "IX_Entitiy2s_TenantId_Entity1Id",
table: "Entitiy2s",
columns: new[] { "TenantId", "Entity1Id" });

migrationBuilder.CreateIndex(
name: "IX_Entitiy2s_TenantId_Entity2Id",
table: "Entitiy2s",
columns: new[] { "TenantId", "Entity2Id" },
unique: true)
.Annotation("SqlServer:Clustered", true);
}
```

### Include provider and version information

EF Core version: 6.4.4
EF Core Design Version: 6.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6
Operating system: Windows 10
IDE: Visual Studio 2022 17.1.4

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.