Tpt mapping Sql Query

[omr-htp]

Can anyone please explain why EF trying to tell the whole story of the entity!

I believe that TPT mapping strategy is the most widely used in real world applications, so this strategy deserves a lot of support and development.
EF team is a great and does a lot of effort introducing new features. Unfortunately, this is done before completing what is required for the existing features.

for example. Because I use TPT strategy I can't use Query Filters usefully.
As another example, I am working on a multimodule multitenant application that contains more than 250 Entities. After upgrading to version 7, creating the model takes 10 times the time that was required for version 6. So, I studied the possibility of using Compiled Model, but unfortunately there is a conflict with the most important feature that I use in EF which is IModelCacheKeyFactory because the application has more than one model depending on the module selected by the tenant.

The question:

Regarding the following code:
Why EF tries to get the whole hierarchy while i just request a top-level entity?

I didn't make a request for any property from Cat or Dog models, I just want the Pet object.

This example has a second problem with the generated SQL string for delete Pet object, it generates multiple Delete statements even if I set delete cascading to Cascade not ClientCascade.
I modified the Cascading behavior after upgrading to Version 7 in response for https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/breaking-changes#tpt-cascade-delete

Thanks.

Project

<Project Sdk="Microsoft.NET.Sdk">
	<PropertyGroup>
		<OutputType>Exe</OutputType>
		<TargetFramework>net7.0</TargetFramework>
	</PropertyGroup>
	<ItemGroup>
		<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.0" />
	</ItemGroup>
</Project>

Code

using System;
using System.Linq;
using MyApp;
using Microsoft.EntityFrameworkCore;

using var context = new MyContext();

await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

Cat cat = new() { Name = "Cat 1", Vet = "Vet 1" };
Dog dog = new() { Name = "Dog 1", FavoriteToy = "Toy 1" };

context.Add(cat);
context.Add(dog);

await context.SaveChangesAsync();

IQueryable<Pet> petQuery = context.Set<Pet>().Where(p => p.Name == "Cat 1");
Console.WriteLine(petQuery.ToQueryString());

Console.ReadLine();


namespace MyApp
{
    public class MyContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite($"Filename=my.db");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Pet>().UseTptMappingStrategy();

            modelBuilder.Entity<Cat>().UseTptMappingStrategy();

            modelBuilder.Entity<Dog>().UseTptMappingStrategy();
        }
    }

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

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

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

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

Output

SELECT p.Id, p.Name, p.Vet, c.FavoriteFood, d.FavoriteToy, CASE
    WHEN d.Id IS NOT NULL THEN 'Dog'
    WHEN c.Id IS NOT NULL THEN 'Cat'
END AS Discriminator
FROM Pet AS p
LEFT JOIN Cat AS c ON p.Id = c.Id
LEFT JOIN Dog AS d ON p.Id = d.Id
WHERE p.Name = 'Cat 1'

Include provider and version information

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.Sqlite)
Target framework: (e.g. .NET 7.0)
Operating system: Windows 10
IDE: (e.g. Visual Studio 2022 17.4)

[roji]

After upgrading to version 7, creating the model takes 10 times the time that was required for version 6.

This is likely because of #29642; in other words, it's a bug (rather than an actual change) that will likely be fixed soon in 7.0. By itself, this shouldn't be a reason to need compiled models.

Apart from that, you posted above about several unrelated things...

I believe that TPT mapping strategy is the most widely used in real world applications, so this strategy deserves a lot of support and development.

Performance-wise, this is certainly the worse of the three inheritance mapping strategies, so we generally discourage using it. See these docs for more details.

for example. Because I use TPT strategy I can't use Query Filters usefully.

Can you provide more details on what that means exactly?

Why EF tries to get the whole hierarchy while i just request a top-level entity?

Your query requests all Pets:

IQueryable<Pet> petQuery = context.Set<Pet>().Where(p => p.Name == "Cat 1");

Since Cats and Dogs are subtypes of Pet, EF fetches them. Think of it as if context.Set<Pet> is just a list of Pets, which contains various types (polymorphism) - Cats, Dogs and other things that inherit from Pet.

[omr-htp]

Since Cats and Dogs are subtypes of Pet, EF fetches them. Think of it as if context.Set is just a list of Pets, which contains various types (polymorphism) - Cats, Dogs and other things that inherit from Pet.

Yes, you are right, so can we make an extension method like .AsNoPolymorphism() or .As<Pet> to tell EF to get only shared properties between Pets (From specific level). just to make a shorter query string.

But we cannot achieve something like this if we cannot make query filters on subtypes!
Thanks.

Now, we close this issue waiting for #29642

Can you provide more details on what that means exactly?

I want to use Query Filters in authorization, but I cannot make Query Filter (in our example) separately for Cat and Dog entities.
like
modelBuilder.Entity<Cat>().HasQueryFilter(e => userCatClaims.Contains(e.Tag));
modelBuilder.Entity<Dog>().HasQueryFilter(e => userDogClaims.Contains(e.Tag));

[roji]

Yes, you are right, so can we make an extension method like .AsNoPolymorphism() or .As to tell EF to get only shared properties between Pets (From specific level). just to make a shorter query string.

You can already query only for Pet instances (since Pet isn't abstract), and exclude Dogs and Cats; see these docs.

If you just want only a certain set of properties, project those out as an anonymous type; that should only query the tables it requires:

context.Set<Pet>().Select(p => new { p.Prop1, p.Prop2 }

I want to use Query Filters in authorization, but I cannot make Query Filter (in our example) separately for Cat and Dog entities.

That's #10259.