In my company, we have a system with the following structure:

1. Moldable hosts: servers are defined by their IP addresses

2. Moldable databases: each database is created based on a country code

3. Company-specific tables: each company has its own set of tables (the same structure for all companies), which are named using the company code. Some table names also include the year

Currently, we use dynamic stored procedures to query a SQL Server database. The process involves using a centralized COMPANY table (shared among all companies) to retrieve the necessary server, database, company code, and year information. This is then used to construct a dynamic SQL query.

Here’s an example of our current approach:

USE dbUnified
GO
DECLARE
     @SQL NVARCHAR(MAX) = ''
    ,@idCompany INT
    ,@serverERP VARCHAR(50)
    ,@baseERP VARCHAR(50)
    ,@codeERP VARCHAR(50)
    ,@yearERP VARCHAR(50)

SELECT 
     @serverERP = C.serverERP 
    ,@baseERP = C.baseERP
    ,@codeERP = C.codeERP
    ,@yearERP = C.yearERP
FROM
    COMPANY C
WHERE
    idCompany = @idCompany

-- Dynamic SQL for tables without year
SET @SQL = '
    SELECT 
        * 
    FROM 
        ['+@serverERP+'].['+@baseERP+'].[dbo].[TABLENAME'+@codeERP+'00]
'

-- Dynamic SQL for tables with year
SET @SQL = '
    SELECT 
        * 
    FROM 
        ['+@serverERP+'].['+@baseERP+'].[dbo].[TABLENAME'+@codeERP+@yearERP+']
'

EXEC SP_ExecuteSQL @SQL

We are now transitioning to Entity Framework Core, and I’m unsure how to replicate this dynamic behavior using C#.

Here are the challenges I’m facing:

1. Dynamic table and column mapping
   The database table names are not intuitive. For example:

   • Table names: PL03{CompanyCode}00 or SupplyerManager{CompanyCode}{YearERP}.
   • Column names: PL03001, PL03002, etc.

   I’d like to map these table and column names to meaningful names in C#. For example:

   • Table PL03{CompanyCode}00 → Invoice
   • Column PL03001 → supplierCode, PL03002 → invoiceNumber, etc.

2. Dynamic database selection
   My current DbContext only interacts with fixed tables (e.g., dbUnified). I need to dynamically connect to other databases and tables programmatically.

3. Expected Usage
   I’d like a single DbContext that allows me to query data dynamically, like this:

   Plain text

   Copy to clipboard

   Open code in new window

   EnlighterJS 3 Syntax Highlighter

   <code>companyInfoDbContext.Invoice.FirstOrDefault(x => x.supplierCode == "123" && x.invoiceNumber == "321");

   // or

   companyInfoDbContext.Supplier.FirstOrDefault(x => x.supplierCode == "123");

   </code>

   <code>companyInfoDbContext.Invoice.FirstOrDefault(x => x.supplierCode == "123" && x.invoiceNumber == "321"); // or companyInfoDbContext.Supplier.FirstOrDefault(x => x.supplierCode == "123"); </code>

   companyInfoDbContext.Invoice.FirstOrDefault(x => x.supplierCode == "123" && x.invoiceNumber == "321");
   // or
   companyInfoDbContext.Supplier.FirstOrDefault(x => x.supplierCode == "123");
   

What I’ve tried so far

• I’ve created a DbContext for dbUnified, which works fine for static tables

• However, I haven’t figured out how to handle multiple databases programmatically

Question

How can I implement a solution in EF Core that allows:

1. Dynamic database and table selection.
2. Mapping table/column names to meaningful C# models and properties.

Any guidance or examples on how to achieve this would be greatly appreciated!