I have many existing queries that use DbSet<dbo_Deal>
and now have the requirement to filter out confidential deals for unauthorized users. I would like to know if there's a way to override the DbSet<dbo_Deal>
so that it selects using a Table Valued parameter instead of its default behavior.
I created the following TVF that filters out confidential deals if the user does not have access:
CREATE FUNCTION [dbo].[GetDeals](@UserKey int)
RETURNS TABLE
RETURN (
SELECT d.*
FROM dbo.Deal d
WHERE d.Confidentiality = 0
OR EXISTS(SELECT *
FROM dbo.UserRole ur
WHERE ur.UserKey = @UserKey
AND ur.Role = 'Admin')
);
I have also added the following to my DbContext to call the SQL Function:
[DbFunction("MyDbContext", "GetDeals")]
[CodeFirstStoreFunctions.DbFunctionDetails(DatabaseSchema = "dbo")]
public IQueryable<dbo_Deal> GetDeals()
{
var userKeyParam = new System.Data.Entity.Core.Objects.ObjectParameter("UserKey", typeof(int)) { Value = _userKey };
return ((System.Data.Entity.Infrastructure.IObjectContextAdapter)this).ObjectContext.CreateQuery<dbo_Deal>("[MyDbContext].[GetDeals](@UserKey)", userKeyParam);
}
I know I can refactor all my queries to just call this function, but it would be great if I could somehow instruct Entity Framework to use this function whenever it selects or joins to Deals. Is that possible?
I wasn't able to get a solution working the way I wanted using a SQL Function, so instead I used this FilteredDbSet which wraps DbSet. All I had to do to implement it was to make the return type on my property in the DbContext a FilteredDbSet
and then instantiate it in the constructor with my desired filter. I also made the private constructor in the class below public so I could mock it for unit testing.
This ended up being a very good solution for me because I avoided having to refactor all the existing Linq queries and any future queries will automatically get this behavior.
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);
}
}
Try to Moq your DbSet
:
public class YourContext: DbContext
{
public YourContext()
{
var tvf = GetDeals();
var mockSet = new Mock<DbSet<dbo_Deal>>();
mockSet.As<IQueryable<dbo_Deal>>().Setup(m => m.Provider).Returns(tvf.Provider);
mockSet.As<IQueryable<dbo_Deal>>().Setup(m => m.Expression).Returns(tvf.Expression);
mockSet.As<IQueryable<dbo_Deal>>().Setup(m => m.ElementType).Returns(tvf.ElementType);
mockSet.As<IQueryable<dbo_Deal>>().Setup(m => m.GetEnumerator()).Returns(() => tvf.GetEnumerator());
//your DbSet:
dbo_Deals = mockSet.Object;
}
}