Entity Framework Core Layer Security Using SESSION_CONTEXT

Question

I am writing a multi-tenant application using Row Level Security using ASP.NET Core and Entity Framework 7 (Core). Since my database is hosted on Microsoft SQL Server, I used the this method to force RLS.

Now I need to set the desired tenant_id to SESSION_CONTEXT.

The first problem I ran into was to start the stored procedure using EF7. The solution is similar:

var resp = context.Set<SessionVars>().FromSql(
          "EXECUTE sp_set_session_context @key = N'my_tenant', @value = {0};
           SELECT * FROM mySessionVars", desiredTenant).ToList();

Using the command above, I can clearly see that SESSION_CONTEXT has been successfully set. Now I expect that the following requests in the same context will be filtered out according to the tenant that I set to SESSION_CONTEXT.

int visibleRows = context.MyModel.ToList().Count;

Unfortunately, the results are not as expected. It behaves the same as the rows were extracted before setting SESSION_CONTEXT.

Is it caused by Eager Download EF7? IS EF7 using cached data? How can I overcome this?

I expect that I can set whatever value I want for SESSION_CONTEXT, and this will be held in context until the change or until the connection is closed.

Answer 1

, ,

EF6 , , , , context.Database.Connection.Open(), , .

, EF.

context.Database.Connection.Open();