Global Query Filters in Entity Framework Core

Back to: ASP.NET Core Tutorials For Beginners and Professionals

Global Query Filters in Entity Framework Core

In this article, I will discuss Global Query Filters in Entity Framework Core (EF Core) with Examples. Please read our previous article discussing Shadow Properties in Entity Framework Core with Examples. At the end of this article, you will understand the following pointers:

1. What are Global Query Filters in Entity Framework Core?
2. Soft Delete Example in EF Core Using Global Query Filter
3. Bypassing the Global Query Filter in Entity Framework Core
4. Multi-Tenancy Example in EF Core Using Global Query Filter
5. Data Security Example in EF Core Using Global Query Filter
6. When to Use Global Query Filters in EF Core?
7. When Not to Use Global Query Filters in EF Core?

What are Global Query Filters in Entity Framework Core?

Global Query Filters in Entity Framework Core (EF Core) allow us to define query criteria that are automatically applied to all queries across a particular entity type. This can be useful for things like soft delete functionality, multi-tenancy, or any scenario where you have a common filtering logic that should be applied globally.

Key Points of Global Query Filters in Entity Framework Core:

• Definition: You can define Global Query Filters in the OnModelCreating method of your DbContext class. It uses LINQ to specify the filter conditions. This is done by using the HasQueryFilter method on an entity type builder.
• Automatic Application: Once the Global Query Filter is defined, the filter is automatically applied to every LINQ query that retrieves data of the specified entity type. This includes direct queries and indirect data retrievals, such as navigation properties.
• Usage with LINQ Queries: When you use LINQ to query your entities, EF Core automatically applies these filters, so you don’t need to specify the filter conditions each time.
• Disabling Filters: In certain scenarios, you might want to bypass these filters (e.g., for an admin role). EF Core allows us to ignore filters using the IgnoreQueryFilters method in your queries.
• Parameterized Filters: EF Core also supports parameterized filters, allowing you to pass parameters into your Global Query Filters. This is particularly useful for scenarios like multi-tenancy, where the tenant ID might be determined at runtime.
• Performance: While Global Query Filters can improve performance by reducing the need for repeated filtering logic in queries, they can also potentially impact performance if not used properly, as they add additional criteria to all queries against the filtered entity types.

Common Uses of Global Query Filter in EF Core:

• Soft Delete: A common use case for Global Query Filters is implementing soft delete functionality. Instead of physically deleting records from the database, a flag (e.g., IsDeleted) is set. By using a filter like builder.Entity<MyEntity>().HasQueryFilter(e => !e.IsDeleted);, you can automatically exclude logically deleted entities from all queries of the entity MyEntity.
• Multi-Tenancy: In a multi-tenant application, a filter can ensure that each query only retrieves data belonging to the tenant associated with the current user. For instance, you might filter entities based on a tenant ID.
• Security: Filtering data based on user roles or permissions.

Soft Delete Example in EF Core Using Global Query Filter

Let us see an example of implementing soft delete functionality using Global Query Filters in Entity Framework Core (EF Core). The soft delete pattern marks entities as deleted without removing them from the database.

Define the Entity with a Soft Delete Property:

First, you need an entity with a property to indicate whether it is deleted. This is typically a boolean flag. So, create a class file named Product.cs and copy and paste the following code. In our example, it is the IsDeleted property, which indicates whether a product is deleted.

namespace EFCoreCodeFirstDemo.Entities
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool IsDeleted { get; set; } // Soft delete flag
    }
}

Configure the Global Query Filter in DbContext:

In your DbContext class, override the OnModelCreating method to define the global query filter. This filter will automatically exclude entities marked as deleted in all queries. So, modify the DbContext class as follows:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //To Display the Generated SQL
            optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

            //Configuring the Connection String
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
        }

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

            // Global Query Filter for soft delete
            modelBuilder.Entity<Product>().HasQueryFilter(e => !e.IsDeleted);
        }

        public DbSet<Product> Products { get; set; }
    }
}

Generate Migration and Apply Database Changes:

Open the Package Manager Console and Execute the add-migration and update-database commands as follows. You can give any name to your migration. Here, I am giving Mig1. The name that you are giving it should not be given earlier.

We need demo data to check whether the Global Query Filter works. So, please execute the following insert statement into the Product database tables. Please note that we have a few records whose IsDeleted column value is set to false.

INSERT INTO Products VALUES ('Product-1', 1); -- 1 Means True, i.e., Deleted
INSERT INTO Products VALUES ('Product-2', 0); -- 0 Means False, i.e., Not Deleted
INSERT INTO Products VALUES ('Product-3', 1); -- 1 Means True, i.e., Deleted
INSERT INTO Products VALUES ('Product-4', 0); -- 0 Means False, i.e., Not Deleted

