Configure Entity Framework to call a SQL Server table function instead of a table on a specific entity

Question

I have a table of entities (let's say documents), where each document may have a different entity connected to it (let's say permission). A permission has a foreign key to the document table.

Example:

Schema:

Document -> Id | Data
Permission -> Id | EntityId | PermissionData

Content:

Document -> 1 | "This is my first doc"
Permission -> 12 | 1 | "This is doc 1's permission set"

If Permission was a table, I wouldn't have any problem - I would have simply use the Include method in my query and receive the connected permission:

ctx.Include(d => d.Permission)...

However, Permission is actually a complex scheme that includes multiple tables and is computed using a SQL Server table-valued function.

I'm trying to create a Permission entity, like every regular table entity, and simply configure Entity Framework to execute a database function call instead of a table join.

If permission was a table and I would have included it in my query, I would expect the SQL execution to look something like this:

select * 
from document d 
join permission p on d.Id = p.EntityId

Instead of that, I would like to achieve something like this:

select * 
from document d 
join fn_getPermissions(p1,p2,p3...) p on d.Id = p.EntityId

Let's assume that the params p1...pn are hard coded, but I need to default them on the C# end and not in SQL Server.

I saw an option to configure an entity to use stored procedures using Entity Framework, but I didn't see any place I can use a stored procedure for querying, rather for insert, delete etc.

I know how to call DBFunctions (using Conventions) - I'm not looking for explicit function calls. I would like to treat the Permission entity as a table entity, mainly since I'm using OData over Entity Framework and I don't want to create specific methods just for this case.

Is there any way to accomplish this kind of behavior? I'm using EF 6.x (not core).

you can create a view that splits the permissions into rows and then you can query it as you wish — Monah, Feb 14 '18 at 07:20
@Monah - I thought about doing that, but I have multiple entities like document and I prefer configuring entity framework somehow. — Amir Popovich, Feb 14 '18 at 07:29
could you please put more details about the permission data and how these entities differs? IMO, the view approach is the best for your case here — Monah, Feb 14 '18 at 07:30
@Monah - There are multiple tables that define the permission scheme. There are multiple entities like document that share that permission scheme logically. There is a function that does lots of calculations and returns the answer based on input params that I can access globally, per request, in my C# code, and therefore, I said that I can use hard coded params for now. — Amir Popovich, Feb 14 '18 at 07:46
I doubt that is easily possible - Entity Framework is not known for its great extensibility. — Evk, Feb 14 '18 at 08:49

score 0 · Answer 1 · answered Feb 14 '18 at 07:21

Depending on your comlex tables structure, this might either work or not. You can basically construct an entity from Sql, but for this you should have a table in DB where actual permissions reside, i.e. one table that has all the data you want (you may use other tables to interpret it, but the data you want is let's say in permission table only). Than you could use sth like this:

   this.Context.Permissions
.FromSql(@"select permName, permDesc from document d join fn_getPermissions(@p1,@p2,@p3...) p on d.Id = @entityId"
, new SqlParameter("p1", val)
, new SqlParameter("p2", val)
, new SqlParameter("p3", val)
, new SqlParameter("entityId", val))
.Select(c => new { c.permName, c.permDesc });

As you see, you can select a dynamic object, or cast it if you want to a type, but you should go through one table of the context, in this case "Permissions".

Thanks for your answer. This is not what I'm looking for. I know how to invoke db functions and to do things explicitly. Since I'm using OData over EF, I'm looking for a neater generic solution. All I'm looking for is to somehow tell entity framework to call a db function instead of a table when it translates the expression to sql. — Amir Popovich, Feb 14 '18 at 07:31

score 0 · Answer 2 · answered Feb 15 '18 at 09:43

I solved this temporary with a workaround.

I'm not going to accept my answer since I don't believe it's the best practice and maybe someone else will come up with a better solution. This workaround works for me since I only want to read the Permissions. If you choose to implement this solution, be careful and check the sql queries it generates to be sure it suitable for you.

So, In order to achieve what I need I've mapped the entity to a dummy table.

modelBuilder.Entity<Permission>().ToTable("Dummy_Permission_Table");

Then, I've created a custom IDbCommandInterceptor and overridden the ReaderExecuting method since it's the only one that's relevant in my case.

public class PermissionDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
       // get the params and create the function call
       command.CommandText = command.CommandText.Replace("[Dummy_Permission_Table]","fn_getPermissions(p1,p2,p3..)");
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }
}

and finally, I've registered the interceptor:

DbInterception.Add(new PermissionDbCommandInterceptor());

Configure Entity Framework to call a SQL Server table function instead of a table on a specific entity

2 Answers2