dotnet / dotnet/efcore

Error while querying entities of derived class with changed primary key name that have owned type in separate table

Open
#35,591 7 comments 0 reactions 0 assignees View on GitHub
area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

### Bug description

I encountered an error while querying entities of derived class with changed primary key name that have owned type in separate table.

After some debugging I noticed that EF joins "SHELTER_MANAGER"."TURTLES_AQUARIUM" using wrong condition. As I excpect, it should join it using reference to "Turtles" table (t."TurtleId") or using reference to base animal table (a."Id") instead of mixing it.

```
Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (20ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT s."Id", t1."Id", t1."ShelterId", t1."Discriminator", t1."Id0", t1."Depth", t1."Length", t1."TurtleId", t1."Width"
FROM "SHELTER_MANAGER"."SHELTERS" AS s
LEFT JOIN (
SELECT a."Id", a."ShelterId", CASE
WHEN t."TurtleId" IS NOT NULL THEN 'Turtle'
WHEN d."DogId" IS NOT NULL THEN 'Dog'
WHEN c."CatId" IS NOT NULL THEN 'Cat'
END AS "Discriminator", t0."Id" AS "Id0", t0."Depth", t0."Length", t0."TurtleId", t0."Width"
FROM "SHELTER_MANAGER"."ANIMALS" AS a
LEFT JOIN "SHELTER_MANAGER"."CATS" AS c ON a."Id" = c."CatId"
LEFT JOIN "SHELTER_MANAGER"."DOGS" AS d ON a."Id" = d."DogId"
LEFT JOIN "SHELTER_MANAGER"."TURTLES" AS t ON a."Id" = t."TurtleId"
LEFT JOIN "SHELTER_MANAGER"."TURTLES_AQUARIUM" AS t0 ON a."TurtleId" = t0."TurtleId"
) AS t1 ON s."Id" = t1."ShelterId"
ORDER BY s."Id", t1."Id"
```

**On the other hand, changing .OwnsOne to .HasOne with simmilar configuration creates a working query:**

```
Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT s."Id", t1."Id", t1."ShelterId", t1."Discriminator", t1."Id0", t1."Depth", t1."Length", t1."TurtleId", t1."Width"
FROM "SHELTER_MANAGER"."SHELTERS" AS s
LEFT JOIN (
SELECT a."Id", a."ShelterId", CASE
WHEN t."TurtleId" IS NOT NULL THEN 'Turtle'
WHEN d."DogId" IS NOT NULL THEN 'Dog'
WHEN c."CatId" IS NOT NULL THEN 'Cat'
END AS "Discriminator", t0."Id" AS "Id0", t0."Depth", t0."Length", t0."TurtleId", t0."Width"
FROM "SHELTER_MANAGER"."ANIMALS" AS a
LEFT JOIN "SHELTER_MANAGER"."CATS" AS c ON a."Id" = c."CatId"
LEFT JOIN "SHELTER_MANAGER"."DOGS" AS d ON a."Id" = d."DogId"
LEFT JOIN "SHELTER_MANAGER"."TURTLES" AS t ON a."Id" = t."TurtleId"
LEFT JOIN "SHELTER_MANAGER"."TURTLES_AQUARIUM" AS t0 ON a."Id" = t0."TurtleId"
) AS t1 ON s."Id" = t1."ShelterId"
ORDER BY s."Id", t1."Id"
```

### Your code

```csharp
public class Shelter
{
public int Id { get; set; }
public IEnumerable Animals { get; set; }
}

public abstract class Animal
{
public int Id { get; set; }
public int ShelterId { get; set; }
}

public class Turtle : Animal
{
public Aquarium Aquarium { get; set; }
}

public class Aquarium
{
public int Width { get; set; }
public int Length { get; set; }
public int Depth { get; set; }
}

public class ShelterConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("SHELTERS", "SHELTER_MANAGER");

builder.HasKey(x => x.Id);

builder.Property(_ => _.Id);

builder.HasMany(_ => _.Animals)
.WithOne()
.HasForeignKey(_ => _.ShelterId);
}
}

public class AnimalConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("ANIMALS", "SHELTER_MANAGER");

builder.HasKey(x => x.Id);
}
}

public class TurtleConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("TURTLES", "SHELTER_MANAGER", tb =>
{
tb.Property(_ => _.Id).HasColumnName("TurtleId");
});

//builder.HasOne(_ => _.Aquarium)
// .WithOne()
// .HasForeignKey("TurtleId");

builder.OwnsOne(_ => _.Aquarium, onb =>
{
onb.ToTable("TURTLES_AQUARIUM", "SHELTER_MANAGER");

onb.Property("Id");
onb.HasKey("Id");

onb.Property("TurtleId");
onb.WithOwner().HasForeignKey("TurtleId");
});
}
}

public class AquariumConfiguration : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("TURTLES_AQUARIUM", "SHELTER_MANAGER");

builder.Property("Id");
builder.HasKey("Id");
}
}

public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions options) : base(options) { }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);

new AnimalConfiguration().Configure(modelBuilder.Entity());

new TurtleConfiguration().Configure(modelBuilder.Entity());
//new AquariumConfiguration().Configure(modelBuilder.Entity());

new ShelterConfiguration().Configure(modelBuilder.Entity());
}

public DbSet Shelters { get; set; }
}
```

### Stack traces

```text
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
Npgsql.PostgresException (0x80004005): 42703: kolumna a.TurtleId nie istnieje

POSITION: 771
at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at ShelterManager.Controllers.ShelterController.Get() in D:\Source\Projects\ShelterManager\ShelterManager\Controllers\ShelterController.cs:line 25
at lambda_method5(Closure, Object)
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
Exception data:
Severity: BŁĄD
SqlState: 42703
MessageText: kolumna a.TurtleId nie istnieje
Hint: Być może chodziło ci o wskazanie kolumny "t.TurtleId".
Position: 771
File: parse_relation.c
Line: 3729
Routine: errorMissingColumn
```

### Verbose output

```text

```

### EF Core version

8.0.12

### Database provider

tested in Npgsql.EntityFrameworkCore.PostgreSQL 8.0.4 and Oracle.EntityFrameworkCore 8.23.60

### Target framework

.Net 8

### Operating system

Windows 10

### IDE

Visual Studio 2022 17.11.5

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.