Querying Entities:

When you query Products, the global query filter is automatically applied, and ‘deleted’ product entities are excluded from the result set. For a better understanding, please modify the Main method of the Program class as follows:

using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                using var context = new EFCoreDbContext();
                var AllActiveProducts = context.Products.ToList(); // Returns only non-deleted entities
                foreach (var product in AllActiveProducts)
                {
                    Console.WriteLine($"ID:{product.Id}, Name:{product.Name}, IsDeleted:{product.IsDeleted}");
                }
                Console.Read();
            }

            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Output:

As you can see in the above output, the SQL Select Query applies with the Global Query Filter in the Where Clause to fetch only those not deleted records.

Bypassing the Global Query Filter in Entity Framework Core:

In cases where you need to access all entities, including those marked as deleted (like for an admin view), you can bypass the global query filter in EF Core using the IgnoreQueryFilters() method. For a better understanding, modify the Program class as follows:

using EFCoreCodeFirstDemo.Entities;
using Microsoft.EntityFrameworkCore;

namespace EFCoreCodeFirstDemo
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                using var context = new EFCoreDbContext();
                var AllProducts = context.Products.IgnoreQueryFilters().ToList(); // Includes deleted entities
                foreach (var product in AllProducts)
                {
                    Console.WriteLine($"ID:{product.Id}, Name:{product.Name}, IsDeleted:{product.IsDeleted}");
                }
                Console.Read();
            }

            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Output:

Considerations: Add an index to the IsDeleted column for performance optimization, especially if your table grows large.

Multi-Tenancy Example in EF Core Using Global Query Filter:

Implementing multi-tenancy in Entity Framework Core using Global Query Filters involves setting up your entities to be filtered based on a tenant identifier. This ensures that each tenant can only access their own data. Let us see how we can implement this using Entity Framework Core Using Global Query Filter:

Defining a Tenant Entity and Tenant Service

First, you might have a Tenant entity and a service to determine the current tenant based on the user’s session, domain, or other criteria. Here, we have hardcoded the Tenant ID value.

namespace EFCoreCodeFirstDemo.Entities
{
    public class Tenant
    {
        public int TenantId { get; set; }
        public string Name { get; set; }
        // Other properties...
    }

    public interface ITenantService
    {
        int GetCurrentTenantId();
    }

    public class TenantService : ITenantService
    {
        public int GetCurrentTenantId()
        {
            // Implementation to retrieve the current tenant ID.
            // ITenantService needs to identify the current tenant.
            // This might involve parsing a subdomain, reading a cookie, or checking user credentials:
            return 1;
        }
    }
}

Modifying Your Entities to Include TenantId

Each entity that should be tenant-specific needs a TenantId field. Please modify the Product class as follows:

namespace EFCoreCodeFirstDemo.Entities
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int TenantId { get; set; }
    }
}

Setting Up the DbContext:

Inject the ITenantService into your DbContext and use it in the OnModelCreating method to apply the Global Query Filter. In your DbContext class, override the OnModelCreating method to define the global query filter. This filter will automatically include entities based on the TenantId.

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        private ITenantService _tenantService;
       
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //Configuring the Connection String
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
        }

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

            //You can Inject the TenantService instance via Constructor
            _tenantService = new TenantService();
            int currentTenantId = _tenantService.GetCurrentTenantId();

            // Global Query Filter
            modelBuilder.Entity<Product>()
                .HasQueryFilter(e => e.TenantId == currentTenantId);
        }

        public DbSet<Product> Products { get; set; }
    }
}

Next, generate migration and update the database. Once you update the database, execute the following SQL Script.

INSERT INTO Products (Name, TenantId) VALUES ('Product-1', 1); 
INSERT INTO Products (Name, TenantId) VALUES ('Product-2', 2);
INSERT INTO Products (Name, TenantId) VALUES ('Product-3', 2); 
INSERT INTO Products (Name, TenantId) VALUES ('Product-4', 1);

Querying Entities

When querying Products, the global filter is automatically applied. Modify the Program class as follows and then verify the output:

using EFCoreCodeFirstDemo.Entities;
using Microsoft.EntityFrameworkCore;

namespace EFCoreCodeFirstDemo
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                using var context = new EFCoreDbContext();
                var TenantProducts = context.Products.ToList(); // Product Based on the Current TenantId
                var AllTenantProducts = context.Products.IgnoreQueryFilters().ToList();

                foreach (var product in TenantProducts)
                {
                    Console.WriteLine($"ID:{product.Id}, Name:{product.Name}, TenantId:{product.TenantId}");
                }
                Console.Read();
            }

            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Output:

Considerations:

