Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Add filter to all query entity framework

I want add CompanyID filter to my all entity framework request.Because each user must see just their records.I dont want add filter (x=>x.CompanyID == cID) all methods in business layer.How can i add automaticly filter to requests.

My GetList method in DAL

     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();
        }
    }

Business

   public List<FinanceData> GetAll()
        {
            return _financeDal.GetList(filter:x=>x.CompanyID==_cID);
        }
like image 791
user1924375 Avatar asked Mar 25 '15 16:03

user1924375


People also ask

How do I filter data in Entity Framework?

To filter data, use linq. You can not use Filter property of BindingSource when the underlying list is BindingList<T> ; Only underlying lists that implement the IBindingListView interface support filtering. To remove filter, just set the data source of your binding source to the local storage of your entities again.

What is query filter?

Filters you apply to the query definition are called query filters. You use query filters to reduce the amount of data retrieved from the data source. Query filters decrease the time it takes to run the report and ensure that only the data relevant to the report users is saved with the document.

How can you disable the filters in the query?

Filters may be disabled for individual LINQ queries by using the IgnoreQueryFilters operator.


1 Answers

In the Entity Framework Core 2.0, you can use Global Query Filters.

  1. Add filter just to the one entity:

    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);
    }
    
  2. If you have many entities, the first way isn't good, Use below code for applying the global filter to all entities(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);
    }
    

And Queries will be:

    exec sp_executesql N'SELECT [x].[BlogId], [x].[Name], [x].[Url]
    FROM [dbo].[Blog] AS [x]
    WHERE [x].[IsDeleted] = 0'
like image 150
Sina Lotfi Avatar answered Oct 20 '22 22:10

Sina Lotfi