TPC query includes table that is not needed

[ajcvickers]

The following query will only return Pet, Cat, or Dog instances. It cannot return FarmAnimal instances, since they are not derived from Pet. Therefore, FarmAnimals should not be included in the generated query.

context.Animals.OfType<Pet>().Where(a => a.Species.StartsWith("F")).ToList();

      SELECT [t].[Id], [t].[Species], [t].[Name], [t].[EdcuationLevel], [t].[FavoriteToy], [t].[Discriminator]
      FROM (
          SELECT [f].[Id], [f].[Species], NULL AS [Name], NULL AS [EdcuationLevel], NULL AS [FavoriteToy], N'FarmAnimal' AS [Discriminator]
          FROM [FarmAnimals] AS [f]
          UNION ALL
          SELECT [p].[Id], [p].[Species], [p].[Name], NULL AS [EdcuationLevel], NULL AS [FavoriteToy], N'Pet' AS [Discriminator]
          FROM [Pet] AS [p]
          UNION ALL
          SELECT [c].[Id], [c].[Species], [c].[Name], [c].[EdcuationLevel], NULL AS [FavoriteToy], N'Cat' AS [Discriminator]
          FROM [Cats] AS [c]
          UNION ALL
          SELECT [d].[Id], [d].[Species], [d].[Name], NULL AS [EdcuationLevel], [d].[FavoriteToy], N'Dog' AS [Discriminator]
          FROM [Dogs] AS [d]
      ) AS [t]
      WHERE [t].[Discriminator] IN (N'Pet', N'Cat', N'Dog') AND ([t].[Species] LIKE N'F%')

Full code:

#nullable enable

public static class Your
{
    public static string ConnectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;Database=SixOh";
}

public abstract class Animal
{
    public int Id { get; set; }
    public string Species { get; set; }
}

public class FarmAnimal : Animal
{
    public decimal Value { get; set; }
}

public class Pet : Animal
{
    public string Name { get; set; }
}

public class Cat : Pet
{
    public string EdcuationLevel { get; set; }
}

public class Dog : Pet
{
    public string FavoriteToy { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    
    public DbSet<Animal> Animals { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasSequence<int>("AnimalIds");
        
        modelBuilder.Entity<Animal>().UseTpcMappingStrategy().Property(e => e.Id).HasDefaultValueSql("NEXT VALUE FOR [AnimalIds]");
        modelBuilder.Entity<Pet>();
        modelBuilder.Entity<Cat>().ToTable("Cats");
        modelBuilder.Entity<Dog>().ToTable("Dogs");
        modelBuilder.Entity<FarmAnimal>().ToTable("FarmAnimals");

    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.AddRange(
                new Cat {Name = "Alice", Species = "Felis catus", EdcuationLevel = "MBA"},
                new Cat {Name = "Mac", Species = "Felis catus", EdcuationLevel = "BA"},
                new Dog {Name = "Toast", Species = "Canis familiaris", FavoriteToy = "Mr. Squirrel"},
                new FarmAnimal {Value = 100.0m, Species = "Ovis aries"});

            context.SaveChanges();
        }
        
        using (var context = new SomeDbContext())
        {
            var results2 = context.Animals.OfType<Pet>().Where(a => a.Species.StartsWith("F")).ToList();
        }
    }
}

[smitpatel]

Which version are you running?

[smitpatel]

Potentially fixed in #28142

[ajcvickers]

@smitpatel Was using preview 5, but was already fixed in daily from yesterday 7.0.0-preview.6.22308.2.