• Security: It’s important to ensure the tenant ID cannot be tampered with, especially in multi-user or public-facing applications.
• Performance: Be careful of performance implications, especially if you have many tenants or a large dataset.
• Indexing: Consider indexing the TenantId field for performance optimization.

Data Security Example in EF Core Using Global Query Filter

Implementing data security in Entity Framework Core using Global Query Filters involves creating filters based on security levels or user roles. This ensures that users can only access data they are authorized to see. Let’s go through a detailed example where we implement a security level-based data access control:

Define a Security Level Property in Your Entities

Add a SecurityLevel property to each entity that needs to be secured. This property indicates the required clearance level to access the entity. So, modify the Product class as follows to include the SecurityLevel property.

namespace EFCoreCodeFirstDemo.Entities
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int SecurityLevel { get; set; } // Security level of the entity
    }
}

Configure the Global Query Filter in DbContext

In your DbContext class, override the OnModelCreating method to include the global query filter. This filter will ensure that users can only access data that matches or is below their security clearance. So, modify the DbContext class as follows:

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //Configuring the Connection String
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
        }

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

            //You can inject this value based on the login user from sessions or cookies
            //Here, we have hardcoded the value to 2
            int _userSecurityLevel = 2;

            // Global Query Filter for data security
            modelBuilder.Entity<Product>().HasQueryFilter(e => e.SecurityLevel <= _userSecurityLevel);
        }

        public DbSet<Product> Products { get; set; }
    }
}

Next, generate migration and update the database. Once you update the database, execute the following SQL Script.

INSERT INTO Products (Name, SecurityLevel) VALUES ('Product-1', 1); 
INSERT INTO Products (Name, SecurityLevel) VALUES ('Product-2', 2);
INSERT INTO Products (Name, SecurityLevel) VALUES ('Product-3', 2); 
INSERT INTO Products (Name, SecurityLevel) VALUES ('Product-4', 1); 
INSERT INTO Products (Name, SecurityLevel) VALUES ('Product-5', 3); 
INSERT INTO Products (Name, SecurityLevel) VALUES ('Product-6', 3);
INSERT INTO Products (Name, SecurityLevel) VALUES ('Product-7', 2); 
INSERT INTO Products (Name, SecurityLevel) VALUES ('Product-8', 1);

Querying Entities

When querying SecureEntities, the global filter is automatically applied. Please modify the Program class as follows and then verify the output:

using EFCoreCodeFirstDemo.Entities;
using Microsoft.EntityFrameworkCore;

namespace EFCoreCodeFirstDemo
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                using var context = new EFCoreDbContext();
                var ProductsBySecurityLevel = context.Products.ToList(); // Product Based on the Current user SecurityLevel
                var AllProducts = context.Products.IgnoreQueryFilters().ToList(); //All Products

                foreach (var product in ProductsBySecurityLevel)
                {
                    Console.WriteLine($"ID:{product.Id}, Name:{product.Name}, SecurityLevel:{product.SecurityLevel}");
                }
                Console.Read();
            }

            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Output:

When to Use Global Query Filters in EF Core?

Global Query Filters in Entity Framework Core are powerful for applying automatic filtering logic to all queries against a particular entity type. Here are scenarios when it’s particularly useful to use them:

• Soft Delete: In applications where entities are not permanently deleted but marked as inactive or deleted (soft delete), Global Query Filters can automatically exclude these records from all queries.
• Multi-Tenancy: When building applications that serve multiple tenants (like different companies or groups), Global Query Filters can ensure that each tenant only accesses their own data by filtering based on tenant ID.
• Row-Level Security: Global Query Filters can enforce these rules if your application has complex security requirements where users have access to only certain rows of data (based on their role or department).
• Data Partitioning: For applications where data needs to be partitioned (like based on region, department, etc.), Global Query Filters can automatically segregate data accordingly in every query.
• Audit or Historical Data: In scenarios where you want to keep historical or audit data in the same table but normally query only the current data, a Global Query Filter can exclude historical records from standard queries.
• Common Conditions: If a common condition almost always applies to queries of a particular entity (like only showing active users or products in stock), a Global Query Filter can enforce this condition by default.

When Not to Use Global Query Filters in EF Core?

• Performance Considerations: If your filter conditions are complex and could lead to performance issues, applying filters at the query level might be better.
• Global Impact: Remember that Global Query Filters impact every query against the entity. If there are many scenarios where the filter needs to be bypassed (using IgnoreQueryFilters), applying filters at the query level might be better.
• Simplicity: For simpler applications or when filters are not consistently required across all queries, applying filters directly in queries might be more straightforward and maintainable.

In the next article, I will discuss the Entity Framework Core Database First Approach with Examples. In this article, I try to explain Global Query Filters in Entity Framework Core (EF Core) with Examples. I hope you enjoy this Global Query Filters in Entity Framework Core article.