Home » Ado.Net » Entity Framework » Using a FilteredDbSet with Entity Framework to have dynamic Filtering

Using a FilteredDbSet with Entity Framework to have dynamic Filtering

If you are using an application that require to have your user to own data and not let other to see or edit it, you need to specify to each of your entity a user id to whom the entity belong. All my entities that belong to a user inherit from IUserOwnable. This interface force the entity to have a UserId.

public interface IUserOwnable
{
    int UserId { get; set; }
}

From here, when I want to get the list of entity of my user I just need to specify in the where clause the logged user id and Entity Framework get me all entities that belong to this one. The following example show you how to get all workout from the logged user.

public override IQueryable<Workout> GetAll()
{
    return DatabaseContext.Set<Workout>().Where(e=>e.UserId == logguedUserId);
}

The problem is that you need to set the UserId every time. This can lead to a problem of security if a developer forget to add the condition. It also a pain to repeat this code everywhere. This is why a better way to do it is to have a FilteredDbSet which will automatically add this condition. Here is the FilteredDbSet class.

public class FilteredDbSet<TEntity> : IDbSet<TEntity>, IOrderedQueryable<TEntity>, IListSource where TEntity : class
{
	private readonly DbSet<TEntity> _set;
	private readonly Action<TEntity> _initializeEntity;
	private readonly Expression<Func<TEntity, bool>> _filter;

	public FilteredDbSet(DbContext context, Expression<Func<TEntity, bool>> filter, Action<TEntity> initializeEntity)
		: this(context.Set<TEntity>(), filter, initializeEntity)
	{
	}

	public IQueryable<TEntity> Include(string path)
	{
		return _set.Include(path).Where(_filter).AsQueryable();
	}

	private FilteredDbSet(DbSet<TEntity> set, Expression<Func<TEntity, bool>> filter, Action<TEntity> initializeEntity)
	{
		_set = set;
		_filter = filter;
		_initializeEntity = initializeEntity;
	}

	public IQueryable<TEntity> Unfiltered()
	{
		return _set;
	}

	public TEntity Add(TEntity entity)
	{
		DoInitializeEntity(entity);
		return _set.Add(entity);
	}
	public void AddOrUpdate(TEntity entity)
	{
		DoInitializeEntity(entity);
		_set.AddOrUpdate(entity);
	}
	public TEntity Attach(TEntity entity)
	{
		DoInitializeEntity(entity);
		return _set.Attach(entity);
	}

	public TDerivedEntity Create<TDerivedEntity>() where TDerivedEntity : class, TEntity
	{
		var entity = _set.Create<TDerivedEntity>();
		DoInitializeEntity(entity);
		return entity;
	}

	public TEntity Create()
	{
		var entity = _set.Create();
		DoInitializeEntity(entity);
		return entity;
	}

	public TEntity Find(params object[] keyValues)
	{
		var entity = _set.Find(keyValues);
		if (entity == null)
			return null;

		
		return entity;
	}

	public TEntity Remove(TEntity entity)
	{
		if (!_set.Local.Contains(entity))
		{
			_set.Attach(entity);
		}
		return _set.Remove(entity);
	}


	public ObservableCollection<TEntity> Local
	{
		get { return _set.Local; }
	}

	IEnumerator<TEntity> IEnumerable<TEntity>.GetEnumerator()
	{
		return _set.Where(_filter).GetEnumerator();
	}

	IEnumerator IEnumerable.GetEnumerator()
	{
		return _set.Where(_filter).GetEnumerator();
	}

	Type IQueryable.ElementType
	{
		get { return typeof(TEntity); }
	}

	Expression IQueryable.Expression
	{
		get
		{
			return _set.Where(_filter).Expression;
		}
	}

	IQueryProvider IQueryable.Provider
	{
		get
		{
			return _set.AsQueryable().Provider;
		}
	}

	bool IListSource.ContainsListCollection
	{
		get { return false; }
	}

	IList IListSource.GetList()
	{
		throw new InvalidOperationException();
	}

	void DoInitializeEntity(TEntity entity)
	{
		if (_initializeEntity != null)
			_initializeEntity(entity);
	}

	public DbSqlQuery<TEntity> SqlQuery(string sql, params object[] parameters)
	{
		return _set.SqlQuery(sql, parameters);
	}
}

From here, you just need to call the DbSet that is filtered instead of the default one.

public override IQueryable<Workout> GetAll()
{
    return DatabaseContext.SetOwnable<Workout>();
}

Of course, the DatabaseContext is your class that inherit from DbContext. The SetOwnable method will call the FilteredDbSet.

public IDbSet<TEntity> SetOwnable<TEntity>() where TEntity : class, IUserOwnable
{
    return new FilteredDbSet<TEntity>(this, entity => entity.UserId == CurrentUser.UserId, entity => entity.UserId = CurrentUser.UserId);
}

As you can see, we create a FilteredDbSet and assign the user Id with the CurrentUser which is the logged user.

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

2 Responses so far.

  1. […] am using FilteredSet to have automatically data from the current logged user of my system. It provides me a dynamic way […]

  2. ANdi says:

    Hi, very nice!!!
    Can you please update and show usage smaples on how to filter globally for tenantId, or COmpany Id, in EF 6, and ASP MVC 5 identity 2.2.1

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.