选择时是否可以使Entity Framework DbSet调用表值函数? [英] Can I make my Entity Framework DbSet call my table valued function when selecting?

问题描述

我有许多使用DbSet<dbo_Deal>的现有查询，现在需要过滤掉未经授权的用户的机密交易.我想知道是否有一种方法可以覆盖DbSet<dbo_Deal>，以便它使用表值参数而不是其默认行为进行选择.

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.

我创建了以下TVF，用于在用户无权访问时过滤掉机密交易:

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')
);

我还在我的DbContext中添加了以下内容以调用SQL函数:

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

我知道我可以重构所有查询以仅调用此函数，但是如果我能以某种方式指示Entity Framework选择或加入Deals来使用此函数，那将是很好的.有可能吗?

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?

推荐答案

我无法使用SQL函数以我想要的方式获得解决方案，所以我改用了

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.

这对我来说是一个很好的解决方案，因为我避免了必须重构所有现有的Linq查询，以后任何查询都将自动获得此行为.

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

这篇关于选择时是否可以使Entity Framework DbSet调用表值函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！