CODEWITHSUNDEEP
c#asp.net-mvcentity-framework
loyalflow
loyalflow

Reputation: 14879

How to change the schema on a dynamic per query basis?

I want to be able to pass the name of the schema to all of my data layer methods, and somehow have Entity Framework change the schema on a per query basis.

Is this possible?

public class UserRepository : GenericRepository<....>
{


    public List<User> GetUsersByLocation(string schema, int locationId)
    {
        ....
    }
}

On a per call basis I want to be able to change the schema for the EF query.

Upvotes: 6

Views: 2350

Answers (7)

Fboisde
Fboisde

Reputation: 1

I think it shall be possible by overriding the IDbCommandInterceptor, as suggested in a previous post. However to make it work, you need to execute the query at the ReaderExecuting stage and provide the result to the interceptionContext. Then the ReaderExecuted won't be called as explained here: MSDN - Logging and Intercepting Database Operations, in the "Suppressing execution" section.

So I guess that the concrete ReaderExecuting shall be changed to:

public void ReaderExecuting(DbCommand command,
                            DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
    if (!string.IsNullOrEmpty(_schemaName))

    {
        command.CommandText = command.CommandText
                                     .Replace("[dbo].", _schemaName);
        interceptionContext.Result = command.ExecuteReader();
    }
}

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109117

As of version 6, Entity Framework offers an easily accessible API to intercept SQL commands. You could use this interface to change the schema name in the command text on the fly.

First, you need a class that implements System.Data.Entity.Infrastructure.Interception.IDbCommandInterceptor. The interface contains a number of methods that clearly show (by name) at which point they intercept the command execution. In you case only one (or a few) of these methods is interesting:

public sealed class ChangeSchemaNameCommandInterceptor : IDbCommandInterceptor
{
    private readonly string _schemaName;

    public ChangeSchemaNameCommandInterceptor(string schemaName)
    {
        _schemaName = "[" + schemaName + "].";
    }

    public void ReaderExecuting(DbCommand command, 
           DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if (!string.IsNullOrEmpty(_schemaName))
            command.CommandText = command.CommandText
                                         .Replace("[dbo].", _schemaName);
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    { }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    { }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    { }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    { }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    { }
}

As you see, the constructor has an argument by which you can set the schema name. The interceptor simply replaces the ubiquitous "dbo" schema name by the specified name just before the SQL command is executed. (Maybe you want to do this in the other "executing" methods as well).

Now you can plug in the interceptor whenever necessary:

public List<User> GetUsersByLocation(string schema, int locationId)
{
    var interceptor = new ChangeSchemaNameCommandInterceptor(schema);
    try
    {
        DbInterception.Add(interceptor);
        return .... // (your EF LINQ query)
    }
    finally
    {
        DbInterception.Remove(interceptor);
    }

}

I don't consider this a very clean solution, but at least it allows you to leave the rest of the code relatively unchanged.

Upvotes: 2

B2K
B2K

Reputation: 2611

In trying to answer your question, I ran across this blog post. Scott Gu -- Custom Schema Mapping

And this post discusses how to change the name of the database schema in OnModelCreating. Working with Schema Names

Hope that points you in the right direction.

Upvotes: 0

Nikhil K S
Nikhil K S

Reputation: 804

If you want SQL DML Query for Multitenant shared schema Data Base as a per query basis then

You have to do the following things

  1. When creating a schema, create a user and set its default schema as this schema
  2. Store all schema and username and password in dbo table as master

if you have all the above data then you can use the same query to get data from server by changing the connectionString as required db username and password

Upvotes: 6

mmilleruva
mmilleruva

Reputation: 2178

You can do this by creating a connection string for each of the schemas in your app.config and naming them based on the schema.

<connectionStrings>
  <add name="schema1" connectionString="Data Source=xxxx;InitialCatalog=schema1;Persist Security Info=True;User ID=xxxx;Password=xxx;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>
  <add name="schema2" connectionString="Data Source=xxxx;InitialCatalog=schema2;Persist Security Info=True;User ID=xxxx;Password=xxx;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

Then you could create a new constructor that passes in the schema like the following:

public class MyDbContext : DbContext
    public ScipCcEntities(string schema)
        : base("Name=" + schema)
    {
    }

If you have lots of schemas you may want to use a connection string builder and build the connection string on the fly: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.initialcatalog(v=vs.110).aspx

Your call in the application would then look like this:

 public List<User> GetUsersByLocation(string schema, int locationId)
 {
    using(var ctx = new MyDbContext(schema))
    {
       // query the database ...
    }
 }

Upvotes: 0

Brian R. Mullin
Brian R. Mullin

Reputation: 423

If you can create your repository with a schema parameter in the constructor, then you could switch to a new database context on the fly.

The EF data context will have a constructor overload with the nameOrConnectionString parameter. If your "schema" parameter can be used in that way, then you could detect your schema context within your methods and reconnect to the other schema before issuing the query.

public class UserRepository : GenericRepository<...>
{
  private string _Schema;

  public UserRepository(string schema) : base(schema)
  {
    _Schema = schema;
  }

  public List<User> GetUsersByLocation(string schema, int locationId)
  {
    if (schema != _Schema)
    {
      return (new UserRepository(schema)).GetUsersByLocation(schema, locationid);
    }

    // query the database ...

  }
}

A more comprehensive solution would involve a redesigned repository to reduce the number of instantiations for the UserRepository class.

Upvotes: 0

TomTom
TomTom

Reputation: 62101

No, not possible. SImple like that. EF considers the schema layout to be static, like pretty much every ORM out there. Sorry for the negative answer, but it just is not possible. You could possibly do something when compiling the model (by changing it in the xml or dynamically in the attributes etc.) but no on a per query basis.

Upvotes: 1

Related Questions