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

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

Question

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

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.

adam0101

Accepted Answer

ZZZ_tmp

adam0101

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

Slava Utesinov

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator

Related