asp.net core - How to query data from different database server in asp net microservices?

Question

I would like your feedbacks and inputs on how to query data from different database server in a micro services pattern. I have been struggling for a couple days and could not come up with an efficient way yet.

Here is my scenario: For simplicity, let's say that I have 3 micro services:

• One is handling the User Posts
• One is handling the User Room Chats
• An API Gateway

Now, I need to get all user posts and room chats and display it into front end based on Creation Date, but I don't want to display them all at once. I want to have a pagination system implemented on front end as well.

What I have:

• Query to get all user posts based on a user Id with pagination order by creation date (page size and page number in the stored proc)
• Query to get all user room chats based on user Id with pagination order by creation date (page size and page number in stored proc)

What I come up with:

On the front end:
    1. Request is constructed with a fixed value of page size and page number. It starts with page size = 1 and page number = 15
    2. When user scroll down, the request page size will increase to 1    
On the back end:
    1. Execute 2 queries, one to the user post microservices, and the other one to the user room chats, each one is fixed with a page size of 1 and a big value of page number, like 1000
    2. Get result lists from both queries, then use Automapper to map them to a list of big combination object
    3. Once I have the list of big combination object, sort it based on creation date, then do pagination based on the page size and page number that is passed from the front end request
    4. Return result from step 3 to front end

I don't like this implementation, since I have to use a fixed value for page size when executing the stored procs. My concern is that if I have a lot of user posts and rooms, it would have big impact on my performance. However, I can't figure out a better way to do this.

FYI, I'm using ASP NET Core for back end, Angular for front end and MS SQL for my database.

I would really appreciated if someone could give me some hints/inputs on how to do that ? or if you have run into this scenario in your real life project, how would you solve this ? Thank you.

Answer 1

等待大神答复