Skip to content

Global query filters produce too many parameters #24476

@stevendarby

Description

@stevendarby

If I add global query filters to all of my entities and the filters use the same property on the DbContext as a value for filtering in all of them, the property is parameterised separately for each entity type in a query rather than sharing one parameter with a common value.

A large number of parameters can make query optimisation harder for the database provider.

In the example below I have two similar DbContexts but with different filtering methods, and use them to run a similar query that includes 3 entities.

One applies a TenantId filter using global query filters: this produces 3 parameters, all with the same value.

The other applies the TenantId filter manually in the Includes and in the Where condition: this produces just 1 parameter. I would hope that you can optimise the global query filters to use a similar strategy to this.

In production we have queries spanning 30+ entities and thus 30+ parameters.

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

        var options = new DbContextOptionsBuilder()
            .UseSqlite(connection)
            .Options;

        using (var context = new DbContextWithFilter(options, 42))
        {
            context.Database.EnsureCreated();

            var queryString = context.Set<Author>()
                .Include(a => a.Blogs)
                .ThenInclude(b => b.Posts)
                .ToQueryString();

            Console.WriteLine("With query filter:");
            Console.WriteLine(queryString);
            Console.WriteLine();
        }

        using (var context = new DbContextWithoutFilter(options, 42))
        {
            context.Database.EnsureCreated();

            var queryString = context.Set<Author>()
                .Include(a => a.Blogs.Where(b => b.TenantId == context.TenantId))
                .ThenInclude(b => b.Posts.Where(p => p.TenantId == context.TenantId))
                .Where(a => a.TenantId == context.TenantId)
                .ToQueryString();

            Console.WriteLine("Without query filter:");
            Console.WriteLine(queryString);
        }
    }
}

public class DbContextWithoutFilter : DbContext
{
    public int TenantId { get; }

    public DbContextWithoutFilter(DbContextOptions options, int tenantId) : base(options)
    {
        TenantId = tenantId;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        var authorBuilder = modelBuilder.Entity<Author>();
        authorBuilder.HasKey(x => new {x.Id, x.TenantId});
        authorBuilder
            .HasMany(x => x.Blogs)
            .WithOne(x => x.Author)
            .HasForeignKey(x => new {x.AuthorId, x.TenantId});

        var blogBuilder = modelBuilder.Entity<Blog>();
        blogBuilder.HasKey(x => new {x.Id, x.TenantId});
        blogBuilder
            .HasMany(x => x.Posts)
            .WithOne(x => x.Blog)
            .HasForeignKey(x => new {x.BlogId, x.TenantId});

        var postBuilder = modelBuilder.Entity<Post>();
        postBuilder.HasKey(x => new {x.Id, x.TenantId});
    }
}

public class DbContextWithFilter : DbContextWithoutFilter
{
    public DbContextWithFilter(DbContextOptions options, int tenantId) : base(options, tenantId)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Author>().HasQueryFilter(x => x.TenantId == TenantId);
        modelBuilder.Entity<Blog>().HasQueryFilter(x => x.TenantId == TenantId);
        modelBuilder.Entity<Post>().HasQueryFilter(x => x.TenantId == TenantId);
    }
}

public abstract class EntityBase
{
    public int Id { get; set; }
    public int TenantId { get; set; }
}

public class Author : EntityBase
{
    public string Name { get; set; }
    public ICollection<Blog> Blogs { get; set; }
}

public class Blog : EntityBase
{
    public string Url { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; }
    public ICollection<Post> Posts { get; set; }
}

public class Post : EntityBase
{
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Output:

With query filter:
.param set @__ef_filter__TenantId_2 42
.param set @__ef_filter__TenantId_1 42
.param set @__ef_filter__TenantId_0 42

SELECT "a"."Id", "a"."TenantId", "a"."Name", "t0"."Id", "t0"."TenantId", "t0"."AuthorId", "t0"."Url", "t0"."Id0", "t0"."TenantId0", "t0"."BlogId", "t0"."Content", "t0"."Title"
FROM "Author" AS "a"
LEFT JOIN (
    SELECT "b"."Id", "b"."TenantId", "b"."AuthorId", "b"."Url", "t"."Id" AS "Id0", "t"."TenantId" AS "TenantId0", "t"."BlogId", "t"."Content", "t"."Title"
    FROM "Blog" AS "b"
    LEFT JOIN (
        SELECT "p"."Id", "p"."TenantId", "p"."BlogId", "p"."Content", "p"."Title"
        FROM "Post" AS "p"
        WHERE "p"."TenantId" = @__ef_filter__TenantId_2
    ) AS "t" ON ("b"."Id" = "t"."BlogId") AND ("b"."TenantId" = "t"."TenantId")
    WHERE "b"."TenantId" = @__ef_filter__TenantId_1
) AS "t0" ON ("a"."Id" = "t0"."AuthorId") AND ("a"."TenantId" = "t0"."TenantId")
WHERE "a"."TenantId" = @__ef_filter__TenantId_0
ORDER BY "a"."Id", "a"."TenantId", "t0"."Id", "t0"."TenantId", "t0"."Id0", "t0"."TenantId0"

Without query filter:
.param set @__context_TenantId_0 42

SELECT "a"."Id", "a"."TenantId", "a"."Name", "t0"."Id", "t0"."TenantId", "t0"."AuthorId", "t0"."Url", "t0"."Id0", "t0"."TenantId0", "t0"."BlogId", "t0"."Content", "t0"."Title"
FROM "Author" AS "a"
LEFT JOIN (
    SELECT "b"."Id", "b"."TenantId", "b"."AuthorId", "b"."Url", "t"."Id" AS "Id0", "t"."TenantId" AS "TenantId0", "t"."BlogId", "t"."Content", "t"."Title"
    FROM "Blog" AS "b"
    LEFT JOIN (
        SELECT "p"."Id", "p"."TenantId", "p"."BlogId", "p"."Content", "p"."Title"
        FROM "Post" AS "p"
        WHERE "p"."TenantId" = @__context_TenantId_0
    ) AS "t" ON ("b"."Id" = "t"."BlogId") AND ("b"."TenantId" = "t"."TenantId")
    WHERE "b"."TenantId" = @__context_TenantId_0
) AS "t0" ON ("a"."Id" = "t0"."AuthorId") AND ("a"."TenantId" = "t0"."TenantId")
WHERE "a"."TenantId" = @__context_TenantId_0
ORDER BY "a"."Id", "a"."TenantId", "t0"."Id", "t0"."TenantId", "t0"."Id0", "t0"."TenantId0"

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions