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

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.