Entity Framework Core Feature - Global Query Filters

Introduction
 
This feature is also called 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 entity framework automatically adds the filter in the where clause before executing the LINQ queries. Usually Global query filters are applied in OnModelCreating method of 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 global query filter to implement soft-delete. To demostrate the example, I have created Employee table and it has the IsDeleted column that is used to define whether the record is deleted or not.
  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. SET ANSI_PADDING ON  
  6. GO  
  7. CREATE TABLE [dbo].[Employee](  
  8.     [Id] [intNOT NULL,  
  9.     [Name] [varchar](50) NULL,  
  10.     [IsDeleted] [bitNULL,  
  11.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  12. (  
  13.     [Id] ASC  
  14. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  15. ON [PRIMARY]  
  16.   
  17. GO  
  18. SET ANSI_PADDING OFF  
  19. GO  
  20. INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted]) VALUES (1, N'Jignesh', 0)  
  21. INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted]) VALUES (2, N'Rakesh', 0)  
  22. INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted]) VALUES (3, N'Tejas', 0)  
  23. INSERT [dbo].[Employee] ([Id], [Name], [IsDeleted]) VALUES (4, N'Rajesh', 1)  
 
 
First define entities and context class

Employee.cs
  1. using System.ComponentModel.DataAnnotations;  
  2. using System.ComponentModel.DataAnnotations.Schema;  
  3.   
  4. namespace GlobalFilterExample.Model  
  5. {  
  6.     [Table("Employee")]  
  7.     public class Employee  
  8.     {  
  9.         [Key]  
  10.         public int Id { get; set; }  
  11.         public string Name { get; set; }  
  12.         public bool IsDeleted { get; set; }  
  13.     }  
  14. }  
EntityModelContext.cs
  1. using Microsoft.EntityFrameworkCore;  
  2. using System;  
  3.   
  4. namespace GlobalFilterExample.Model  
  5. {  
  6.     public class EntityModelContext : DbContext  
  7.     {  
  8.         protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
  9.         {  
  10.             optionsBuilder.UseSqlServer(@"Server=(local);Database=Test;user Id=sa; password=Passwd@12;");  
  11.         }  
  12.   
  13.         protected override void OnModelCreating(ModelBuilder modelBuilder)  
  14.         {  
  15.             base.OnModelCreating(modelBuilder);  
  16.         }  
  17.   
  18.         public DbSet<Employee> Employees { getset; }  
  19.     }  
  20. }  
Next step, configure the global query filter in OnModelCreating method of context class. Using HasQueryFilter API, we can applied global filter on entity type.
  1. protected override void OnModelCreating(ModelBuilder modelBuilder)  
  2. {  
  3.     modelBuilder.Entity<Employee>()  
  4.         .HasQueryFilter(p => !p.IsDeleted);  
  5.   
  6.     base.OnModelCreating(modelBuilder);  
  7. }  
The expression passed in HasQueryFilter method is automatically applied to any LINQ queries for Employee Type.

Example code
  1. using (EntityModelContext context = new EntityModelContext())  
  2. {  
  3.     Console.WriteLine("---------------With Global Query Filters---------------");  
  4.     var data = context.Employees.ToList();  
  5.     foreach (var d in data)  
  6.     {  
  7.         Console.WriteLine("{0}\t{1}", d.Id, d.Name);  
  8.     }  
  9.   
  10.     Console.ReadLine();  
  11. }  
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
  1. var data1 = context.Employees  
  2.     .IgnoreQueryFilters().ToList();  
  3. foreach (var d in data1)  
  4. {  
  5.     Console.WriteLine("{0}\t{1}", d.Id, d.Name);  
  6. }  
Output

 
 
Limitations
 
It has the following limitations:
  • It cannot contain references to navigation properties
  • It can be defined only at 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 devlopment.
 
You can view or download the source code from the following GitHub link.

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now