Query Filters in Entity Framework Core & ASP.NET

One of the main reasons I like ASP.NET so much is that it has great tooling for working with data. Entity Framework is the standard ORM in the ASP.NET framework. EF provides powerful features like LINQ queries, migrations, and change tracking. These features allow developers to create sophisticated business applications with relative ease.

A powerful yet lesser-known feature in Entity Framework is query filtering. Query filters enable developers to set global data retrieval conditions. It’s like adding a WHERE clause to every query, but only needing to do it in one place. If you are trying to learn how to build multitenant apps, understanding the capabilities of query filters is essential. Definitely check out the Nano ASP.NET Boilerplate which implements the concepts covered in this tutorial and many more, with complete documentation.

In this article we will implement a soft-delete feature with the help of query filters. Soft deletion is a common feature in many applications, that when a user deletes a record from the database, its not actually removed, but rather marked as deleted. Later, when retrieving records, the entities marked as deleted are not returned. This approach provides the ability to retain data for historical or auditing purposes while maintaining data integrity.

A soft-delete feature is small in scope and complexity but demonstrates the core functionality of query filters. Once you understand the fundamentals of query filters, you will begin to see how they could be used in more complex scenarios like multi-tenancy.  

Download the sample app code from Github here to follow along.

Prerequisites

• Basic knowledge of ASP.NET and C#
• Familiarity with Entity Framework
• Understanding of Dependency Injection and Interfaces

Project Setup

Begin by creating a Web API project with no authentication. The project we create is going to be very simple and it will only contain one entity called Product, with one service called ProductService, and one controller called ProductsController. Its really not even necessary to create a Product Service – we could instead just put all of our business logic in the Products Controller but we will stick to the service pattern and use dependency injection because its good practice. If you are unfamiliar with the service pattern, interfaces, or dependency injection, be sure to read the other tutorials I’ve published on those topics in the blog.

Add Entity Framework

We will begin by adding the necessary Nuget packages for Entity Framework. Right click on the project in the solution explorer and choose Manage Nuget Packages. There are three that we need to install:

• Entity Framework Core
• Entity Framework SqlServer
• Entity Framework Tools

Next, create a Models folder and create a class called ApplicationDbContext. This class should inherit from the DbContext class which is provided by the Entity Framework Core nuget package. The code should look like this at this point:

public class ApplicationDbContext : DbContext
{

        // Constructor -- convention used by Entity Framework 
        public ApplicationDbContext(DbContextOptions options) : base(options)
        {
        }
  
}

In the appsettings.json file, create an entry for connection strings and name it default connection like this:

  "ConnectionStrings": {
    "DefaultConnection": "Data Source=(localdb)\\mssqllocaldb;Database=queryFilterAppDb;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

In program.cs add this line to add a database connection as a service to the application:

// adding a database service with configuration -- connection string read from appsettings.json
builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

With those steps out of the way, the application is configured to use a database with Entity Framework. Later we will scaffold an initial migration and create a database called queryFilterAppDb.

Create the Product Entity

In the Models folder, create one entity named Product with the following properties. Make one of the fields a boolean property called IsDeleted. The Product class should look like this:

public class Product 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public bool IsDeleted { get; set; }
}

Let’s remember to create a DbSet for our Product entity in the ApplicationDbContext like so:

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

We will come back to the ApplicationDbContext in a moment for the real fun with query filtering but for now let’s just focus on setting up the application.

Create the Product Service

Create a folder called Services in the top level of the application and within that folder, another folder called ProductService. Here we will create a class called ProductService and an interface called IProductService like this:

public interface IProductService
{
    IEnumerable<Product> GetAllProducts();
    Product GetProductById(int id);
    Product CreateProduct(CreateProductRequest request);
    bool DeleteProduct(int id);
}

Above is the interface, and below is the service that contains our business logic for managing Products

public class ProductService : IProductService
{
    private readonly ApplicationDbContext _context; // database context
    public ProductService(ApplicationDbContext context)
    {
        _context = context;
    }

