Fetch users list from 165 databases with 30 different DB schema using .NET CORE and EF

asp.net-core asp.net-web-api c# entity-framework entity-framework-core


I am building an API that will fetch users list from 165 databases with 30 different DB schema.

Some tables might have different names, like table User, it could be named User, Users, AspNetUsers, ApplicationUser.

Fields from certain tables might have different name even though they contain same information. like column UserId, it could be named userId, user_id, uid, id in different schema.

I want to get list of all users(C# Properties: UserId, UserName) from all databases, How can I reach this?

Currently, my app is only connected to one database with standard recommended configurations.

1/3/2020 2:58:13 AM

Popular Answer

To query "165 databases with 30 different DB schema", what you face first is performance. you need a revolutionary solution.

I had an experience to handle super-heavy query for a distributed application:

when we send a request to a API(Let's simply call it GetUsersCount()) , the API will simply send many sub-requests(it is "165" requests in your case) to other APIs with parallel tasks, then the API GetUsersCount() will concat all results to response. so the API GetUsersCount() doesn't do the any query, but concat results only. by this way, we reduced response time from 5-10 minutes to 1 seconds. so you need to add a layer(which should be another restful API) behind your API, which layer will only query one specific database with EF core.

But in your case, I wonder how big is your result? will you sort/group/paging result? but it can also be resolved by more detail designs with above idea.

But, if you have only several DBs(like, 2-3 DBs), I would suggest a Cross-database-query to union all several tables to one view with a Linked-server, and then use Keyless-entity-type(Query-type) to visibly operate result. 2 options:

  1. Create the view to your current connected database with EF Core code-first approach, then visit the view as a table. follow the referenced posts, your will reach it.
  2. Use trigger(if it's allowed) to synchnize the view-schame to one unique table of your connected database. this is the best when data is not huge and you have to group, sort, paging result.
1/3/2020 12:52:31 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow