Make ignoring query filters more flexible

[taisbak]

Currently filtering is turned off for all entities if IgnoreQueryFilters() is present somewhere in a query.

It would be more useful if it was scoped by context.

I.e., on DbSet, it would only be for that reference of the entity. Same for Include().

Anywhere else it would apply globally as today.

It might be better to call the new ignore methods something else e.g., IgnoreEntityFilters().

I'm aware that this topic has been discussed before and dismissed. Still, I hope you will read this as friendly reminder of the need and reconsider :-)

Some code to illustrate the point:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EfCoreIgnoreQueryFilters
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new Context())
            {
                var TablesAndColumns =
                    db.Tables                // .IgnoreQueryFilters()  // ignore filtering of Tables
                    .Include(t => t.Columns) // .IgnoreQueryFilters()  // ignore filtering of Columns
                    ;

                foreach (var t in TablesAndColumns.Take(10))
                {
                    Console.WriteLine(t.Name);
                    foreach (var c in t.Columns)
                        Console.WriteLine("     " + c.Name);
                }

                var TablesAndColumns2 =
                    db.Tables                // .IgnoreQueryFilters()   // ignore filtering of Tables
                    .Join(db.Columns,        // .IgnoreQueryFilters(),  // ignore filtering of Columns
                        t => t.Object_id,
                        c => c.Object_id,
                        (t, c) => new { Table = t.Name, Column = c.Name })
                    ;

                foreach (var tc2 in TablesAndColumns2.Take(10))
                    Console.WriteLine(tc2.Table + " . " + tc2.Column);

                var TablesAndColumns3 =
                    from t in db.Tables      // .IgnoreQueryFilters()  // ignore filtering of Tables
                    join c in db.Columns     // .IgnoreQueryFilters()  // ignore filtering of Columns
                        on t.Object_id equals c.Object_id
                    select new { Table = t.Name, Column = c.Name }
                    ;

                foreach (var tc3 in TablesAndColumns3.Take(10))
                    Console.WriteLine(tc3.Table + " . " + tc3.Column);
            }
        }

        public class Context : DbContext
        {
            public DbSet<SysColumns> Columns { get; set; }

            public DbSet<SysTables> Tables { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder
                    .UseLoggerFactory(MyLoggerFactory)
                    .UseSqlServer("Server=tcp: sqlcore-ag-k2.tcb.sdc.dk,5101;Initial Catalog=SQLK21;Integrated Security=True;MultipleActiveResultSets=True;Pooling=False",
                                   options => options.UseRelationalNulls())
                    ;
            }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder
                    .Entity<SysColumns>()
                    .HasQueryFilter(c => c.Is_nullable == false) 
                    .HasKey(c => new { c.Object_id, c.Column_id })
                    ;

                modelBuilder
                    .Entity<SysTables>()
                    .HasQueryFilter(e => e.Schema_id == 1)
                    ;
            }

            public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder =>
                builder
                    .AddFilter("Microsoft.EntityFrameworkCore.Database.Command", LogLevel.Information)
                    .AddConsole())
                    ;
        }

        [Table("tables", Schema = "sys")]
        public class SysTables
        {
            [Key]
            public int Object_id { get; set; }
            public string Name { get; set; }
            public int Schema_id { get; set; }

            public List<SysColumns> Columns { get; set; }
        }

        [Table("columns", Schema = "sys")]
        public class SysColumns
        {
            public int Object_id { get; set; }
            public string Name { get; set; }
            public int Column_id { get; set; }
            public bool Is_nullable { get; set; }

            [ForeignKey("Object_id")]
            public SysTables Table { get; set; }
        }
    }
}

Further technical details

EF Core version: 3.0.0-preview9.19420.21
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2019 16.3.0 preview 2.0

[smitpatel]

Runs into same issue as #6717

[ajcvickers]

See #10275 (comment)

[ajcvickers]

See also the experience for explicit loading in #21349

[Luk164]

I have a similar issue where I would appreciate an optional true/false parameter for the IgnoreQueryFilters() for some specific situations. Right now I have to split the query into two with an "if" in the middle. So IgnoreQueryFilters(false) would have the same result as not putting it there at all.