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.
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:
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.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.