Best Practice for using Open Access in for a Multi-Tenant System

[Mike]

I have been evaluating Open Access for a MS SQL Server based multi-tenant project and have run into a problem described below.

My question is what is the recommended approach for using Open Access in a multi-tenant environment?

The details …

Our preference is for 1) tenants to store data in shared tables with a tenantId column to identify which rows belong to which tenants 2) To use triggers to set the TenantId as rows are inserted 3) to use views to make sure tenants only see their data

I have experimented with a combination of using the connection string and sql connection context to pass the tenant id and user name into SQL Server to use in the triggers and views. My preference would be to use SQL context to pass data in – to avoid passing everything on the SQL connection string and so end up having many SQL connections.

I have extended my entity model as shown below to set the SQL context by calling a stored proc call SetContextInfo in an overridden OnDatabaseOpen method

    public partial class db

    {

        public string tenantId;

        public string tenantUserId;

        public db(string connection, string TenantId, string TenantUserId)

            : base(connection, backend, metadataSource)

        {

            this.tenantId = TenantId;

            this.tenantUserId = TenantUserId;

        }

        protected override void OnDatabaseOpen(Telerik.OpenAccess.BackendConfiguration backendConfiguration, Telerik.OpenAccess.Metadata.MetadataContainer metadataContainer)

        {

            string contextCmdText = String.Format(@"[Core].[SetContextInfo] '{0}', '{1}'", tenantId, tenantUserId);

            backendConfiguration.ConnectionPool.InitSQL.Add(contextCmdText);

            base.OnDatabaseOpen(backendConfiguration, metadataContainer);

        }

    }

I have a test rig repeatedly calling the test code below passing in different tenant Ids each time

            string connectionString = "TestConnection";            

            using (db dbContext = new db(connectionString, TenantId, TenantUserName))

            {

                var person = new Person();

                person.FirstName = FirstName;

                person.LastName = LastName;

                dbContext.Add(person);

                dbContext.SaveChanges();              

            }               
    

I am finding that the entity model constructor gets called correctly every time my overridden OnDatabaseOpen method to set the connect context does not get called every time -  hence my inserted row is not allocated to the correct tenant.

So is there a better approach than this one? 

Sorry for the long post – thanks for your help

Mike 

[PetarP]

Hello Mike,

 The problem here is that we are caching the database instance based on your connection string. This said the OpenDatabase is called only the first time for a given connection string. Each subsequent call results in a database being returned from our cache. 
The quickest way to solve that is to always use a connection string that is different than the ones used before (you can just append a ; at the end of it).
Be aware that this might cause some performance penalties to your project as you will have a database object stored inside of your application for each of your tenants.

Please use this as a temporary workaround as we are planning to change how our cache behaves with our next service pack.

Kind regards,
Petar
the Telerik team