efcore多个全局查询筛选器组合使用，软删除(IsDeleted)，多租户(TenantId)
两种方式组合
1、单表组合，每个表都设置各自的多个查询筛选器
来源：https://github.com/dotnet/efcore/issues/26146
https://github.com/Krzysztofz01/EFCore.QueryFilterBuilder/tree/master/EFCore.QueryFilterBuilder
用法：
a、新建接口 IQueryFilterBuilder.cs，把 https://github.com/Krzysztofz01/EFCore.QueryFilterBuilder/blob/master/EFCore.QueryFilterBuilder/IQueryFilterBuilder.cs 中的代码复制进去
1 using System; 2 using System.Linq.Expressions; 3 4 namespace EFCore.QueryFilterBuilder 5 { 6 public interface IQueryFilterBuilder<TEntity> where TEntity : class 7 { 8 Expression<Func<TEntity, bool>> Build(); 9 IQueryFilterBuilder<TEntity> AddFilter(Expression<Func<TEntity, bool>> expression, bool active = true); 10 IQueryFilterBuilder<TEntity> AddFilter(string filterName, Expression<Func<TEntity, bool>> expression, bool active = true); 11 IQueryFilterBuilder<TEntity> DisableFilter(string filterName); 12 IQueryFilterBuilder<TEntity> EnableFilter(string filterName); 13 } 14 }
b、新建类 QueryFilterBuilder.cs，把 https://github.com/Krzysztofz01/EFCore.QueryFilterBuilder/blob/master/EFCore.QueryFilterBuilder/QueryFilterBuilder.cs中的代码复制进去
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Linq; 5 using System.Linq.Expressions; 6 7 namespace EFCore.QueryFilterBuilder 8 { 9 public class QueryFilterBuilder<TEntity> : IQueryFilterBuilder<TEntity> where TEntity : class 10 { 11 private readonly Dictionary<string, QueryFilter> _queryFilters; 12 13 private QueryFilterBuilder() => 14 _queryFilters = new Dictionary<string, QueryFilter>(); 15 16 #region IQueryFilterBuilder interface method implementation 17 18 /// <summary> 19 /// Combine all active expressions into one expression. 20 /// </summary> 21 /// <exception cref="InvalidOperationException">The builder contains no filters.</exception> 22 /// <returns>A LINQ predicate expression.</returns> 23 public Expression<Func<TEntity, bool>> Build() 24 { 25 if (_queryFilters.Count == 0) 26 throw new InvalidOperationException("No expressions provided."); 27 28 var activeQueryFilters = _queryFilters 29 .Where(q => q.Value.Active) 30 .Select(q => q.Value.Expression) 31 .ToList(); 32 33 if (activeQueryFilters.Count == 0) 34 return q => true; 35 36 if (activeQueryFilters.Count == 1) 37 return activeQueryFilters.Single(); 38 39 var exp = activeQueryFilters.First(); 40 41 foreach (var e in activeQueryFilters.Skip(1)) 42 { 43 var leftParam = exp.Parameters[0]; 44 var rightParam = e.Parameters[0]; 45 46 var visitor = new ReplaceParameterVisitor(rightParam, leftParam); 47 48 var leftBody = exp.Body; 49 var rightBody = visitor.Visit(e.Body); 50 51 exp = Expression.Lambda<Func<TEntity, bool>>(Expression.AndAlso(leftBody, rightBody), leftParam); 52 } 53 54 return exp; 55 } 56 57 /// <summary> 58 /// Adding a filter to a given query filter builder. 59 /// </summary> 60 /// <param name="expression">A LINQ predicate expression.</param> 61 /// <param name="active">Indication of whether the filter should be applied, this parameter can be controlled by a service injected into DbContext.</param> 62 /// <returns>A QueryFilterBuilder instance to chain methods.</returns> 63 public IQueryFilterBuilder<TEntity> AddFilter(Expression<Func<TEntity, bool>> expression, bool active = true) 64 { 65 string key = Guid.NewGuid().ToString(); 66 67 _queryFilters.Add(key, QueryFilter.Create(key, active, expression)); 68 return this; 69 } 70 71 /// <summary> 72 /// Adding a filter to a given query filter builder. 73 /// </summary> 74 /// <param name="filterName">The unique name of the filter.</param> 75 /// <param name="expression">A LINQ predicate expression.</param> 76 /// <param name="active">Indication of whether the filter should be applied, this parameter can be controlled by a service injected into DbContext.</param> 77 /// <exception cref="ArgumentException">Filter with given name already exists.</exception> 78 /// <exception cref="ArgumentNullException">Filter name is null.</exception> 79 /// <returns>A QueryFilterBuilder instance to chain methods.</returns> 80 public IQueryFilterBuilder<TEntity> AddFilter(string filterName, Expression<Func<TEntity, bool>> expression, bool active = true) 81 { 82 _queryFilters.Add(filterName, QueryFilter.Create(filterName, active, expression)); 83 return this; 84 } 85 86 /// <summary> 87 /// Searching for a filter based on the given name and disabling it. 88 /// </summary> 89 /// <param name="filterName">The unique name of the filter.</param> 90 /// <exception cref="KeyNotFoundException">Filter with given name does not exist.</exception> 91 /// <exception cref="ArgumentNullException">Filter name is null.</exception> 92 /// <returns>A QueryFilterBuilder instance to chain methods.</returns> 93 public IQueryFilterBuilder<TEntity> DisableFilter(string filterName) 94 { 95 var queryFilter = _queryFilters[filterName]; 96 97 _queryFilters[filterName] = QueryFilter.Create(queryFilter.Name, false, queryFilter.Expression); 98 return this; 99 } 100 101 /// <summary> 102 /// Searching for a filter based on the given name and enabling it. 103 /// </summary> 104 /// <param name="filterName">The unique name of the filter.</param> 105 /// <exception cref="KeyNotFoundException">Filter with given name does not exist.</exception> 106 /// <exception cref="ArgumentNullException">Filter name is null.</exception> 107 /// <returnsA QueryFilterBuilder instance to chain methods.></returns> 108 public IQueryFilterBuilder<TEntity> EnableFilter(string filterName) 109 { 110 var queryFilter = _queryFilters[filterName]; 111 112 _queryFilters[filterName] = QueryFilter.Create(queryFilter.Name, true, queryFilter.Expression); 113 return this; 114 } 115 116 #endregion 117 118 #region QueryFilterBuilder factory 119 120 /// <summary> 121 /// QueryFilterBuilder factory. 122 /// </summary> 123 /// <returns>A new QueryFilterBuilder instance.</returns> 124 public static IQueryFilterBuilder<TEntity> Create() 125 { 126 return new QueryFilterBuilder<TEntity>(); 127 } 128 129 #endregion 130 131 #region Hide System.Object inherited methods 132 133 [EditorBrowsable(EditorBrowsableState.Never)] 134 public override bool Equals(object obj) => base.Equals(obj); 135 136 [EditorBrowsable(EditorBrowsableState.Never)] 137 public override int GetHashCode() => base.GetHashCode(); 138 139 [EditorBrowsable(EditorBrowsableState.Never)] 140 public override string ToString() => base.ToString(); 141 142 #endregion 143 144 #region QueryFilterBuilder private helper classes 145 146 private class ReplaceParameterVisitor : ExpressionVisitor 147 { 148 private readonly ParameterExpression _oldParameter; 149 private readonly ParameterExpression _newParameter; 150 151 public ReplaceParameterVisitor(ParameterExpression oldParameter, ParameterExpression newParameter) 152 { 153 _oldParameter = oldParameter; 154 _newParameter = newParameter; 155 } 156 157 protected override Expression VisitParameter(ParameterExpression node) 158 { 159 return ReferenceEquals(node, _oldParameter) ? _newParameter : base.VisitParameter(node); 160 } 161 } 162 163 private class QueryFilter 164 { 165 public string Name { get; private set; } 166 public bool Active { get; private set; } 167 public Expression<Func<TEntity, bool>> Expression { get; private set; } 168 169 private QueryFilter() { } 170 171 public static QueryFilter Create(string name, bool active, Expression<Func<TEntity, bool>> expression) 172 { 173 return new QueryFilter 174 { 175 Name = name, 176 Active = active, 177 Expression = expression 178 }; 179 } 180 } 181 182 #endregion 183 } 184 }
c、在DbContext类方法 OnCreating中这样用。每个表都要写一次。比如User表、Category表。
1 modelBuilder.Entity<User>() 2 .HasQueryFilter(QueryFilterBuilder<User> 3 .Create() 4 .AddFilter(d => d.IsDeleted == false) 5 .AddFilter(d => d.TenantId.ToString() == GetTenantId().ToString()) 6 .Build());
GetTenantId()方法 自己写的
2、统一组合，只用配置一次 查询筛选器，所有表都使用此规则
来源：https://haacked.com/archive/2019/07/29/query-filter-by-interface/
https://gist.github.com/haacked/febe9e88354fb2f4a4eb11ba88d64c24
用法：
a、新建类ModelBuilderExtensions.cs
1 /* 2 Copyright Phil Haack 3 Licensed under the MIT license - https://github.com/haacked/CodeHaacks/blob/main/LICENSE. 4 */ 5 using System; 6 using System.Linq; 7 using System.Linq.Expressions; 8 using System.Reflection; 9 using Microsoft.EntityFrameworkCore; 10 using Microsoft.EntityFrameworkCore.Metadata.Builders; 11 using Microsoft.EntityFrameworkCore.Metadata.Internal; 12 using Microsoft.EntityFrameworkCore.Query; 13 14 public static class ModelBuilderExtensions 15 { 16 static readonly MethodInfo SetQueryFilterMethod = typeof(ModelBuilderExtensions) 17 .GetMethods(BindingFlags.NonPublic | BindingFlags.Static) 18 .Single(t => t.IsGenericMethod && t.Name == nameof(SetQueryFilter)); 19 20 public static void SetQueryFilterOnAllEntities<TEntityInterface>( 21 this ModelBuilder builder, 22 Expression<Func<TEntityInterface, bool>> filterExpression) 23 { 24 foreach (var type in builder.Model.GetEntityTypes() 25 .Where(t => t.BaseType == null) 26 .Select(t => t.ClrType) 27 .Where(t => typeof(TEntityInterface).IsAssignableFrom(t))) 28 { 29 builder.SetEntityQueryFilter( 30 type, 31 filterExpression); 32 } 33 } 34 35 static void SetEntityQueryFilter<TEntityInterface>( 36 this ModelBuilder builder, 37 Type entityType, 38 Expression<Func<TEntityInterface, bool>> filterExpression) 39 { 40 SetQueryFilterMethod 41 .MakeGenericMethod(entityType, typeof(TEntityInterface)) 42 .Invoke(null, new object[] { builder, filterExpression }); 43 } 44 45 static void SetQueryFilter<TEntity, TEntityInterface>( 46 this ModelBuilder builder, 47 Expression<Func<TEntityInterface, bool>> filterExpression) 48 where TEntityInterface : class 49 where TEntity : class, TEntityInterface 50 { 51 var concreteExpression = filterExpression 52 .Convert<TEntityInterface, TEntity>(); 53 builder.Entity<TEntity>() 54 .AppendQueryFilter(concreteExpression); 55 } 56 57 // CREDIT: This comment by magiak on GitHub https://github.com/dotnet/efcore/issues/10275#issuecomment-785916356 58 static void AppendQueryFilter<T>(this EntityTypeBuilder entityTypeBuilder, Expression<Func<T, bool>> expression) 59 where T : class 60 { 61 var parameterType = Expression.Parameter(entityTypeBuilder.Metadata.ClrType); 62 63 var expressionFilter = ReplacingExpressionVisitor.Replace( 64 expression.Parameters.Single(), parameterType, expression.Body); 65 66 if (entityTypeBuilder.Metadata.GetQueryFilter() != null) 67 { 68 var currentQueryFilter = entityTypeBuilder.Metadata.GetQueryFilter(); 69 var currentExpressionFilter = ReplacingExpressionVisitor.Replace( 70 currentQueryFilter.Parameters.Single(), parameterType, currentQueryFilter.Body); 71 expressionFilter = Expression.AndAlso(currentExpressionFilter, expressionFilter); 72 } 73 74 var lambdaExpression = Expression.Lambda(expressionFilter, parameterType); 75 entityTypeBuilder.HasQueryFilter(lambdaExpression); 76 } 77 } 78 79 public static class ExpressionExtensions 80 { 81 // This magic is courtesy of this StackOverflow post. 82 // https://stackoverflow.com/questions/38316519/replace-parameter-type-in-lambda-expression 83 // I made some tweaks to adapt it to our needs - @haacked 84 public static Expression<Func<TTarget, bool>> Convert<TSource, TTarget>( 85 this Expression<Func<TSource, bool>> root) 86 { 87 var visitor = new ParameterTypeVisitor<TSource, TTarget>(); 88 return (Expression<Func<TTarget, bool>>)visitor.Visit(root); 89 } 90 91 class ParameterTypeVisitor<TSource, TTarget> : ExpressionVisitor 92 { 93 private ReadOnlyCollection<ParameterExpression> _parameters; 94 95 protected override Expression VisitParameter(ParameterExpression node) 96 { 97 return _parameters?.FirstOrDefault(p => p.Name == node.Name) 98 ?? (node.Type == typeof(TSource) ? Expression.Parameter(typeof(TTarget), node.Name) : node); 99 } 100 101 protected override Expression VisitLambda<T>(Expression<T> node) 102 { 103 _parameters = VisitAndConvert(node.Parameters, "VisitLambda"); 104 return Expression.Lambda(Visit(node.Body), _parameters); 105 } 106 } 107 }
b、在DbContext类方法 OnCreating中这样用
modelBuilder.SetQueryFilterOnAllEntities<Entity>(p => !p.IsDeleted);
modelBuilder.SetQueryFilterOnAllEntities<Entity>(p => p.TenantId.ToString() == GetTenantId().ToString());
Entity类中，有字段 IsDeleted和TenantId，其他实体类都继承Entity类
综上 两种方式，都可以实现过滤软删除和租户数据。
这是原帖地址
