EF Core 7.0 generates query with non-existent columns for composite foreign key #29697
Comments
|
This issue is lacking enough information for us to be able to fully understand what is happening. In particular, it doesn't contain code for the entity types or model configuration. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate. |
|
Sure. Here's the project: https://github.com/scobei/efcore7_composite_fk_issue Steps to reproduceStep 1 - Add tables to databaseAdd the two tables base_table and sub_table described above. I am using PostgreSQL 13, by the way. Step 2 - ScaffoldThe command I used is: Step 3 - Build and run |
|
Note for triage: repros with SQL Server. There is code below to generate the SQL Server database as well as the results of reverse engineering the database. Looks like a reverse engineering issue--the @scobei As a workaround, you can update the scaffolded code to add in the missing entity.HasOne(d => d.BaseTable).WithMany(p => p.SubTables)
.HasForeignKey(p => new { p.SubId, p.BaseTypeId })
.HasPrincipalKey(p => new { p.BaseId, p.BaseTypeId })
.HasConstraintName("fk_sub_base_one_to_one");The generated model is: Scaffolded code: using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using test.Data.Models;
namespace test.Data;
public partial class TestDbContext : DbContext
{
public TestDbContext(DbContextOptions<TestDbContext> options)
: base(options)
{
}
public virtual DbSet<BaseTable> BaseTables { get; set; }
public virtual DbSet<SubTable> SubTables { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<BaseTable>(entity =>
{
entity.HasKey(e => e.BaseId).HasName("base_table_pkey");
entity.Property(e => e.BaseId).ValueGeneratedNever();
});
modelBuilder.Entity<SubTable>(entity =>
{
entity.HasKey(e => e.SubId).HasName("sub_table_pkey");
entity.Property(e => e.SubId).ValueGeneratedNever();
entity.HasOne(d => d.BaseTable).WithMany(p => p.SubTables).HasPrincipalKey(p => new { p.BaseId, p.BaseTypeId });
});
OnModelCreatingPartial(modelBuilder);
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
namespace test.Data.Models;
[Table("base_table")]
[Index("BaseId", "BaseTypeId", Name = "AK_base_table_base_id_base_type_id", IsUnique = true)]
public partial class BaseTable
{
[Key]
[Column("base_id")]
public long BaseId { get; set; }
[Column("base_type_id")]
public int BaseTypeId { get; set; }
public virtual ICollection<SubTable> SubTables { get; } = new List<SubTable>();
}using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
namespace test.Data.Models;
[Table("sub_table")]
public partial class SubTable
{
[Key]
[Column("sub_id")]
public long SubId { get; set; }
[Column("base_type_id")]
public int BaseTypeId { get; set; }
public virtual BaseTable BaseTable { get; set; } = null!;
}Scaffolding command: To generate the test database: public class SomeDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
}
public class Program
{
public static async Task Main()
{
using (var context = new SomeDbContext())
{
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
context.Database.ExecuteSqlRaw(
@" CREATE TABLE [dbo].[base_table] (
[base_id] bigint NOT NULL,
[base_type_id] int NOT NULL,
CONSTRAINT [base_table_pkey] PRIMARY KEY ([base_id]),
CONSTRAINT [AK_base_table_base_id_base_type_id] UNIQUE ([base_id], [base_type_id])
);
");
context.Database.ExecuteSqlRaw(
@" CREATE TABLE [dbo].[sub_table] (
[sub_id] bigint NOT NULL,
[base_type_id] int NOT NULL,
CONSTRAINT [sub_table_pkey] PRIMARY KEY ([sub_id]),
CONSTRAINT [FK_sub_table_base_table_sub_id_base_type_id] FOREIGN KEY ([sub_id], [base_type_id]) REFERENCES [dbo].[base_table] ([base_id], [base_type_id]) ON DELETE CASCADE
);
");
}
}
} |
|
Adding |
|
Duplicate of #29418. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Issue description
If a table references a composite unique constraint, EF Core 7.0 generates a query that includes columns that do not exist. Previous versions did not have this problem. Below is a minimal example to reproduce the problem.
Database tables
Program.cs
Output for EF Core: 6.0.11, Npgsql: 6.0.7
Output for EF Core: 7.0.0, Npgsql: 7.0.0
Provider and version information
EF Core version: 7.0.0
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 7.0
Operating system: Windows 11 Pro for Workstations, version 22H2
IDE: Visual Studio 2022 17.4.1
The text was updated successfully, but these errors were encountered: