set table schema at runtime

[Mac]

I have an oracle db running our ERP system which uses the concept of shared tables (not my choice!)

I can only login with a user 'query' to a query schema. This user is given read-only permissions to other schemas and is the only means of accessing data in these other schemas.

I access various sprocs/functions etc in my query schema as it's the only schema which I can modify, but it has no tables or data. They exist in the other schemas.

Every schema apart from Query has identical sets of tables.

schemaA.Table1
schemaB.Table1
query.MySproc

A "shared table" means that the data is only in schemaA even though empty tables exist in the other schemas. I can only access the data by qualifying shared table with the schema name.

It means I often end generating queries like

Code: Select all

from table1 in context.GetData<Table1>()
join table2 in context.GetData<Table2>() on table1.col1 equals table2.col1
join table3 in context.GetData<Table3>() on table2.col2 equals table3.col2

which geneates pl/sql

Code: Select all

select (..fields)
from schemaA.table1 t1
inner join schemaB.table2 t2 on t1.col1 = t2.col1
inner join schemaA.table3 t3 on t2.col2 = t3.col2

I can never know the specific schema I am targeting for non shared data until runtime as it is based on information associated with a ERP user.

How can I set the schema per table at runtime based on some business logic per user?

Previously I modified the T4 templates to alter the

Code: Select all

[Table(Name = "SharedPrefix.TableA")] 

which would work if I could set the default schema and not prefix the TableAttributes.

I now have to use three different schemas query, sharedTableSchema, nonsharedTableSchema all from the same dataContext.

Code: Select all

TableAttribute 

is sealed so cannot override, so is

Code: Select all

AttributeMappingSource

. Haven't been able to inherit from

Code: Select all

MappingSource 

to override the metamodel, type resolution.

Currently I'm running a static method to prefix TableAttribute names in the T4 templates and I've overloaded the datacontext constructors to accept the schema name and am running a sql command

Code: Select all

ALTER SESSION SET CURRENT_SCHEMA=

when connecting.

Looking at ways to use IoC interception also to try and inject the schema or change the mapping at runtime but its all a bit hacky and would be so much easier to have the means to map each table using runtime instead of compile time logic.

Maybe I've missing something obvious in the code but when it comes to my admittedly very specific scenario, it's is really awkward to get your product to work well.

Any ideas or thoughts on providing an extensibility point to make this possible?

[MariiaI]

I can never know the specific schema I am targeting for non shared data until runtime as it is based on information associated with a ERP user.
...
How can I set the schema per table at runtime based on some business logic per user?

You can try using fluent mapping approach for this. Please refer to this topic in the LinqConnect documentation:
http://www.devart.com/linqconnect/docs/ ... pping.html

If you'll encounter any difficulties with this or have any questions, feel free to contact us.