none
Default Clasure Where in Includes Generyc Repository Entity Framework 6.1 Code First c# RRS feed

  • Question

  • I have a generic repository where all the database tables have a column to delete logical. For example:

    View my simple database diagram

    I need all the queries held by the repository from the Sale table is added to condition negating delete columns, including when the tables (SaleItem, Item, Customer) were included.

    [Table("Sale")]
    public partial class Sale
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Sale()
        {        
            SaleItems = new HashSet<SaleItem>();            
        }
    
        public long ID { get; set; }
        public long? CostumerID { get; set; }
        public virtual Costumer Costumer { get; set; }
        public bool Deleted { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<SaleItem> SaleItems { get; set; }
    
    public class SaleRepository : BaseRepository<Sale>
    {
        public async Task<Sale> FindOneAndRelatedAsync(object id)
        {
            return await FindAsync(id,
                (s => s.Costumer),
                (s => s.SaleItems),
                (s => s.SaleItems.Select(si => si.Item))
            );
     }
    
    public class BaseRepository<T> : IBaseRepository<T> where T : class
    {
        private string deletedName = "Deleted";
        public async virtual Task<T> FindAsync(object id, params Expression<Func<T, object>>[] includes)
        {
            return await FindAsync(id, false, includes);
        }
    
        public async virtual Task<T> FindAsync(object id, bool hasDeleted = false, params Expression<Func<T, object>>[] includes)
        {
            var query = DbSet.AsQueryable();
    
            query = includeRelationships(includes, query);
    
            query = query.Where(idPredicateExpression(id));
    
            if (!hasDeleted)
                query = query.Where(deletedPredicateExpression());
    
            return await query.FirstOrDefaultAsync();
        }
    
        protected Expression<Func<T, bool>> deletedPredicateExpression()
        {
            return columnPredicateExpression(deletedName, false);
        }
    
        protected Expression<Func<T, bool>> columnPredicateExpression(string columnName, object keyword)
        {
            var arg = Expression.Parameter(typeof(T), "p");
    
            var method = keyword.GetType() == typeof(string) ? typeof(string).GetMethod("Contains", new[] { typeof(string) }) : keyword.GetType().GetMethod("Equals", new[] { keyword.GetType() });
    
            var body = Expression.Call(
                Expression.Property(arg, columnName),
                method,
                Expression.Constant(keyword));
    
            return Expression.Lambda<Func<T, bool>>(body, arg);
        }
    
        protected IQueryable<T> includeRelationships(Expression<Func<T, object>>[] includes, IQueryable<T> query)
        {
            if (includes != null)
            {
                query = includes.Aggregate(query, (current, include) => current.Include(include));
            }
    
            return query;
        }

    As can be seen in the above code, I can create an Expression to add the condition WHERE somene the Sale entity with the method deletedPredicateExpression(). It works perfectly. But the entities that are included by includeRelationships() method (SaleItem, Item and Costumer) must include the condition WHERE.

    When the relationship is 1:n, as the Costumer entity. I can perform insert the code below.

    protected IQueryable<T> includeRelationships(Expression<Func<T, object>>[] includes, IQueryable<T> query)
        {
            if (includes != null)
            {
                int i = 0;
                query = includes.Aggregate(query, (current, include) => current.Include(include));
                foreach (var include in includes)
                {
                        var test = test(include.Body.Type);
                        query = query.Where(test).AsQueryable();
                }
    
            }
    
            return query;
        }
    
        protected Expression<Func<T, bool>> test(Type typeEntity)
        {
            string columnName = typeEntity.Name +"." + deletedName;
    
                string[] props = columnName.Split('.');
                Type type = typeof(T);
                ParameterExpression arg = Expression.Parameter(type, "m");
                Expression expr = arg;
                foreach (string prop in props)
                {
                    // use reflection (not ComponentModel) to mirror LINQ
                    PropertyInfo pi = type.GetProperty(prop);
                    expr = Expression.Property(expr, pi);
                    type = pi.PropertyType;
                }
                var val1 = Expression.Constant(false);
                Expression e1 = Expression.Equal(expr, val1);
                Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
                return (Expression<Func<T, bool>>)Expression.Lambda(delegateType, e1, arg);  
        }

    If my relationship is N:N or when I need to access an object inside a collection as in entity Item that is below SaleItems can not include the clause WHERE because it does not have the property Deleted of the object SaleItems or Item for example.

    Exist a way to filter all the entities to always generate a query considering a condition in the case WHERE Deleted == 0 by default?



    Tuesday, February 2, 2016 9:04 PM

Answers

  • Hi Andre,

    I would suggest that you could create a base entity class, and then all the entities class inherit the base entity class, and then you could filter them by property named deleted anywhere.

    public abstract class BaseEntity
    {
        public BaseEntity()
        {
            IsDelete = false;
        }
       public bool IsDelete { get; set; }
    }
    
    public class entities : BaseEntity (such as item,customers)
    {
    //some properties
    }
    

    And you could modify your BaseRepository class as below

    public class BaseRepository<T> : IBaseRepository<T> where T : BaseEntity
    {
       public T GetEntityById(int id)
        {
            return this.Entities.where(item => item.id == id && item.IsDelete == false).FirstOrDefault();            
    }
    }
    

    Best regards,

    Cole Wu

    Thursday, February 4, 2016 8:29 AM
    Moderator