1

I'm using EF6 (Code First) to connect to a multi-tenanted database where each client has their own set of tables prefixed with a different schema name. Depending on who logs into the website, I change the connection string to a different user, who in turn has a default schema. Using this, I was hoping that if I had two tables - client1.Person and client2.Person - then I would just query the table 'Person' and the appropriate table would be queried depending on the login.

So, my first problem in EF6 was that all queries were prefixing the tables with 'dbo' - ie. 'dbo.Person'. No problem, I called modelBuilder.HasDefaultSchema("") to remove the schema name from the queries.

Now, I think this worked, but now the generated SQL prefixes the tables with 'CODEFIRSTDATABASE' - ie. 'CODEFIRSTDATABASE.Person'.

I am vaguely interested in what/why this is happening, but more importantly I simply want EF to query the database with NO schema information prepended - something that I thought would be a no-brainer.

BTW - I would also be happy to dynamically change the schema name when I open the connection HOWEVER, I should add that as well as the client-specific tables there are general 'system' tables which are shared amongst clients. In my code-first model, I specifically set those schema names using...

modelBuilder.Entity<SystemLog>().ToTable("SystemLog", "system");

...and obviously I don't want those changing dynamically.

Cheers,

Ben
  • 77
  • 11
  • I haven't had an answer for this, but I did find a workaround, which is to use a different instance of the context for each schema name. Refer [to this SO post](http://stackoverflow.com/questions/19458943/multi-tenant-with-code-first-ef6). However, I would still like the Entity Framework to be able to take an empty schema name and allow the current user (via the connection string) to dictate the schema (via their default schema in SQL Server). I wonder - perhaps EF can't do this because it would invalidate its query caching etc? – Ben Jul 03 '15 at 01:32
  • Unfortunately EF will always specify a schema name, so there isn't really a way to just have it use the schema from the connection. You could use a command interceptor to remove/change it, but you would be doing string manipulation on the raw SQL, which would be messy. Your approach of using a different context is the way to do this at the moment. – Rowan Miller Jul 07 '15 at 17:36

1 Answers1

0

No way with EF for now.

Use EF just for creating models automatically by "Update model from database" option(from edmx file on your vs project) and use them while working in your business&data layer on server side application.

Then: Create Stored procedures which get table name as a parameter to access for all of your tables. By this way you can make CRUD actions for all prefixed tables(including will be generated ones) more generic.

Community
  • 1
  • 1
Fuat
  • 789
  • 9
  • 14