Allow Entity Splitting with non-identifying FKs

[Anspitzen]

Bug description

Referencing This dokumentation

When declaring the entity splitting with a non-convention foreign key from dependend entity to primary table,
without having an entity for the primary table,
the generated SQL query does ignore the foreign key and just links the primary keys from both tables in the join

public class SqlContext : DbContext
{
public DbSet Dependends { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<DependSplit>(entity =>
    {
        entity.ToTable("Prime", "dbo");
        entity.SplitToTable(
            "Depend", "dbo",
            tb =>
            {
                tb.Property(p => p.SomeValue);
            });
        entity.HasOne<DependSplit>().WithOne().HasForeignKey<DependSplit>(d => d.Primary_ID_ToLink);
    });
}

}

Produces on a Dependents.ToList() the query

SELECT [d].[Id], [d].[Primary_ID_ToLink], [p].[SomeValue]
FROM [dbo].[Depend] AS [d]
INNER JOIN [dbo].[Prime] AS [p] ON [d].[Id] = [p].[Id]

It should be:
SELECT [d].[Id], [d].[Primary_ID_ToLink], [p].[SomeValue]
FROM [dbo].[Depend] AS [d]
INNER JOIN [dbo].[Prime] AS [p] ON [d].[Primary_ID_ToLink] = [p].[Id]

For brevity a lot of other data is left from the code, I just added the minimalist code snippets to get a running sample showcasing the problem
Ids are Identity, the Prime table is independent from the Depend.
Entries in the Prime can exist without a corresponding Id in the Depend
Each Entry in Depend requires an entry in Prime, which will have a different Id (as Prime has many times more entries incremented on key)
I query just the Depend and no Entities from Prime

Your code

(Existing) Database:

CREATE TABLE dbo.Prime (
	Id int NOT NULL PRIMARY KEY,
	SomeValue int NOT NULL
)
CREATE TABLE dbo.Depend (
	Id int NOT NULL PRIMARY KEY,
	Primary_ID_ToLink int NOT NULL,
	FOREIGN KEY (Primary_ID_ToLink) REFERENCES Prime(Id)
)
INSERT INTO dbo.Prime (Id, SomeValue) 
VALUES (1, 1), (2, 2), (3, 3), (4, 4)
INSERT INTO dbo.Depend (Id, Primary_ID_ToLink) 
VALUES (1, 2), (2, 3), (4, 4)

Model:

public class DependSplit
{
    public int Id {  get; set; }
    public int Primary_ID_ToLink {  get; set; }
    public int SomeValue {  get; set; }
}

Ef Core configuration:

public class SqlContext : DbContext
{
    public DbSet<DependSplit> Dependends { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    string SqlConStr = "InsertConnectionStringHere";
    optionsBuilder.UseSqlServer(SqlConStr).LogTo(message => Debug.WriteLine(message));
}
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<DependSplit>(entity =>
        {
            entity.ToTable("Prime", "dbo");
            entity.SplitToTable(
                "Depend", "dbo",
                tb =>
                {
                    tb.Property(p => p.SomeValue);
                });
            entity.HasOne<DependSplit>().WithOne().HasForeignKey<DependSplit>(d => d.Primary_ID_ToLink);
        });
    }
}

Usage:

using (var context = new SqlContext())
{
    var items = await context.Dependends.AsNoTracking().ToListAsync();
}

Stack traces

Verbose output

EF Core version

9.0.2

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 8.0

Operating system

No response

IDE

No response

[thalysmarciobn]

SplitToTable only works when both tables share the same primary key. In this case, Depend links to Prime using a foreign key (Primary_ID_ToLink), but EF Core still tries to join on Id = Id. That’s why the SQL is wrong.

To fix it, don’t use SplitToTable. Map Prime and Depend as separate entities and set up a relationship with .HasOne().WithMany().HasForeignKey(...). This will make EF Core generate the correct join on Primary_ID_ToLink = Prime.Id.

---- u can try -----

