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

Question

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.

Answer 1

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