dotnet / dotnet/efcore

HasOne/WithOne in TPH with shared column should create multiple indexes

Open
#30,216 4 comments 0 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

### Include your code
Entities
```csharp
public class Api{
public int Id {get; set;}

[...]

public Ecommerce Ecommerce {get; set;} // One-to-zero-or-one (optional)

public Logistics Logistics {get; set;} // One-to-zero-or-one (optional)
}

public enum EntityType{
None,

Ecommerce,
Logistics,
Customer
}

public class Entity{
public Guid Id {get; set;}

public EntityType Type {get; set;}

[...]
}

public class Ecommerce : Entity{
public Api Api {get; set;} // One-to-one (required)

[...]
}

public class Logistics : Entity{
public Api Api {get; set;} // One-to-one (required)

[...]
}

public class Customer : Entity{
[...]
}
```
Fluent API
```csharp
modelBuilder.Entity()
.HasKey(a => a.Id)
/* ... */;

modelBuilder.Entity()
.HasKey(e => e.Id)
.HasDiscriminator("Type")
.HasValue(EntityType.None)
.HasValue(EntityType.Ecommerce)
.HasValue(EntityType.Logistics)
.HasValue(EntityType.Customer)
/* ... */;

modelBuilder.Entity()
.Property("ApiId").IsRequired();
modelBuilder.Entity()
.HasOne("Api").WithOne("Ecommerce").IsRequired();

modelBuilder.Entity()
.Property("ApiId").IsRequired();
modelBuilder.Entity()
.HasOne("Api").WithOne("Logistics").IsRequired();

modelBuilder.Entity()
/* ... */;
```

This produces the following migration:
```csharp
[...]

migrationBuilder.CreateTable(
name: "Entities",
columns: table => new
{
Id = table.Column(type: "uniqueidentifier", nullable: false),
Type = table.Column(type: "int", nullable: false),
[...]
ApiId = table.Column(type: "int", nullable: true),
Logistics_ApiId = table.Column(type: "int", nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Entities", x => x.Id);
table.ForeignKey(
name: "FK_Entities_Api_Logistics_ApiId",
column: x => x.Logistics_ApiId,
principalTable: "Api",
principalColumn: "Id");
table.ForeignKey(
name: "FK_Entities_Api_ApiId",
column: x => x.ApiId,
principalTable: "Api",
principalColumn: "Id");
});

[...]

migrationBuilder.CreateIndex(
name: "IX_Entities_Logistics_ApiId",
table: "Entities",
column: "Logistics_ApiId",
unique: true,
filter: "[Logistics_ApiId] IS NOT NULL");

migrationBuilder.CreateIndex(
name: "IX_Entities_ApiId",
table: "Entities",
column: "ApiId",
unique: true,
filter: "[ApiId] IS NOT NULL");
```

Which is fine I guess, because those columns would be null if the entity is not `Logistics` or `Ecommerce` but will be correctly populated on those types.

But since I'm using TPH I'd like to save a column by mapping the `ApiId` to the same column:
```csharp
modelBuilder.Entity()
.Property("ApiId").IsRequired();
modelBuilder.Entity()
.HasOne("Api").WithOne("Ecommerce").IsRequired().HasColumnName("ApiId");

modelBuilder.Entity()
.Property("ApiId").IsRequired();
modelBuilder.Entity()
.HasOne("Api").WithOne("Logistics").IsRequired().HasColumnName("ApiId");
```

This produces the following migration:
```csharp
[...]

migrationBuilder.CreateTable(
name: "Entities",
columns: table => new
{
Id = table.Column(type: "uniqueidentifier", nullable: false),
Type = table.Column(type: "int", nullable: false),
[...]
ApiId = table.Column(type: "int", nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Entities", x => x.Id);
table.ForeignKey(
name: "FK_Entities_Api_ApiId",
column: x => x.ApiId,
principalTable: "Api",
principalColumn: "Id");
});

[...]

migrationBuilder.CreateIndex(
name: "IX_Entities_ApiId",
table: "Entities",
column: "ApiId",
unique: true,
filter: "[ApiId] IS NOT NULL");
```

As you can see the single index is not enough since I have two relationship on different entities types. I think something like this would work:
```csharp
migrationBuilder.CreateIndex(
name: "IX_Entities_ApiId_Ecommerce",
table: "Entities",
column: "ApiId",
unique: true,
filter: "[Type] = 1 AND [ApiId] IS NOT NULL");

migrationBuilder.CreateIndex(
name: "IX_Entities_ApiId_Logistics",
table: "Entities",
column: "ApiId",
unique: true,
filter: "[Type] = 2 AND [ApiId] IS NOT NULL");
```

Maybe this would require some additional change tracking between migrations

### Include provider and version information

EF Core version: 6.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0

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.