I'm experiencing significant performance issues due to the soft delete and multitenant filters. Is there a way to override them and use my own user-defined function?
Queries are being generated with an OR condition that makes SQL unable to seek my index
AND (@__ef_filter__p_0 = 1 OR [IsDeleted] = 0) AND (@__ef_filter__p_2 = 1 OR [TenantId] = @tenantId)
9 Answer(s)
-
0
- Solution
Short answer: Yes. Starting from ABP v8.3, you can switch ABP’s EF Core global filters (ISoftDelete, IMultiTenant, etc.) to a user-defined-function (UDF) mapping approach that removes the “(@__ef_filter__p_X = 1 OR …)” pattern and generates index-friendly predicates. You can also selectively disable filters per scope/query when needed.
Steps (apply in your DbContext):
- Indicate which entities should be filtered:
protected override bool ShouldFilterEntity<TEntity>(IMutableEntityType entityType) { if (typeof(ISoftDelete).IsAssignableFrom(typeof(TEntity)) || typeof(IMultiTenant).IsAssignableFrom(typeof(TEntity))) { return true; } return base.ShouldFilterEntity<TEntity>(entityType); }- Use UDF mapping for the filter so EF generates sargable SQL without the “OR @ef_filter...”:
using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata; using Microsoft.EntityFrameworkCore.Metadata.Builders; using Microsoft.EntityFrameworkCore.Query.SqlExpressions; using System.Linq.Expressions; using Volo.Abp.Data; using Volo.Abp.Domain.Entities; using Volo.Abp.MultiTenancy; using Volo.Abp.EntityFrameworkCore; public class MyProjectDbContext : AbpDbContext<MyProjectDbContext> { protected bool SoftDeleteEnabled => DataFilter?.IsEnabled<ISoftDelete>() ?? false; protected bool MultiTenantEnabled => DataFilter?.IsEnabled<IMultiTenant>() ?? false; public MyProjectDbContext(DbContextOptions<MyProjectDbContext> options) : base(options) { } protected override Expression<Func<TEntity, bool>> CreateFilterExpression<TEntity>(ModelBuilder modelBuilder) { var expression = base.CreateFilterExpression<TEntity>(modelBuilder); // ISoftDelete if (typeof(ISoftDelete).IsAssignableFrom(typeof(TEntity))) { Expression<Func<TEntity, bool>> softDeleteFilter = e => !SoftDeleteEnabled || !EF.Property<bool>(e, "IsDeleted"); if (UseDbFunction()) { softDeleteFilter = e => SoftDeleteUdf(((ISoftDelete)e).IsDeleted, true); var current = this.GetService<AbpEfCoreCurrentDbContext>(); modelBuilder.HasDbFunction(typeof(MyProjectDbContext).GetMethod(nameof(SoftDeleteUdf))!) .HasTranslation(args => { var isDeleted = args[0]; var boolParam = args[1]; if (current.Context?.DataFilter.IsEnabled<ISoftDelete>() == true) { // IsDeleted = 0 return new SqlBinaryExpression( ExpressionType.Equal, isDeleted, new SqlConstantExpression(Expression.Constant(false), boolParam.TypeMapping), boolParam.Type, boolParam.TypeMapping); } // no WHERE fragment when disabled return new SqlConstantExpression(Expression.Constant(true), boolParam.TypeMapping); }); } expression = expression == null ? softDeleteFilter : QueryFilterExpressionHelper.CombineExpressions(expression, softDeleteFilter); } // IMultiTenant if (typeof(IMultiTenant).IsAssignableFrom(typeof(TEntity))) { Expression<Func<TEntity, bool>> tenantFilter = e => !MultiTenantEnabled || EF.Property<Guid?>(e, "TenantId") == CurrentTenant.Id; if (UseDbFunction()) { tenantFilter = e => TenantUdf(((IMultiTenant)e).TenantId, CurrentTenant.Id); var current = this.GetService<AbpEfCoreCurrentDbContext>(); modelBuilder.HasDbFunction(typeof(MyProjectDbContext).GetMethod(nameof(TenantUdf))!) .HasTranslation(args => { var entityTenantId = args[0]; var currentTenantId = args[1]; if (current.Context?.DataFilter.IsEnabled<IMultiTenant>() == true) { // TenantId = @CurrentTenantId return new SqlBinaryExpression( ExpressionType.Equal, entityTenantId, currentTenantId, entityTenantId.Type, ((SqlExpression)entityTenantId).TypeMapping); } return new SqlConstantExpression(Expression.Constant(true), ((SqlExpression)entityTenantId).TypeMapping); }); } expression = expression == null ? tenantFilter : QueryFilterExpressionHelper.CombineExpressions(expression, tenantFilter); } return expression; } public static bool SoftDeleteUdf(bool isDeleted, bool _) => throw new NotSupportedException(AbpEfCoreDataFilterDbFunctionMethods.NotSupportedExceptionMessage); public static bool TenantUdf(Guid? tenantId, Guid? currentTenantId) => throw new NotSupportedException(AbpEfCoreDataFilterDbFunctionMethods.NotSupportedExceptionMessage); public override string GetCompiledQueryCacheKey() { return $"{base.GetCompiledQueryCacheKey()}:{SoftDeleteEnabled}:{MultiTenantEnabled}:{CurrentTenant.Id}"; } private static bool UseDbFunction() => true; // keep as feature flag if you need }Resulting SQL examples:
When filters enabled:
- Soft delete: WHERE [IsDeleted] = 0
- Tenant: WHERE [TenantId] = @tenantId
When disabled: no WHERE fragment is emitted for that filter. This removes the “(@__ef_filter__p_X = 1 OR …)” form and allows index seeks on IsDeleted/TenantId aligned indexes.
Disabling or customizing filters per scope/query:
- Disable all soft delete (for the scope):
using (DataFilter.Disable<ISoftDelete>()) { // query without soft-delete }- Disable multi-tenancy (for cross-tenant maintenance, etc.):
using (DataFilter.Disable<IMultiTenant>()) { // query without tenant constraint }- Disable for a single LINQ operation (typical EF Core pattern):
var q = (await _repo.GetQueryableAsync()) .IgnoreQueryFilters(); // if you really need to bypass everythingIndexing tips:
- Ensure indexes match the new predicates. Common patterns:
- CREATE INDEX IX_AppTable_TenantId ON AppTable(TenantId)
- CREATE INDEX IX_AppTable_IsDeleted ON AppTable(IsDeleted)
- For multi-tenant tables, compound indexes often help: (TenantId, IsDeleted, <frequent column>)
- Ensure indexes match the new predicates. Common patterns:
Notes:
- The UDF methods in C# throw NotSupportedException by design; they are only placeholders for translation.
- Remember to include the filter state in GetCompiledQueryCacheKey to avoid wrong plan caching across different filter states.
- Sources
- ABP Data Filtering and UDF-based global filters (validated):
- https://abp.io/docs/latest/framework/infrastructure/data-filtering
- EF Core UDF mapping (official):
- https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping
- ABP Framework repo (reference to EF Core filter infra):
- https://github.com/abpframework/abp
This response was generated by AI. If it didn’t solve your problem, feel free to post a new answer — our support team will jump in to help you soon.
Markdown supported.Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post) -
0
I still need help, I implemented AI's suggestion, but the entities that implement ISfotDelete are not being fetched at all
Markdown supported.Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post) -
0
hi
What is your ABP version?
We added a new feature after 8.3.0
see https://abp.io/community/articles/use-userdefined-function-mapping-for-global-filter-pht26l07
Thanks
Markdown supported.Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post) -
0
My version is: 9.2.3
I saw the article. Could you provide more guidance on how to override the soft delete and the multitenant filters? I followed the recommendation from the AI, but I'm still getting the OR clause.
It might be because the sample method override starts with this:
var expression = base.CreateFilterExpression<TEntity>(modelBuilder);
So, it might be creating the ABP framework filters and not my custom ones. Any ideas or feedback on that?
Thanks.
Markdown supported.Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post) -
0
hi
If your package version is
9.2.3then there should be noAND (@__ef_filter__p_0 = 1 OR [IsDeleted] = 0)query.Can you share a simple project?
liming.ma@volosoft.com
Thanks.
Markdown supported.Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post) -
0
Unfortunately, I don't have the time to build one. Is it something I need to enable somehow?
Markdown supported.Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post) -
0
This is not a new project; it started on version 7.0.3.
I searched everywhere in my codebase for any changes to the filter code and couldn't find anything. Could there be something new in the modules configuration that I need to add/include since then to make it work natively instead of overriding the built-in filters?
Overriding the "CreateFilterExpression" is working now, but I would prefer not to do it.
Thanks.
Markdown supported.Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post)
