0

I'm doing .net project upgradation from ADO.Net to Entity Framework DBContext.

There are multiple schema's in my SQL database, from SCHEMA 1 to 150 SCHEMA'S. Each schema has 12 tables with different data, but the table structure remains the same.

Existing code has multiple queries like below as well nested queries

SELECT COLUMN NAME FROM [SCHEMA].TABLE NAME ORDER BY 1; here [SCHEMA] will be dynamic

When I call Scaffold-DbContext "Server=;Database=;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DBModels

It generate single DBContext with multiple tables entity with respect of SCHEMA's.

I would need your suggestion. How I should generate Scaffold-DbContext so that I can query any tables by just passing SCHEMA'S dynamic.

Haribansh Gupta
  • 1
  • 2
  • Instead of using Database in connection string you can put in your query : "Use XYZ" where XYZ is the database. – jdweng Aug 22 '22 at 16:20
  • This is for MySql but I think it should work MSSql too: https://stackoverflow.com/questions/69816020/using-entityframework-core-how-can-i-dynamically-change-the-mysql-database-i-c – Poul Bak Aug 22 '22 at 16:20
  • @jdweng you mean like this ? - Scaffold-DbContext "Server=.;USE XXXX;Integrated Security=True" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DBModels . It complain use Keyword not supported – Haribansh Gupta Aug 22 '22 at 16:32
  • No. As a SQL statement : https://learn.microsoft.com/en-us/sql/t-sql/language-elements/use-transact-sql?force_isolation=true&view=sql-server-ver16. So try : Use XYZ; SELECT COLUMN NAME FROM [SCHEMA].TABLE NAME ORDER BY 1; Or string query = string.Format("Use {0}; SELECT COLUMN NAME FROM [SCHEMA].TABLE NAME ORDER BY 1;", databaseName); – jdweng Aug 22 '22 at 16:50
  • If you want to execute dynamic query using `raw-sql` you could define the `schema name` as well do that it will execute on perticular schema only. – Md Farid Uddin Kiron Aug 23 '22 at 09:49
  • @jdweng here challenge is - for each tables DBContext generate separate entity which is obvious but in this case I have DBLayer which have some queries where dynamically set SCMEA and inline queries are same. Like Above I have explained . So difficult to write queries. – Haribansh Gupta Aug 24 '22 at 14:14
  • @MdFaridUddinKiron, challenge is - for each tables DBContext generate separate entity which is obvious but in this case I have DBLayer which have some queries where dynamically set SCMEA and inline queries are same. Like Above I have explained . So difficult to write queries. – Haribansh Gupta Aug 24 '22 at 14:15
  • If that is the case, then please share your code which you couldn’t be able to make it workable. Hypothetically the scenario sometimes cannot be visualized, so share your exact code which you are struggling with. – Md Farid Uddin Kiron Aug 25 '22 at 08:39

0 Answers0