DEV Community

Cover image for EF Core HasQueryFilter II
Karen Payne
Karen Payne

Posted on

EF Core HasQueryFilter II

#csharp #dotnetcore #database

Introduction

EF Core has the HasQueryFilter extension method, which is a LINQ query predicate applied to EF Core entity models. This is especially useful in scenarios requiring soft deletion. A developer may need to view data without a filter, which is done using IgnoreQueryFilters extension method.

Source code

Learn how

  • Filter on properties rather than soft deletes and archiving, see the following for soft deletes.
  • Control filters using settings from appsettings.json 
{
  "ContextOptions": {
    "UseAuditInterceptor": true,
    "CustomersOptions": {
      "UseQueryFilter": true,
      "CountryCode": 20
    },
    "CategoryOptions": {
      "UseQueryFilter": true,
      "Id": 2
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Example 1

Using a customer table in a database, filter by country code.

Customers and Countries table and relations

We can hard-code a country code in the OnModelCreating method of the dbContext.

modelBuilder.Entity<Customer>()
    .HasQueryFilter(c =>
        EF.Property<int?>(c,
            nameof(Models.Customer.CountryIdentifier)) == 20); // 20 is the CountryIdentifier for USA in Northwind database
Enter fullscreen mode Exit fullscreen mode

Or use a bool and int properties from appsettings.json.

settings for Customer model in appsettings.json

Below HasQueryFilter is wrapped with an if condition to use HasQueryFilter or not.

if (ContextSettings.Instance.CustomerOptions.UseQueryFilter)
{

    modelBuilder.Entity<Customer>()
        .HasQueryFilter(c =>
            EF.Property<int?>(c,
                nameof(Models.Customer.CountryIdentifier)) ==
            ContextSettings.Instance.CustomerOptions.CountryCode);
}



public class CustomerOptions
{
    public bool UseQueryFilter { get; set; }
    public int CountryCode { get; set; }
}



public sealed class ContextSettings
{
    private static readonly Lazy<ContextSettings> Lazy = new Lazy<ContextSettings>(() => new ContextSettings());

    public static ContextSettings Instance => ContextSettings.Lazy.Value;

    public CustomerOptions CustomerOptions { get; set; }

      private ContextSettings()
    {
        // Config is set up in the project file to bind the configuration
        // to the ContextOptions class, so we can directly retrieve it here.
        ContextOptions options = Config.Configuration.JsonRoot()
            .GetSection(nameof(ContextOptions))
            .Get<ContextOptions>()!;

        CustomerOptions = options.CustomersOptions; 
    }
}
Enter fullscreen mode Exit fullscreen mode

Example 2

Uses the same logic as in example 1, this time to use HasQueryFilter for filtering on a category for products.

if (ContextSettings.Instance.CategoryOptions.UseQueryFilter)
{

    modelBuilder.Entity<Category>()
        .HasQueryFilter(c =>
            EF.Property<int?>(c,
                nameof(Models.Category.CategoryId)) ==
            ContextSettings.Instance.CategoryOptions.Id);

}
Enter fullscreen mode Exit fullscreen mode

Another option, perform a contains for multiple category identifiers.

appsettings.json

added an integer array

if (ContextSettings.Instance.CategoryOptions.UseQueryFilter)
{
    var ids = ContextSettings.Instance.CategoryOptions.Identifiers;
    modelBuilder.Entity<Category>()
        .HasQueryFilter(c =>
            ids.Contains(EF.Property<int>(c, nameof(Models.Category.CategoryId))));
}
Enter fullscreen mode Exit fullscreen mode

Sample code uses a conditional to try out both.

#if UseThis
            if (ContextSettings.Instance.CategoryOptions.UseQueryFilter)
            {

                modelBuilder.Entity<Category>()
                    .HasQueryFilter(c =>
                        EF.Property<int?>(c,
                            nameof(Models.Category.CategoryId)) ==
                        ContextSettings.Instance.CategoryOptions.Id);

            }
#else
            if (ContextSettings.Instance.CategoryOptions.UseQueryFilter)
            {
                var ids = ContextSettings.Instance.CategoryOptions.Identifiers;
                modelBuilder.Entity<Category>()
                    .HasQueryFilter(c =>
                        ids.Contains(EF.Property<int>(c, nameof(Models.Category.CategoryId))));
            }
#endif
Enter fullscreen mode Exit fullscreen mode

EF Core 10: Using multiple query filters

Introduces named query filters, which allow you to manage each filter separately, including selectively disabling one but not the other.

modelBuilder.Entity<Blog>()
    .HasQueryFilter("SoftDeletionFilter", b => !b.IsDeleted)
    .HasQueryFilter("TenantFilter", b => b.TenantId == tenantId);
Enter fullscreen mode Exit fullscreen mode

Summary

This article has provided some alternative uses of global filtering in EF Core beyond what most web examples offer, such as soft deletes and the ability to control filter enabling without recompiling the application.

Top comments (0)