Join with query filter causes nested subquery and poor query performance

[bigpjo]

EFCore 3.1.4

When using dynamic query filters and joins the sql generated uses subqueries for each table joined causing slow query executing on large data tables.

namespace EFCoreTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new EFExampleContext())
            {
                var cd = (from n in db.Name
                          join t in db.Telephone on n.Id equals t.NameId
                          select new
                          {
                              n.Name1,
                              t.PhoneNumber
                          })
                         .ToList();
            }
        }
    }

    public class EFExampleContext : DbContext
    {
        public EFExampleContext()
        {
        }

        public virtual DbSet<Name> Name { get; set; }
        public virtual DbSet<Telephone> Telephone { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Database=EFExample;Integrated Security=true;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Name>(entity =>
            {
                entity.Property(e => e.Id).ValueGeneratedNever();

                entity.Property(e => e.Name1)
                    .HasColumnName("Name")
                    .HasMaxLength(100)
                    .IsUnicode(false);
            });

            modelBuilder.Entity<Telephone>(entity =>
            {
                entity.Property(e => e.Id).ValueGeneratedNever();

                entity.Property(e => e.PhoneNumber)
                    .HasMaxLength(10)
                    .IsUnicode(false);

                entity.HasOne(d => d.Name)
                    .WithMany(p => p.Telephone)
                    .HasForeignKey(d => d.NameId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Telephone_Telephone");

                entity.HasQueryFilter(e => !e.Deleted);
            });


        }

    }

    public class Name
    {
        public Name()
        {
            Telephone = new HashSet<Telephone>();
        }

        public int Id { get; set; }
        public string Name1 { get; set; }
        public bool Deleted { get; set; }

        public virtual ICollection<Telephone> Telephone { get; set; }
    }

    public class Telephone
    {
        public int Id { get; set; }
        public int NameId { get; set; }
        public string PhoneNumber { get; set; }
        public bool Deleted { get; set; }

        public virtual Name Name { get; set; }
    }
}

Produces sql statement

SELECT [n].[Name] AS [Name1], [t0].[PhoneNumber]
FROM [Name] AS [n]
INNER JOIN (
    SELECT [t].[Id], [t].[Deleted], [t].[NameId], [t].[PhoneNumber]
    FROM [Telephone] AS [t]
    WHERE [t].[Deleted] <> CAST(1 AS bit)
) AS [t0] ON [n].[Id] = [t0].[NameId]

Whereas the optimal query would be

SELECT [n].[Name] AS [Name1], [t0].[PhoneNumber]
FROM [Name] AS [n]
INNER JOIN [Telephone] AS [t0] ON [n].[Id] = [t0].[NameId] and [t0].[Deleted] <> CAST(1 AS bit)

Any suggestions on what I may be doing wrong or how to optimise the query generation

Thanks in advance

[Coldairarrow]

is there any solutions ?
i have the same question

[paaland]

Not only that, but if you try and use full text indexing functions like Contains or FreeText it failes since it tried to do them on the sub query.

SELECT [n].[Name] AS [Name1], [t0].[PhoneNumber]
FROM [Name] AS [n]
INNER JOIN (
    SELECT [t].[Id], [t].[Deleted], [t].[NameId], [t].[PhoneNumber]
    FROM [Telephone] AS [t]
    WHERE [t].[Deleted] <> CAST(1 AS bit)
) AS [t0] ON [n].[Id] = [t0].[NameId]
WHERE  Contains('PhoneNumber', '"something")

[FrancoisKr]

Will this issue be addressed? Any workarounds?

[roji]

@smitpatel related/dup of #17622?

[smitpatel]

Also related #21082