preface
In project development, we often design a series of fields such as soft deletion, user, etc. to facilitate various filtering during business query
Then the extended question is:
How do we add these conditions to business queries? Or disable some query criteria dynamically?
EF Core has its own global filtering and query function
EF Core provides a HasQueryFilter for us to preset some filter conditions during query
For example:
builder.HasQueryFilter(x => !x.IsDelete);
In this way, EF Core will automatically help us implement filtering when querying
Then, if you don't want to use it, you can ignore it all
DbSet.IgnoreQueryFilters();
At first glance, it's perfect
Then when we actually operate
- Should I configure every Entity once?
- I just want to disable some filters?
- Should some parameters of my query criteria be dynamic?
For example, data related to users, etc
(some people may say that I try to inject the User information into the DbContext. If I want other information, I'll continue to inject it?)
This is the gap between theory and practice
Then I searched online for a long time and found entityframework plus (open source and free)
https://github.com/zzzprojects/EntityFramework-Plus
Official website address: http://entityframework-plus.net/
There are many built-in functions. This article only focuses on query filtering
Entityframework plus query filtering function
1.QueryFilterManager
QueryFilterManager is mainly used to preset global filtering
For example:
QueryFilterManager.Filter<Customer>(q => q.Where(x => x.IsActive)); var ctx = new EntitiesContext(); QueryFilterManager.InitilizeGlobalFilter(ctx);
This will...
However, it should be noted in advance that QueryFilterManager cannot be changed after default
It can't be changed. This is what the author saw when he just replied to others at Google
It conflicts with the third dynamic point before us
Then we can only look at other ways
2.Filter
Z.EntityFramework.Plus provides a way to inject filter conditions through the extension of DbContext
For example:
var ctx = new EntitiesContext(); ctx.Filter<IAnimal>(MyEnum.EnumValue, q => q.Where(x => x.IsDomestic)) //Disable specified key query criteria ctx.Filter(MyEnum.EnumValue).Disable(); var dogs = ctx.Dogs.ToList(); //Enable specified key query criteria ctx.Filter(MyEnum.EnumValue).Enable(); // SELECT * FROM Dog WHERE IsDomestic = true var dogs = ctx.Dogs.ToList();
It seems to meet our needs
3.AsNoFilter
Disable condition
For example:
var ctx = new EntitiesContext(); this.Filter<Customer>(q => q.Where(x => x.IsActive)); // SELECT * FROM Customer WHERE IsActive = true var list = ctx.Customers.ToList(); // SELECT * FROM Customer var list = ctx.Customers.AsNoFilter().ToList();
How to enable the specified query criteria after AsNoFilter(), the author does not seem to have extended it accordingly. The corresponding extension method will be given later
After saying so many theories, what about the actual operation?
- How can these conditions be injected?
- How can I expand at will?
- What if we operate through warehousing rather than directly through DbContext?
How to package
Here is an example of my own open source project:
github : https://github.com/wulaiwei/WorkData.Core
Mainly dependent framework
- AutoFac
- EF Core
- Z.EntityFramework.Plus
For us, no matter how many data filters we use, the return value should be the same. We look at DbContext.Filter(...) and find that its return value is BaseQueryFilter
For this, we can get two pieces of information. We need to pass in DbContext and a method with the return value of BaseQueryFilter
Therefore, we define the following interface iddynamicfilter
public interface IDynamicFilter { BaseQueryFilter InitFilter(DbContext dbContext); }
So we have a standard here
For example, we need a user and a soft deleted data filter. We just need to inherit it
How do we distinguish them?
Before using Z.EntityFramework.Plus, we saw the Key that can set the filter
So we also extend the attribute DynamicFilterAttribute as their name
public class DynamicFilterAttribute: Attribute { /// <summary> /// Name /// </summary> public string Name { get; set; } }
Then we define our users and soft deleted data filters and set names for them
CreateDynamicFilter
/// <summary> /// CreateDynamicFilter /// </summary> [DynamicFilter(Name = "CreateUserId")] public class CreateDynamicFilter : IDynamicFilter { /// <summary> /// InitFilter /// </summary> /// <param name="dbContext"></param> /// <returns></returns> public BaseQueryFilter InitFilter(DbContext dbContext) { var workdataSession = IocManager.Instance.Resolve<IWorkDataSession>(); if (workdataSession == null) return dbContext .Filter<ICreate>("CreateUserId", x => x.Where(w => w.CreateUserId == string.Empty )); return dbContext .Filter<ICreate>("CreateUserId", x => x.Where(w => w.CreateUserId == workdataSession.UserId || w.CreateUserId == "")); } }
explain:
var workdataSession = IocManager.Instance.Resolve<IWorkDataSession>();
Used to get the parameters you need
IocManager.Instance.Resolve is the encapsulated source code of WorkData. For information about Ioc, please refer to git or the previous blog
SoftDeleteDynamicFilter
/// <summary> /// SoftDeleteDynamicFilter /// </summary> [DynamicFilter(Name = "SoftDelete")] public class SoftDeleteDynamicFilter: IDynamicFilter { public BaseQueryFilter InitFilter(DbContext dbContext) { return dbContext .Filter<IsSoftDelete>("SoftDelete", x => x.Where(w => !w.IsDelete)); } }
In this way, all our interfaces and implementations are defined. How to manage them?
1. Inject the inherited iddynamicfilter into Ioc
#region dynamic audit injection var filterTypes = _typeFinder.FindClassesOfType<IDynamicFilter>(); foreach (var filterType in filterTypes) { var dynamicFilterAttribute = filterType.GetCustomAttribute(typeof(DynamicFilterAttribute)) as DynamicFilterAttribute; if (dynamicFilterAttribute == null) continue; builder.RegisterType(filterType).Named<IDynamicFilter>(dynamicFilterAttribute.Name); } #endregion
explain:
- ITypeFinder is a reflection method extracted from nopcommerce. It has been integrated into WorkData. Baidu can query the corresponding description documents
- Get the attribute name of DynamicFilterAttribute through GetCustomAttribute as the name registered to Ioc
2. How to set an enable data filter? We define a configuration file and inject the configuration file through the program provided by. net core
/// <summary> ///Dynamic interceptor configuration /// </summary> public class DynamicFilterConfig { public List<string> DynamicFilterList{ get; set; } }
How to inject the configuration file can be done through Baidu or view the workdata source code, which is not explained
3. How to manage it? When is it uniformly added to the DbContext?
We define a DynamicFilterManager here, provide a dictionary collection to temporarily store all iddynamicfilters, and provide a method to initialize values
public static class DynamicFilterManager { static DynamicFilterManager() { CacheGenericDynamicFilter = new Dictionary<string, IDynamicFilter>(); } /// <summary> /// CacheGenericDynamicFilter /// </summary> public static Dictionary<string, IDynamicFilter> CacheGenericDynamicFilter { get; set; } /// <summary> /// AddDynamicFilter /// </summary> /// <param name="dbContext"></param> /// <returns></returns> public static void AddDynamicFilter(this DbContext dbContext) { if (dbContext == null) return; foreach (var dynamicFilter in CacheGenericDynamicFilter) dynamicFilter.Value.InitFilter(dbContext); } /// <summary> /// AsWorkDataNoFilter /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="context"></param> /// <param name="filterStrings"></param> /// <returns></returns> public static IQueryable<T> AsWorkDataNoFilter<T>(this DbSet<T> query, DbContext context, params object[] filterStrings) where T : class { var asNoFilterQueryable = query.AsNoFilter(); object query1 = asNoFilterQueryable; var items = CacheGenericDynamicFilter.Where(x => filterStrings.Contains(x.Key)); query1 = items.Select(key => context.Filter(key.Key)).Where(item => item != null) .Aggregate(query1, (current, item) => (IQueryable) item.ApplyFilter<T>(current)); return (IQueryable<T>) query1; } /// <summary> /// SetCacheGenericDynamicFilter /// </summary> public static void SetCacheGenericDynamicFilter() { var dynamicFilterConfig = IocManager.Instance.ResolveServiceValue<DynamicFilterConfig>(); foreach (var item in dynamicFilterConfig.DynamicFilterList) { var dynamicFilter = IocManager.Instance.ResolveName<IDynamicFilter>(item); CacheGenericDynamicFilter.Add(item, dynamicFilter); } } }
Then we initialize OnModelCreating in DbContext
/// <summary> ///Override model creation function /// </summary> /// <param name="modelBuilder"></param> protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); //Initialize object DynamicFilterManager.SetCacheGenericDynamicFilter(); }
How to pay the condition to DbContext after initialization?
In the DynamicFilterManager, we provide an extension method AddDynamicFilter, which you can call when you create a DbContext
/// <summary> /// AddDynamicFilter /// </summary> /// <param name="dbContext"></param> /// <returns></returns> public static void AddDynamicFilter(this DbContext dbContext) { if (dbContext == null) return; foreach (var dynamicFilter in CacheGenericDynamicFilter) dynamicFilter.Value.InitFilter(dbContext); }
In WorkData, we need to call EfContextFactory
dbContext = _resolver.Resolve<TDbContext>(); //Initialize interceptor dbContext.AddDynamicFilter();
/// <summary> /// EfContextFactory /// </summary> public class EfContextFactory : IEfContextFactory { private readonly IResolver _resolver; public EfContextFactory(IResolver resolver) { _resolver = resolver; } /// <summary> /// default current context /// </summary> /// <param name="dic"></param> /// <param name="tranDic"></param> /// <returns></returns> public TDbContext GetCurrentDbContext<TDbContext>(Dictionary<string, DbContext> dic, Dictionary<DbContext, IDbContextTransaction> tranDic) where TDbContext : DbContext { return GetCurrentDbContext<TDbContext>(dic, tranDic, string.Empty); } /// <summary> ///GetCurrentDbContext /// </summary> /// <typeparam name="TDbContext"></typeparam> /// <param name="dic"></param> /// <param name="tranDic"></param> /// <param name="conString"></param> /// <returns></returns> public TDbContext GetCurrentDbContext<TDbContext>(Dictionary<string, DbContext> dic, Dictionary<DbContext, IDbContextTransaction> tranDic, string conString) where TDbContext : DbContext { conString = typeof(TDbContext).ToString(); var dbContext = dic.ContainsKey(conString + "DbContext") ? dic[conString + "DbContext"] : null; try { if (dbContext != null) { return (TDbContext)dbContext; } } catch (Exception) { dic.Remove(conString + "DbContext"); } dbContext = _resolver.Resolve<TDbContext>(); //Initialize interceptor dbContext.AddDynamicFilter(); //We are creating one and putting it into the data slot dic.Add(conString + "DbContext", dbContext); //Start transaction var tran = dbContext.Database.BeginTransaction(); tranDic.Add(dbContext, tran); return (TDbContext)dbContext; } }
In this way, all our filters have been injected
There's one left that we said before
How to enable the specified query criteria after AsNoFilter(), the author does not seem to have extended it accordingly. The corresponding extension method will be given later
After viewing the source code
/// <summary> /// Filter the query using context filters associated with specified keys. /// </summary> /// <typeparam name="T">The type of elements of the query.</typeparam> /// <param name="query">The query to filter using context filters associated with specified keys.</param> /// <param name="keys"> /// A variable-length parameters list containing keys associated to context filters to use to filter the /// query. /// </param> /// <returns>The query filtered using context filters associated with specified keys.</returns> public static IQueryable<T> Filter<T>(this DbSet<T> query, params object[] keys) where T : class { BaseQueryFilterQueryable filterQueryable = QueryFilterManager.GetFilterQueryable((IQueryable) query); IQueryable<T> query1 = filterQueryable != null ? (IQueryable<T>) filterQueryable.OriginalQuery : (IQueryable<T>) query; return QueryFilterManager.AddOrGetFilterContext(filterQueryable != null ? filterQueryable.Context : InternalExtensions.GetDbContext<T>(query)).ApplyFilter<T>(query1, keys); }
Z.EntityFramework.Plus provides an ApplyFilter, so we make an extension based on this
/// <summary> /// AsWorkDataNoFilter /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="context"></param> /// <param name="filterStrings"></param> /// <returns></returns> public static IQueryable<T> AsWorkDataNoFilter<T>(this DbSet<T> query, DbContext context, params object[] filterStrings) where T : class { var asNoFilterQueryable = query.AsNoFilter(); object query1 = asNoFilterQueryable; var items = CacheGenericDynamicFilter.Where(x => filterStrings.Contains(x.Key)); query1 = items.Select(key => context.Filter(key.Key)).Where(item => item != null) .Aggregate(query1, (current, item) => (IQueryable) item.ApplyFilter<T>(current)); return (IQueryable<T>) query1; }
In this way, we can pass in the specified filter name to enable what we want
Finally, our warehouse becomes like this:
/// <summary> /// EfBaseRepository /// </summary> /// <typeparam name="TEntity"></typeparam> /// <typeparam name="TPrimaryKey"></typeparam> /// <typeparam name="TDbContext"></typeparam> public class EfBaseRepository<TDbContext, TEntity, TPrimaryKey> : BaseRepository<TEntity, TPrimaryKey>, IRepositoryDbConntext where TEntity : class, IAggregateRoot, IEntity<TPrimaryKey> where TDbContext : DbContext { //public IQueryable<EntityType> EntityTypes => Context.Model.EntityTypes.Where(t => t.Something == true); private readonly IDbContextProvider<TDbContext> _dbContextProvider; private readonly IPredicateGroup<TEntity> _predicateGroup; public EfBaseRepository( IDbContextProvider<TDbContext> dbContextProvider, IPredicateGroup<TEntity> predicateGroup) { _dbContextProvider = dbContextProvider; _predicateGroup = predicateGroup; } /// <summary> /// Gets EF DbContext object. /// </summary> public TDbContext Context => _dbContextProvider.GetContent(); /// <summary> /// Gets DbSet for given entity. /// </summary> public virtual DbSet<TEntity> DbSet => Context.Set<TEntity>(); #region DbContext /// <summary> /// GetDbContext /// </summary> /// <returns></returns> public DbContext GetDbContext() { return Context; } #endregion #region Query /// <summary> /// FindBy /// </summary> /// <param name="primaryKey"></param> /// <returns></returns> public override TEntity FindBy(TPrimaryKey primaryKey) { var entity = DbSet.Find(primaryKey); return entity; } /// <summary> /// FindBy /// </summary> /// <param name="primaryKey"></param> /// <param name="includeNames"></param> /// <returns></returns> public override TEntity FindBy(TPrimaryKey primaryKey, string[] includeNames) { var query = DbSet; foreach (var includeName in includeNames) { query.Include(includeName); } var entity = query.Find(primaryKey); return entity; } /// <summary> /// AsNoFilterFindBy /// </summary> /// <param name="primaryKey"></param> /// <returns></returns> public override TEntity AsNoFilterFindBy(TPrimaryKey primaryKey) { var entity = DbSet.AsNoFilter() .SingleOrDefault(x => x.Id.Equals(primaryKey)); return entity; } /// <summary> /// AsNoFilterFindBy /// </summary> /// <param name="primaryKey"></param> /// <param name="includeNames"></param> /// <returns></returns> public override TEntity AsNoFilterFindBy(TPrimaryKey primaryKey, string[] includeNames) { var query = DbSet.AsNoFilter(); foreach (var includeName in includeNames) { query.Include(includeName); } var entity = query.SingleOrDefault(x => x.Id.Equals(primaryKey)); return entity; } /// <summary> /// FindBy /// </summary> /// <param name="primaryKey"></param> /// <param name="filterStrings"></param> /// <returns></returns> public override TEntity FindBy(TPrimaryKey primaryKey, params object[] filterStrings) { var entity = DbSet.AsWorkDataNoFilter(Context, filterStrings) .SingleOrDefault(x => x.Id.Equals(primaryKey)); return entity; } /// <summary> /// FindBy /// </summary> /// <param name="primaryKey"></param> /// <param name="includeNames"></param> /// <param name="filterStrings"></param> /// <returns></returns> public override TEntity FindBy(TPrimaryKey primaryKey, string[] includeNames, params object[] filterStrings) { var query = DbSet.AsWorkDataNoFilter(Context, filterStrings); foreach (var includeName in includeNames) { query.Include(includeName); } var entity = query.SingleOrDefault(x => x.Id.Equals(primaryKey)); return entity; } /// <summary> /// GetAll /// </summary> /// <returns></returns> public override IQueryable<TEntity> GetAll() { return DbSet; } /// <summary> /// GetAll /// </summary> /// <param name="includeNames"></param> /// <returns></returns> public override IQueryable<TEntity> GetAll(string[] includeNames) { var query = DbSet; foreach (var includeName in includeNames) { query.Include(includeName); } return query; } /// <summary> /// GetAll /// </summary> /// <param name="filterStrings"></param> /// <returns></returns> public override IQueryable<TEntity> GetAll(params object[] filterStrings) { return DbSet.AsWorkDataNoFilter(Context, filterStrings); } /// <summary> /// GetAll /// </summary> /// <param name="includeNames"></param> /// <param name="filterStrings"></param> /// <returns></returns> public override IQueryable<TEntity> GetAll(string[] includeNames, params object[] filterStrings) { var query = DbSet.AsWorkDataNoFilter(Context, filterStrings); foreach (var includeName in includeNames) { query.Include(includeName); } return query; } /// <summary> /// AsNoFilterGetAll /// </summary> /// <returns></returns> public override IQueryable<TEntity> AsNoFilterGetAll() { return DbSet.AsNoFilter(); } /// <summary> /// AsNoFilterGetAll /// </summary> /// <param name="includeNames"></param> /// <returns></returns> public override IQueryable<TEntity> AsNoFilterGetAll(string[] includeNames) { var query = DbSet.AsNoFilter(); foreach (var includeName in includeNames) { query.Include(includeName); } return query; } #endregion #region Insert /// <summary> /// Insert /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="model"></param> public override TEntity Insert(TEntity model) { return DbSet.Add(model).Entity; } /// <summary> /// InsertGetId /// </summary> /// <param name="model"></param> /// <returns></returns> public override TPrimaryKey InsertGetId(TEntity model) { model = Insert(model); Context.SaveChanges(); return model.Id; } /// <summary> /// Insert /// </summary> /// <param name="entities"></param> public override void Insert(IEnumerable<TEntity> entities) { if (entities == null) throw new ArgumentNullException(nameof(entities)); DbSet.AddRange(entities); Context.SaveChanges(); } #endregion #region Delete /// <summary> /// Delete /// </summary> /// <param name="entity"></param> public override void Delete(TEntity entity) { DbSet.Remove(entity); Context.SaveChanges(); } /// <summary> /// Delete /// </summary> /// <param name="entities"></param> public override void Delete(IEnumerable<TEntity> entities) { if (entities == null) throw new ArgumentNullException(nameof(entities)); DbSet.RemoveRange(entities); Context.SaveChanges(); } #endregion #region Update /// <summary> /// Update /// </summary> /// <param name="entity"></param> public override void Update(TEntity entity) { DbSet.Update(entity); Context.SaveChanges(); } /// <summary> /// Update /// </summary> /// <param name="entities"></param> public override void Update(IEnumerable<TEntity> entities) { if (entities == null) throw new ArgumentNullException(nameof(entities)); DbSet.UpdateRange(entities); Context.SaveChanges(); } #endregion }
Note: the design concept of warehouse is extracted from ABP
Finally, the test is attached
The enabled filters are "CreateUserId", "SoftDelete"
/// <summary> /// Index /// </summary> /// <returns></returns> public IActionResult Index() { _baseRepository.GetAll().ToList(); _baseRepository.GetAll("CreateUserId","xxx Filter assumed to not exist").ToList(); _baseRepository.AsNoFilterGetAll().ToList(); _baseRepository.FindBy("1"); _baseRepository.FindBy("1", "CreateUserId", "xxx Filter assumed to not exist"); _baseRepository.AsNoFilterFindBy("1"); return View(); }