0

I have a SAAS application which handle a multi tenant functionality.

I am planning to use different schema for each client.

My tables are created in .NET Core 3.1 using Entity Framework Core with a code-first approach and default schema dbo.

Now, whenever a client is created through the application, I want to create a same copy of dbo schema tables structure to another schema

For example: consider I have a table called dbo.Product, when Client A is registered, I need to create a table with ClientA.Product

How can I handle dynamic creation of tables in .NET Core 3.1 / Entity Framework Core?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sooraj Bathery
  • 135
  • 3
  • 10
  • 6
    That approach will create many problems and solve few. It's generally a better practice, and easier, to have a separate database per tenant. – David Browne - Microsoft Jul 26 '20 at 17:32
  • You can consider to add a field for each table where store the customerId (like dynamics ax), and split SQL table to different files. – Max Jul 26 '20 at 17:53
  • @DavidBrowne-Microsoft ,I have a micro service based architecture , we are planning to host in some cloud platform , If I use separate database for each tenant , It will end up with huge number of database – Sooraj Bathery Jul 26 '20 at 18:06
  • @Max Since It's a cloud based SAAS application, It's very difficult to manage data backups and data a analysis for each client if store tenantid in all my tables – Sooraj Bathery Jul 26 '20 at 18:09
  • 2
    On a cloud platform having a "huge number of databases" is typically much more flexible and useful, and even cheaper than what you're proposing. A SQL Server database is a unit of backup, HA, scale, security, versioning, deployment etc. And is a natural fit for separating tenants data. See eg: https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview – David Browne - Microsoft Jul 26 '20 at 18:12
  • @DavidBrowne-Microsoft I agree with you, but do we have any way to copy table structure from one schema to another schema ? – Sooraj Bathery Jul 26 '20 at 18:41
  • Start here: https://stackoverflow.com/questions/41979215/how-to-implement-imodelcachekeyfactory-in-ef-core and here https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/history-table – David Browne - Microsoft Jul 26 '20 at 18:57

0 Answers0