Entity Framework Core Feature - Global Query Filters

Introduction

This feature is also called a model-level query filter. It allows us to specify a filter in the model level that is automatically applied to all queries that are executed in the context of the specified type. It means that the entity framework automatically adds the filter in the where clause before executing the LINQ queries. Usually, global query filters are applied in the OnModelCreating method of the context. These filters are also automatically applied to LINQ queries involving entity types that are indirectly referenced, such as ones included as a navigation property.

Common uses of this feature are.

• Soft delete: an Entity Type defines an IsDeleted property, and the application does not require deleted data.
• Multi-tenancy: an Entity Type defines a TenantId property

Example

The following example shows how to apply a global query filter to implement soft-delete. To demonstrate the example, I have created an Employee table and it has the IsDeleted column that is used to define whether the record is deleted or not.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
    [Id] [int] NOT NULL,
      NULL,
    [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [Id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted]) VALUES (1, N'Jignesh', 0)
INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted]) VALUES (2, N'Rakesh', 0)
INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted]) VALUES (3, N'Tejas', 0)
INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted]) VALUES (4, N'Rajesh', 1)

SQL

Copy

First, define entities and context class.

Employee. cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace GlobalFilterExample.Model
{
    [Table("Employee")]
    public class Employee
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public bool IsDeleted { get; set; }
    }
}

C#

Copy

EntityModelContext.cs

using Microsoft.EntityFrameworkCore;
using System;

namespace GlobalFilterExample.Model
{
    public class EntityModelContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(local);Database=Test;user Id=sa; password=Passwd@12;");
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
        public DbSet<Employee> Employees { get; set; }
    }
}

C#

Copy

In the next step, configure the global query filter using the OnModelCreating method of the context class. Using HasQueryFilter API, we can apply a global filter on the entity type.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
        .HasQueryFilter(p => !p.IsDeleted);
    base.OnModelCreating(modelBuilder);
}

C#

Copy

The expression passed in the HasQueryFilter method is automatically applied to any LINQ queries for Employee Type.

Example code

using (EntityModelContext context = new EntityModelContext())
{
    Console.WriteLine("---------------With Global Query Filters---------------");
    var data = context.Employees.ToList();
    foreach (var d in data)
    {
        Console.WriteLine("{0}\t{1}", d.Id, d.Name);
    }
    Console.ReadLine();
}

C#

Copy

Output

Disabling Global Filters

The global filters are applied using any LINQ query. In some cases, we do not require these filters. The global filters may be disabled for individual LINQ queries by using the IgnoreQueryFilters() method.

Example

var data1 = context.Employees
    .IgnoreQueryFilters().ToList();

foreach (var d in data1)
{
    Console.WriteLine("{0}\t{1}", d.Id, d.Name);
}

C#

Copy

Output

Limitations

It has the following limitations.

• It cannot contain references to navigation properties
• It can be defined only at the root Entity Type of an inheritance hierarchy
• IgnoreQueryFilters method ignores all the filters on the entity type; i.e., we cannot remove particular filters using this method

Summary

The Global Query Filter or Model-Level Query Filter is a very useful feature introduced in entity framework code. It helps us to apply filters on entity types that a developer might forget during development.

You can view or download the source code from the following GitHub link.