Use User-Defined Function Mapping for Global Filter

Introduction

ABP provides data filters that can filter queries automatically based on some rules. This feature is useful for implementing multi-tenancy, soft delete, and other global filters. It uses EF Core's Global Query Filters system for the EF Core Integration.

EF Core Global Query Filters generate filter conditions and apply them to SQL queries. ABP controls whether this filter condition takes effect through a variable. However, this variable may cause performance losses in some scenarios.

The Filter Condition Variable

Think of a scenario with a global filter IIsActive, which filters out inactive entities:

public class Book : IIsActive
{
    public string Name { get; set; }

    public bool IsActive { get; set; } 
}

The SQL generated by the EF Core Global Query Filters is as follows:

SELECT * FROM [AppBooks] AS [a]
WHERE (@__ef_filter__p_0 = CAST(1 AS bit) OR [a].[IsActive] = CAST(1 AS bit))

The __ef_filter__p_0 variable controls whether the filter condition takes effect.

The generated SQL is not optimal, and some databases do not optimize it well.

Using User-defined function mapping for global filters

In the upcoming preview version of ABP, v8.3.0-rc.1, we start the User-defined function mapping to implement global filters more efficiently. This feature is enabled by default, so you don't need to make any changes if you create a new solution and start from scratch. Otherwise, you can enable it easily by following the instructions below.

To use this new feature for your custom global filters, you need to change your DbContext as follows:

protected bool IsActiveFilterEnabled => DataFilter?.IsEnabled<IIsActive>() ?? false;

protected override bool ShouldFilterEntity<TEntity>(IMutableEntityType entityType)
{
    if (typeof(IIsActive).IsAssignableFrom(typeof(TEntity)))
    {
        return true;
    }

    return base.ShouldFilterEntity<TEntity>(entityType);
}

protected override Expression<Func<TEntity, bool>> CreateFilterExpression<TEntity>(ModelBuilder modelBuilder)
{
    var expression = base.CreateFilterExpression<TEntity>(modelBuilder);

    if (typeof(IIsActive).IsAssignableFrom(typeof(TEntity)))
    {
        Expression<Func<TEntity, bool>> isActiveFilter = e => !IsActiveFilterEnabled || EF.Property<bool>(e, "IsActive");

        if (UseDbFunction())
        {
            isActiveFilter = e => IsActiveFilter(((IIsActive)e).IsActive, true);

            var abpEfCoreCurrentDbContext = this.GetService<AbpEfCoreCurrentDbContext>();
            modelBuilder.HasDbFunction(typeof(MyProjectNameDbContext).GetMethod(nameof(IsActiveFilter))!)
                .HasTranslation(args =>
                {
                    // (bool isActive, bool boolParam)
                    var isActive = args[0];
                    var boolParam = args[1];

                    if (abpEfCoreCurrentDbContext.Context?.DataFilter.IsEnabled<IIsActive>() == true)
                    {
                        // isActive == true
                        return new SqlBinaryExpression(
                            ExpressionType.Equal,
                            isActive,
                            new SqlConstantExpression(Expression.Constant(true), boolParam.TypeMapping),
                            boolParam.Type,
                            boolParam.TypeMapping);
                    }

                    // empty where sql
                    return new SqlConstantExpression(Expression.Constant(true), boolParam.TypeMapping);
                });
        }

        expression = expression == null ? isActiveFilter : QueryFilterExpressionHelper.CombineExpressions(expression, isActiveFilter);
    }

    return expression;
}

public static bool IsActiveFilter(bool isActive, bool boolParam)
{
    throw new NotSupportedException(AbpEfCoreDataFilterDbFunctionMethods.NotSupportedExceptionMessage);
}

public override string GetCompiledQueryCacheKey()
{
    return $"{base.GetCompiledQueryCacheKey()}:{IsActiveFilterEnabled}";
}

After these changes, the SQL generated by the EF Core Global Query Filters will be as follows:

Enabling the IIsActive filter:

SELECT * FROM [AppBooks] AS [a] WHERE 
[a].[IsActive] = CAST(1 AS bit)

Disabling the IIsActive filter:

SELECT * FROM [AppBooks] AS [a]

Conclusion

We have implemented global filters using User-defined function mapping, which can generate more efficient SQL and thus improve performance.

Upgrade to the latest ABP version and enjoy the performance improvement!

References