public class SqlContext : DbContext
{
    public DbSet<Depend> Dependends { get; set; }
    public DbSet<Prime> Primes { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Depend>(entity =>
        {
            entity.ToTable("Depend", "dbo");
            entity.HasKey(d => d.Id);

            entity.HasOne(d => d.Prime)
                  .WithMany()
                  .HasForeignKey(d => d.Primary_ID_ToLink)
                  .HasPrincipalKey(p => p.Id)
                  .IsRequired();
        });

        modelBuilder.Entity<Prime>(entity =>
        {
            entity.ToTable("Prime", "dbo");
            entity.HasKey(p => p.Id);
        });
    }
}

public class Depend
{
    public int Id { get; set; }
    public int Primary_ID_ToLink { get; set; }

    public Prime Prime { get; set; }  // navigation property
}

public class Prime
{
    public int Id { get; set; }
    public int SomeValue { get; set; }
}

var items = await context.Dependends
    .AsNoTracking()
    .Select(d => new
    {
        d.Id,
        d.Primary_ID_ToLink,
        d.Prime.SomeValue
    })
    .ToListAsync();

[Anspitzen]

Thats how I'm doing it as an immediate stopgap (With a HasOne,withOne relationship, and the Include on query. Only flattening later on because). But that does return only the anonymous type, not the needed Model of Depend with the SomeValue. So I have to cast it again in memory after the query, where I got all data already, just not typed.
From my understanding the Entity Splitting is basically a flattening for a 1:1 relationship on query level. I can declare different FKs for 1:1, I have the same declaration in the documentation for the splitting, but it behaves differently? Thats what is causing me grief.

[thalysmarciobn]

Thats how I'm doing it as an immediate stopgap (With a HasOne,withOne relationship, and the Include on query. Only flattening later on because). But that does return only the anonymous type, not the needed Model of Depend with the SomeValue. So I have to cast it again in memory after the query, where I got all data already, just not typed. From my understanding the Entity Splitting is basically a flattening for a 1:1 relationship on query level. I can declare different FKs for 1:1, I have the same declaration in the documentation for the splitting, but it behaves differently? Thats what is causing me grief.

this avoids duplicate memory allocation

context.Dependends
    .AsNoTracking()
    .Select(d => new DependSplit 
    {
        Id = d.Id,
        Primary_ID_ToLink = d.Prime.Id,
        SomeValue = d.Prime.SomeValue
    })
    .ToList();

for critical scenarios use FromSqlRaw

with auto mapper:

context.Dependends
    .Include(d => d.Prime)
    .ProjectTo<DependSplit>(_mapper.ConfigurationProvider)
    .ToList();

---- or ----

if you prefer to switch between using view

modelBuilder.Entity<Depend>().ToView("consultView");

the above alternatives are just stopgap

[Anspitzen]

Exatly, it is a stopgap.
Again, I have abstracted the problem to minimum running problem. Lets just say I have no access to database structure (just quering it) like I'm using an in-memory Northwind databse for example.

I don't have AutoMapper included (and WILL NOT do so).
I don't plan to have a model for Prime inside this part of the app, so yes, stopgap has it for navigation, it is not planned to have at all because it should not get exposed.

The Select is what is working (thats the stopgap), but what I expect EF Core to do without me telling it so. Because I query the Depend set already, so it would be confusing to have again tell it what to make with it. Thats why I use EF core, else I could just go and use dapper

using(IDbConnection = new SqlConnection)
{
    return connection.Query<Depend>("SELECT Id, SomeValue FROM dbo.Depend LEFT JOIN Prime p ON p.Id = Primary_ID_ToLink");
}

And be done with it. If I have to write down the whole query myself and twice over again I wouldn't use Ef Core. That's waht the Context is for.

And again, my main problem is the configuring of the Foreign Key. I configure a Entity<Depend>().HasOne<Depend>().WithOne().HasForeignKey<Depend>("Primary_ID_ToLink"), thats why I expect Ef Core to use that FK and not ignore that completely. I told it where to find each property, so why should I configure it when it gets ignored?