    // get a list of all products
    public IEnumerable<Product> GetAllProducts()
    {
        var products = _context.Products.ToList();
        return products;
    }

    // get a single product
    public Product GetProductById(int id)
    {
        var product = _context.Products.Where(x => x.Id == id).FirstOrDefault();
        return product;
    }

    // create a new product
    public Product CreateProduct(CreateProductRequest request)
    {
        var product = new Product();
        product.Name = request.Name;
        product.Price = request.Price;

        _context.Add(product);
        _context.SaveChanges();

        return product;
    }


    // delete a product
    public bool DeleteProduct(int id)
    {
        var product = _context.Products.Where(x => x.Id == id).FirstOrDefault();

        if (product != null)
        {
            _context.Remove(product);
            _context.SaveChanges();
            return true;
        }
        return false;
    }
}

For the Create method, create a DTO (data transfer object) class for a new product request. In this case, I’ve named mine CreateProductRequest and placed it into a DTOs folder within the ProductService folder, like so:

    public class CreateProductRequest
    {
        public string Name { get; set; }
        public decimal Price { get; set; }
    }

Next, we will need to register the service in the top-level program.cs class as a transient service. Also in the program.cs we will configure our database to use ApplicationDBContext with SqlServer and a connection string which is specified in appsettings.json. The program.cs class should look like this when its all finished:

using Microsoft.EntityFrameworkCore;
using queryFilterApp.Models;
using queryFilterApp.Services.ProductService;

var builder = WebApplication.CreateBuilder(args); // <--- 1. Create the Builder (ASP.NET convention)

// default services added when creating 'web api project' template
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();

// adding a database service with configuration -- connection string read from appsettings.json
builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// adding our own service -- CRUD services should be registered with transient lifetimes
builder.Services.AddTransient<IProductService, ProductService>();


var app = builder.Build(); // <--- 2. Build the App (ASP.NET convention)


// default middleware when choosing create 'web api project'
app.UseHttpsRedirection(); 
app.UseAuthorization(); 
app.MapControllers();

app.Run(); // <--- 3. Run the App (ASP.NET convention)

Finally, we will create a Products controller with Get, Post, and Delete endpoints. The controller will inject an instance of the Product Service as is typically done in ASP.NET applications. Nothing fancy so far!

    [Route("api/[controller]")]
    [ApiController]
    public class ProductsController : ControllerBase
    {
        private readonly IProductService _productService; 

        public ProductsController(IProductService productService)
        {
            _productService = productService; // inject the products service
        }
   
        // Get list of products
        [HttpGet]
        public IActionResult Get()
        {   
            var list = _productService.GetAllProducts(); 
            return Ok(list);
        }

        // Create a new product
        [HttpPost]
        public IActionResult Post(CreateProductRequest request)
        {
            var result = _productService.CreateProduct(request);
            return Ok(result);
        }

        // Delete a product by id
        [HttpDelete("{id}")]
        public IActionResult Delete(int id)
        {
            var result = _productService.DeleteProduct(id);
            return Ok(result);
        }

    }

Before we run the application we will need to create an initial migration and run the update-database command to actually create the database and tables. Note that if you are downloading the sample project on GitHub, the initial migration has already been created, and all you need to do is run the update-database command in the Package Manager Console.

Run the Application

Now that we have a basic application set up with a database, product service, and controller, run the application.

Open up Postman or whatever your preferred tool is for testing API endpoints, and let’s start by creating some products.

Create a post request with a CreateProductRequest json object in the body, to send to https://localhost:7237/api/Products

Note that the localhost address in your case may be different if you are not using the GitHub example provided.

This is what the Post request looks like in postman, with Name being a string value and Price a decimal.

Create a few more products to populate our database, and once you’re done you can test the Get method to get a list a products like so:

If we were to test our last endpoint, the Delete endpoint, we already know what will happen. We could send the delete request with one of the ID numbers and that product would be removed from the database.

