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!
Comments
No one has commented yet, be the first to comment!