Can I make my Entity Framework DbSet call my table valued function when selecting?

ef-code-first entity-framework entity-framework-6

Question

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?

1
0
12/12/2017 3:11:57 PM

Accepted Answer

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);
  }
}
0
12/29/2017 4:29:24 PM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow