Explore Global Query Filters in EF Core

Query Filters

In this article we are going to check one of the features of Entity Framework Core, Global Query Filters; this was introduced first in EF Core 2.0 and is just a query predicate that will be appended to the end of where clause for queries on entities for which this feature has been activated. Some common scenarios would be Soft delete and Multi tenancy.

Consider that you are writing an application in which entities can be softly deleted, why not completely delete those entities? Just don’t do that, said Udi Dahan. Okay, let’s create an Author entity:

public class Author
{
    // omitted code

    public Guid Id { get; private set; }
    public string FirstName { get; private set; }
    public string LastName { get; private set; }
    public DateTime DateOfBirth { get; private set; }
    public bool IsDeleted { get; private set; }
}

If we want to add a query filter to the Author entity, that should be done either in OnModelCreating method of the DbContext, or in the IEntityTypeConfiguration<t> class related to entity T. For now, we could create a DbContext and override its OnModelCreating method, then configure the Author entity by telling the context to automatically filter out soft-deleted records, their IsDeleted property is true.

public class BookstoreDbContext : DbContext
{
    // omitted code

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<author>()
                    .HasQueryFilter(author => !author.IsDeleted);

        base.OnModelCreating(modelBuilder);
    }

    public DbSet<author> Authors { get; set; }
}

To achieve that, HasQueryFilter method is being used, it is defined on EntityTypeBuilder and takes an Expression of Func<author, bool=""> which means it will take an author instance and returns a boolean based on the conditions defined, in this case, the author not being (soft) deleted. Now, whenever you query the Author entity, this query will automatically get appended to your originally provided query on the where clause by the DbContext.

var authors = context.Authors.Where(author => author.LastName.StartsWith("joh"));

Resulting in a database query:

SELECT  a.*
FROM    dbo.Authors as a
WHERE   a.LastName  LIKE 'joh%'
    AND a.IsDelete  =     0

So far, so good. This could be a common scenario for almost all of your entities(aggregate roots, if interested) and we are reluctant to repeat the same code for every individual entity, Don’t Repeat Yourself.

Let’s first extract an interface, ICanBeSoftDeleted, which desired entities will implement that.

public interface ICanBeSoftDeleted
{
    bool IsDeleted{ get; set;}
}

public class Author : ICanBeSoftDeleted
{
    // omitted code
}

public class Book : ICanBeSoftDeleted
{
    // omitted code
}

Now we have to configure the common query filter for each entity, in the previous version we used a generic overload of modelBuilder.Entity<t> method, now it is not possible though, so we have to generate a Lambda Expression for each entity to be able to use the non-generic overload:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var softDeleteEntities = typeof(ICanBeSoftDeleted).Assembly.GetTypes()
                .Where(type => typeof(ICanBeSoftDeleted)
                                .IsAssignableFrom(type)
                                && type.IsClass
                                && !type.IsAbstract);

    foreach (var softDeleteEntity in softDeleteEntities)
    {
        modelBuilder.Entity(softDeleteEntity)
                    .HasQueryFilter(
                        GenerateQueryFilterLambda(softDeleteEntity)
                        );
    }

    base.OnModelCreating(modelBuilder);
}

Lambda Expressions

Let’s discover the GenerateQueryFilterLambda, it takes the type of the entity and will return a LambdaExpression of Func<type, bool="">, we should generate e => e.IsDeleted == false, right? See how to generate each part using Expressions in .Net.

private LambdaExpression GenerateQueryFilterLambdaExpression(Type type)
{
    // we should generate:  e => e.IsDeleted == false
    // or: e => !e.IsDeleted

    // e =>
    var parameter = Expression.Parameter(type, "e");

    // false
    var falseConstant = Expression.Constant(false);

    // e.IsDeleted
    var propertyAccess = Expression.PropertyOrField(parameter, nameof(ICanBeSoftDeleted.IsDeleted));

    // e.IsDeleted == false
    var equalExpression = Expression.Equal(propertyAccess, falseConstant);

    // e => e.IsDeleted == false
    var lambda = Expression.Lambda(equalExpression, parameter);

    return lambda;
}

Ignore Filters

Comments on top of each line indicating that what part of the (lambda) expression is going to be generated. This way we can simply implement the interface and our DbContext automatically detect and add the common global filter to our queries for the specified entities. At the end, whenever you want to disable query filter use IgnoreQueryFilters() on your LINQ query.

var authors = context.Authors
                .Where(author => author.LastName.StartsWith("joh"))
                .IgnoreQueryFilters();

Conclusion

Most of the time there are business query patterns that will apply globally on some entities in your application, by employing Query Filters of EF Core you could simply and easily implement such requirement. There is also a limitation, these filters can only be applied to the root entity of an inheritance hierarchy. Finally, you could find the source code for this article on GitHub. Have a great day and enjoy coding.

Buy Me a Coffee at ko-fi.com

Explore Shadow Properties in EF Core

We will take a look at another popular feature, Shadow Properties. When designing applications we tend to keep our code clean and simple, however there are times that you need to add properties other than what is required in your main business use cases, CreatedOn and LastUpdatedOn are such well-known properties.

Custom Configuration Providers in ASP.NET Core

A colleague of mine had a requirement in which he was interested to put the configuration settings of an ASP.NET Core application inside a SQL Server table and read them at startup of the application with a mechanism similar to what we already have for JSON files; also he wanted to have the ability that these variable being overridden or override other settings if applicable. In this article, I am going to describe how that could be achievable and how to implement such feature.

Get started with ASP.NET Core for non-C# developers

I had a discussion on a meet-up a month ago with a friend of mine, and he was telling me it is not really easy or clear how one could create a simple web application in .NET and C#. Hopefully this post is going to help that friend of mine! 😊

An error has occurred. This application may no longer respond until reloaded. Reload x