Struggling to get TableSchemaModelAdapter Working

Jul 14, 2010 at 3:57 PM
Hi, Background... I have a SQL Server db that separates users' data by schema. For instance, User1 has the default schema User1 and User2 has the default schema User2. The database contains multiple Clients tables - one for every user/schema e.g. User1.Clients User2.Clients ... User[n].Clients Prior to EF4 our SPROCs did not have to mention schemas specifically as the default of the logged-in user would be used. For example, "SELECT * FROM Clients" would get the correct data dependant on the logged in user. I have created a simple EDM and changed it inline with your documentation... public partial class MyEntities : AdaptingObjectContext { public MyEntities(string connectionString, string schema) : base(connectionString, "MyEntities", new ConnectionAdapter(new TableSchemaModelAdapter(schema), System.Reflection.Assembly.GetCallingAssembly())) { ContextOptions.LazyLoadingEnabled = true; OnContextCreated(); } } This continues to work if the supplied connectionString / schema matches that used to create the initial EDM, but fails if a different user / schema is used. Here is an example of the exception (I had passed 'User2' as the schema param on the _ctor)... "System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'Clients', database 'MyDb', schema 'User1'. Any help much appreciated! Chris (@GoodCoffeeCode)
Oct 18, 2010 at 1:01 PM

It's because the metadata is stored in a static member of the connection adapter. Change the static member in an instance member and you're good to go.