How to create default filters with Entity framework 6?

Question

I have an Excluded field in several classes and when I will make my select I always have to impose this condition in the query, is there any way to make the filter implicitly? recalling that it is EF 6.

var consulta = this.Contexto.Set<ClienteIdRaiz>().AsQueryable();
var totalRegistros = 0;

consulta = consulta.Where(x => !x.Excluido);

Answer 1

• Entity Framework 6

The Entity Framework 6 does not have the own resource to make Global Query Filter, but with the addition of packages via nuget:

• Z.EntityFramework.Plus.EF6
• Z.EntityFramework.Plus.QueryIncludeFilter.EF6

has added resources on the Entity Framework 6 and one of them is to provide a global filter in your SQL implicitly.

After installing the two packages and configuring their class, add a line inside the construtor of the class it inherits from the DbContext. A scenario would be to recover all the people with the camp Ativo = 1, example:

public DatabaseContext()
        :base(ConnStr)
{
    Database.SetInitializer<DatabaseContext>(null);
    QueryFilterManager.Filter<People>(x => x.Where(a => a.Active == true));
}

or

call the extension method Filter<>, example:

public DatabaseContext()
            :base(ConnStr)
{
    Database.SetInitializer<DatabaseContext>(null);            
    this.Filter<People>(x => x.Where(a => a.Active));
}

Besides the filters this is standard throughout the application, the name already says Global Query Filter. If at any time in the application you need this filter not to be called use the method AsNoFilter(), example:

db.People.AsNoFilter().ToList();

that the filter will not be added to the SQL.

Complete minimum example:

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using Z.EntityFramework.Plus;

namespace ConsoleApp41.Models
{
    public class People
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool Active { get; set; }
    }

    public class DatabaseContext: DbContext
    {
        protected const string ConnStr = "Connect_Strings";
        public DatabaseContext()
            :base(ConnStr)
        {
            Database.SetInitializer<DatabaseContext>(null);
            QueryFilterManager.Filter<People>(x => x.Where(a => a.Active == true));
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<People>()
                .ToTable("People");

            modelBuilder.Entity<People>()
                .HasKey(X => X.Id)
                .Property(x => x.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
                .IsRequired();

            modelBuilder.Entity<People>()
                .Property(x => x.Name)
                .HasMaxLength(50)
                .IsRequired();

            modelBuilder.Entity<People>()
                .Property(x => x.Active)
                .IsRequired();

        }

        public DbSet<People> People { get; set; }
    }
}

In the documentation you have a variety of filters, but, on its doubt what is in the answer is the ideal for its development.

---

• Entity Framework Core

This feature is not present in Entity Framework 6, but, in the Entity Framework Core such resource already exists under the name of Global Query Filters, how to set up?

In the method OnModelCreating, add a line as an example below:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<People>().HasQueryFilter(p => p.Active);
    //
}

has the same effect as the example of the installed package in the version 6 and also has to disable using the method IgnoreQueryFilters(), example:

var result  = db.People.IgnoreQueryFilters().ToList();

_References:

• Z.EntityFramework.Plus.EF6 - Global Query Filter
• Installation - Z.EntityFramework.Plus.EF6
• Installation - Z.EntityFramework.Plus.QueryIncludeFilter.EF6
• Entity Framework Core - Global Query Filters