How to specify an optional SplitToTable configuration

[gabrielmatrella]

I need to configure fields in the splitted table as optional, to ef core prepare queries with left join instead inner, there is some way to achieve this now?

public void Configure(EntityTypeBuilder<Produto> entity)
    {
        entity.ToTable("produto");

        entity.SplitToTable("produto_fiscal", tableBuilder =>
        {
            tableBuilder.Property(p => p.DescricaoEstoque);
        });
      ...

The resulting query:

 SELECT p.produtoid
      FROM produto AS p
      INNER JOIN produto_fiscal AS p0 ON p.produtoid = p0.produtoid

EF Core version: 7.0.0
Database provider: (e.g. Npgsql)

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[gabrielmatrella]

There it is:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EfCoreSplitToTableOptionalIssue;

public static class Config
{
    public static string ConnectionString = @"Server=localhost;Database=SplitToTableSample;TrustServerCertificate=True;Integrated Security=True";
}

public class Produto
{
    public int ProdutoId { get; set; }
    public string Descricao { get; set; } = "";
    public string? DescricaoEstoque { get; set; }
}

public class SomeDbContext : DbContext
{
    public DbSet<Produto> Produtos { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Config.ConnectionString)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Produto>(entity =>
        {
            entity.ToTable("produto")
                .SplitToTable("produto_fiscal", builder =>
                {
                    builder.Property(p => p.DescricaoEstoque);
                });
            
            entity.HasKey(x => x.ProdutoId);
            
            entity.Property(x => x.ProdutoId)
                .ValueGeneratedOnAdd();
        });
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        using var dbContext = new SomeDbContext();
        dbContext.Database.EnsureDeleted();
        dbContext.Database.EnsureCreated();
        
        dbContext.Produtos.Add(new Produto()
        {
            Descricao = "My Product"
        });

        dbContext.SaveChanges();
        
        dbContext.Produtos.ToList();
    }
}

Resulting query:

SELECT [p].[ProdutoId], [p].[Descricao], [p0].[DescricaoEstoque]
FROM [produto] AS [p]
INNER JOIN [produto_fiscal] AS [p0] ON [p].[ProdutoId] = [p0].[ProdutoId]

It works well in an environment where all records are created with the existence of the splittedtable at the beginning, but when you do mutation to your database adding some splittedtable after you already have records, its not possible to get results, unless executing some kind of script to create rows for every record in the new table during migration process.

Is there any configuration for the split table, where I can inform that it may not have records? (Left join)

[ajcvickers]

@gabrielmatrella We will discuss.

[AndriySvyryd]

Duplicate of #27974