EF Core — Table Per Type (TPT) | OptimizaEFCore by ZZZ Projects

CSharp · Console

/Program.cs

using System;
using System.Linq;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

public abstract class Contract
{
    public int ContractId { get; set; }
    public DateTime StartDate { get; set; }
    public int Months { get; set; }
    public decimal Charge { get; set; }
}

public class MobileContract : Contract
{
    public string MobileNumber { get; set; } = "";
}

public class TvContract : Contract
{
    public string PackageType { get; set; } = "";
}

public class BroadbandContract : Contract
{
    public int DownloadSpeed { get; set; }
}

public class AppDbContext : DbContext
{
    private readonly SqliteConnection _connection;

    public AppDbContext(SqliteConnection connection)
    {
        _connection = connection;
    }

    public DbSet<Contract> Contracts => Set<Contract>();
    public DbSet<MobileContract> MobileContracts => Set<MobileContract>();
    public DbSet<TvContract> TvContracts => Set<TvContract>();
    public DbSet<BroadbandContract> BroadbandContracts => Set<BroadbandContract>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(_connection);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Contract>()
            .UseTptMappingStrategy()
            .ToTable("Contracts");

        modelBuilder.Entity<MobileContract>()
            .ToTable("MobileContracts");

        modelBuilder.Entity<TvContract>()
            .ToTable("TvContracts");

        modelBuilder.Entity<BroadbandContract>()
            .ToTable("BroadbandContracts");
    }
}

public class Program
{
    public static void Main()
    {
        using var connection = new SqliteConnection("Data Source=:memory:");
        connection.Open();

        using (var setupContext = new AppDbContext(connection))
        {
            setupContext.Database.EnsureDeleted();
            setupContext.Database.EnsureCreated();

            setupContext.Contracts.AddRange(
                new MobileContract
                {
                    StartDate = new DateTime(2025, 1, 10),
                    Months = 12,
                    Charge = 29.99m,
                    MobileNumber = "555-1001"
                },
                new TvContract
                {
                    StartDate = new DateTime(2025, 2, 5),
                    Months = 24,
                    Charge = 49.99m,
                    PackageType = "Premium"
                },
                new BroadbandContract
                {
                    StartDate = new DateTime(2025, 3, 1),
                    Months = 18,
                    Charge = 39.99m,
                    DownloadSpeed = 600
                },
                new MobileContract
                {
                    StartDate = new DateTime(2025, 4, 20),
                    Months = 6,
                    Charge = 19.99m,
                    MobileNumber = "555-2002"
                }
            );

            setupContext.SaveChanges();
        }

        using var context = new AppDbContext(connection);

        // 1) Consulta polimórfica al tipo base
        var baseQuery = context.Contracts.OrderBy(c => c.ContractId);

        Console.WriteLine("=== 1) Consulta polimórfica al tipo base (DbSet<Contract>) ===");
        Console.WriteLine();
        Console.WriteLine(baseQuery.ToQueryString());
        Console.WriteLine();

        var allContracts = baseQuery.ToList();

        Console.WriteLine("Resultados:");
        foreach (var contract in allContracts)
        {
            var typeName = contract.GetType().Name;

            Console.WriteLine(
                $"Id={contract.ContractId}, Tipo={typeName}, Charge={contract.Charge}, Months={contract.Months}"
            );
        }

        Console.WriteLine();
        Console.WriteLine(new string('-', 80));
        Console.WriteLine();

        // 2) Consulta por tipo derivado
        var mobileQuery = context.Contracts
            .OfType<MobileContract>()
            .OrderBy(c => c.ContractId);

        Console.WriteLine("=== 2) Consulta por tipo derivado (OfType<MobileContract>) ===");
        Console.WriteLine();
        Console.WriteLine(mobileQuery.ToQueryString());
        Console.WriteLine();

        var mobileContracts = mobileQuery.ToList();

        Console.WriteLine("Resultados:");
        foreach (var contract in mobileContracts)
        {
            Console.WriteLine(
                $"Id={contract.ContractId}, Tipo={contract.GetType().Name}, MobileNumber={contract.MobileNumber}, Charge={contract.Charge}"
            );
        }

        Console.WriteLine();
        Console.WriteLine(new string('-', 80));
        Console.WriteLine();

        // 3) Mostrar filas por tabla para que se vea el esquema TPT
        Console.WriteLine("=== 3) Filas por tabla ===");
        Console.WriteLine();

        Console.WriteLine("Contracts:");
        foreach (var row in context.Contracts
                     .AsNoTracking()
                     .OrderBy(c => c.ContractId)
                     .Select(c => new
                     {
                         c.ContractId,
                         c.StartDate,
                         c.Months,
                         c.Charge
                     }))
        {
            Console.WriteLine(
                $"ContractId={row.ContractId}, StartDate={row.StartDate:yyyy-MM-dd}, Months={row.Months}, Charge={row.Charge}"
            );
        }

        Console.WriteLine();
        Console.WriteLine("MobileContracts:");
        foreach (var row in context.MobileContracts
                     .AsNoTracking()
                     .OrderBy(c => c.ContractId)
                     .Select(c => new
                     {
                         c.ContractId,
                         c.MobileNumber
                     }))
        {
            Console.WriteLine(
                $"ContractId={row.ContractId}, MobileNumber={row.MobileNumber}"
            );
        }

        Console.WriteLine();
        Console.WriteLine("TvContracts:");
        foreach (var row in context.TvContracts
                     .AsNoTracking()
                     .OrderBy(c => c.ContractId)
                     .Select(c => new
                     {
                         c.ContractId,
                         c.PackageType
                     }))
        {
            Console.WriteLine(
                $"ContractId={row.ContractId}, PackageType={row.PackageType}"
            );
        }

        Console.WriteLine();
        Console.WriteLine("BroadbandContracts:");
        foreach (var row in context.BroadbandContracts
                     .AsNoTracking()
                     .OrderBy(c => c.ContractId)
                     .Select(c => new
                     {
                         c.ContractId,
                         c.DownloadSpeed
                     }))
        {
            Console.WriteLine(
                $"ContractId={row.ContractId}, DownloadSpeed={row.DownloadSpeed}"
            );
        }
    }
}