c# - 向所有查询 Entity Framework 添加过滤器

Question

我想将 CompanyID 过滤器添加到我的所有 Entity Framework 请求中。因为每个用户必须只看到他们的记录。我不想在业务层中添加过滤器 (x=>x.CompanyID == cID) 的所有方法。如何自动添加过滤器要求。

我在 DAL 中的 GetList 方法

     public List<TEntity> GetList(Expression<Func<TEntity, bool>> filter)
    {
        using (var context = new TContext())
        {

            return filter == null
                ? context.Set<TEntity>().ToList()
                : context.Set<TEntity>().Where(filter).ToList();
        }
    }

商业

   public List<FinanceData> GetAll()
        {
            return _financeDal.GetList(filter:x=>x.CompanyID==_cID);
        }

Answer 1

在 Entity Framework 核心 2.0 ，您可以使用 Global Query Filters .

仅向一个实体添加过滤器:

public interface IDelete
{
    bool IsDeleted { get; set; }
}

public class Blog : IDelete
{        
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post : IDelete
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public bool IsDeleted { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

// Default method inside the DbContext or your default Context
protected override void OnModelCreating(ModelBuilder modelBuilder)
{         
    base.OnModelCreating(builder);  

    modelBuilder.Entity<Blog>()
                // Add Global filter to the Blog entity
                .HasQueryFilter(p => p.IsDeleted == false);

    modelBuilder.Entity<Post>()
                // Add Global filter to the Post entity
                .HasQueryFilter(p => p.IsDeleted == false);
}

如果你有很多实体，第一种方法不好，使用下面的代码将全局过滤器应用于所有实体( Magic way ):

public static class ModelBuilderExtension
{
    public static void ApplyGlobalFilters<TInterface>(this ModelBuilder modelBuilder, Expression<Func<TInterface, bool>> expression)
    {
        var entities = modelBuilder.Model
            .GetEntityTypes()
            .Where(e => e.ClrType.GetInterface(typeof(TInterface).Name) != null)
            .Select(e => e.ClrType);
        foreach (var entity in entities)
        {
            var newParam = Expression.Parameter(entity);
            var newbody = ReplacingExpressionVisitor.Replace(expression.Parameters.Single(), newParam, expression.Body);    
            modelBuilder.Entity(entity).HasQueryFilter(Expression.Lambda(newbody, newParam));
        }
    }
}

// Default method inside the DbContext or your default Context
protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    modelBuilder.Entity<Blog>();
    modelBuilder.Entity<Post>();

    builder.ApplyGlobalFilters<IDelete>(e => e.IsDeleted == false);
}

查询将是:

    exec sp_executesql N'SELECT [x].[BlogId], [x].[Name], [x].[Url]
    FROM [dbo].[Blog] AS [x]
    WHERE [x].[IsDeleted] = 0'