We are developing a multitenant app using .net core and EntityFramework. There will be a single deployed instance of APIs but multiple DBS( one DB per user). So what's the best strategy change DB connection string as per-user context.
2
-
What kind of app? Desktop or web?Caius Jard– Caius Jard2020-04-08 18:56:59 +00:00Commented Apr 8, 2020 at 18:56
-
its an API @CaiusJardzahid rasool– zahid rasool2020-04-08 19:12:35 +00:00Commented Apr 8, 2020 at 19:12
Add a comment
|
2 Answers
Set the connection string in the OnConfiguring method of DbContext, by calling UseSqlServer, or whatever your database is. In this method, you need to have a reference to some service that will provide the user-specific connection. For this, IMO, the best way is to inject an IHttpContextAccessor through the DbContext class, and, inside OnConfiguring, use it to obtain the HttpContext and all the registered services. You must not forget to register IHttpContextAccessor by a call to AddHttpContextAccessor.
Sample code:
//MyContext
public class MyContext : DbContext
{
public MyContext(DbContextOptions options, IHttpContextAccessor httpContextAccessor) : base(options)
{
this.HttpContextAccessor = httpContextAccessor;
}
protected IHttpContextAccessor HttpContextAccessor { get; }
protected override void OnConfiguring(DbContextOptionsBuilder builder)
{
var context = this.HttpContextAccessor.HttpContext;
var userService = context.RequestServices<IUserService>();
var user = context.User.Identity.Name;
var connectionString = userService.GetConnectionString(user);
builder.UseSqlServer(connectionString);
base.OnConfiguring(builder);
}
}
//Startup.ConfigureServices
services.AddHttpContextAccessor();
services.AddDbContext<MyContext>();
Note that IUserService is just some service that you must implement that will return the connection string for the given user.
Comments
I think you should implement something like is in this link https://learn.microsoft.com/en-us/ef/core/miscellaneous/cli/dbcontext-creation
or like this example
using System.Configuration;
using System.Data.SqlClient;
using System.Threading.Tasks;
public interface IDbContextFactory
{
Task<TContext> Create<TContext>(int userId);
}
public class DbContextFactory : IDbContextFactory
{
public DbContextFactory()
{
}
public async Task<TContext> Create<TContext>(int userId)
where TContext: DbContext, new()
{
//TODO: build connectionString using SqlConnectionStringBuilder and replace the values in []
var connectionStringBuilder = new SqlConnectionStringBuilder("Application Name=SSCommitmentService;")
{
DataSource = $"tcp:[dbServer],1433",
InitialCatalog = "[catalog]",
UserID = "[sqlUserid]",
Password = "[sqlPassword]"
};
var options = new DbContextOptionsBuilder<TContext>();
//TODO: configure the DbContextOptions to use the Provider (Sql, MySql, etc)
options.UseSqlServer(connectionStringBuilder.ConnectionString);
//TODO: instantiate the required dbcontext
var dbContext = new TContext(options.Options);
return dbContext;
}
}