What we want to do instead is tell Entity Framework to change the IsDeleted property to true when it receives a delete request for the Product entity. Then we can then change our Get request to filter out any products that have a true value in the IsDeleted attribute. With that, we will have implemented a soft delete feature in our application (on Product entities).

The problem is that adding a WHERE clause to any place we retrieve products would be tedious, and if we ever add more entities to our application, we will need to add the WHERE clause to those queries as well. Instead of that hot mess, we could use a global query filter to apply the clause to every query. Furthermore, we can use the query filter to apply the clause to other entities we add to our application, not just Products.

This is where the fun begins. Let’s start with the first part, intercepting delete requests in Entity Framework and marking the IsDeleted property to true.

Overriding On Save Changes in ApplicationDbContext

As the title suggests, we are going to override the Save Changes method of Entity Framework. Its very common to override the Save Changes method. Overriding just means that you are adding actions that happen during a particular phase of an operation.

In the ApplicationDbContext class, add this code block after the DbSet declarations:

public override int SaveChanges()
{
      foreach (var entry in ChangeTracker.Entries<Product>().ToList()) 
      {
          switch (entry.State)
          {
                case EntityState.Deleted:   // intercept delete requests, mark field as deleted       
                        entry.Entity.IsDeleted = true;
                        entry.State = EntityState.Modified; 
                        break;
                }
            }

       var result = base.SaveChanges();
       return result;
}

With this code in place, entity framework will take a difference course of action with Product entities. We tell EF that for any changes it has in regards to Product entities, check for delete requests (EntityState.Deleted) and for those requests change the IsDeleted property to true. We then send the request along but as EntityState.Modified, so that EF handles the request like an update request.

The foreach loop can be improved further, by looping through entities that implement a particular interface, instead of one specific entity type. For example, if you had Products, Customers, Orders, etc. create an interface type called ISoftDelete, with a mandatory IsDeleted field. Then have all the entities needing soft-delete implement this interface type and in the SaveChanges override, loop through Entries<ISoftDelete>.

Create the Global Query Filter in OnModelCreating

At this point your excitement levels must be through the roof, because you know that the next part is the best part. So without further ado, here is the final piece of the puzzle, the almighty global query filter:

    protected override void OnModelCreating(ModelBuilder builder)
        {
            // filter out deleted entities (soft delete)
            builder.Entity<Product>().HasQueryFilter(s => s.IsDeleted == false); 
        }

Once again we are using the override technique to append actions onto a particular phase of a process, in this case, Entity Framework’s OnModelCreating method. Unlike SaveChanges which fires any time data is persisted to the database, OnModelCreating fires only once, during the application startup.

It is here that we can add a global query filter using the method HasQueryFilter() and then passing a lambda expression as a parameter.

With this query filter in place anytime we retrieve Product entities, records that have a value of true for IsDeleted will be omitted. As you can see this is quite powerful and surprisingly simple. Go ahead and make sure its all working by deleting one of the products, and inspecting the database table. If all goes correctly, the record will still be there in the table but its IsDeleted value will be set to true. When you go to retrieve the list of products, the soft-deleted record won’t be returned.

If you add new entity types that also need soft-delete query filters, you can add new lines for them in the OnModelCreating method. Sadly there isn’t a quick and easy way to add the same query filter to all entity types that implement a particular interface like we did in the SaveChanges override.

If you wanted to achieve that functionality you could create an extension method to the ModelBuilder class to handle that, but the code for that is rather advanced. The Nano ASP.NET Boilerplate project utilizes such an extension method for its soft-delete feature as well as audit fields and multi-tenancy.

No matter what level of skill you have as an ASP.NET developer, the Nano ASP.NET Boilerplate will save you a lot of time building your next application or simply advance your learning if you are trying to understand complex topics.

That concludes this tutorial and I hope you learned something about query filters along the way. To learn more about the Entity Framework, check out this related tutorial on capturing audit data. And if you think you are ready for an advanced topic, dive into my guide to multitenancy in ASP.NET (coming soon).