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

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


I already have a lot of queries that utiliseDbSet<dbo_Deal> You now need to screen off unwanted users from secret agreements. I want to know whether there is a method to bypass theDbSet<dbo_Deal> such that instead of following its usual actions, it makes selections utilising a Table Valued argument.

The following TVF I developed filters out private transactions if the user does not have access:

CREATE FUNCTION [dbo].[GetDeals](@UserKey int)
    SELECT d.*
    FROM dbo.Deal d
    WHERE d.Confidentiality = 0
                FROM dbo.UserRole ur
                WHERE ur.UserKey = @UserKey
                AND ur.Role = 'Admin')

In order to call the SQL Function, I have additionally added the following to my DbContext:

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

It would be nice if I could somehow tell Entity Framework to utilise this procedure everytime it picks from or joins to Deals. I know I can modify all my queries to just use this function, but that would be great too. That seems feasible.

12/12/2017 3:11:57 PM

Accepted Answer

12/29/2017 4:29:24 PM

Popular Answer

Attempt to zzz-five-zzz yourDbSet :

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


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