What is the best way to encapsulate all your database functionality (e.g. a database API) behind a C# ASP.NET Core Web API that can be used by multiple (mainly) backend and frontend client services processing huge amounts of data?
Imagine 10 windows services (half are not .Net) all making and saving changes to data in a central master database, and a MVC app monitoring it. It is not possible to compile a common library into all of them, it needs to be a callable service.
Considering httpClient calls are mainly asynchronous, using httpClient you cannot guarantee if one client sends an async update, and another client sends an async read for the same data, whether the values returned will be from before, or after, the update. It fails ACID on consistency.
The other aspect that async is giving me doubts over is having async calls inside transactions that would be coming from business layers of calling client services.
Ive spent a good amount of time researching httpClient, async and sync, CQRS pattern, etc. and so far, not found any discussion or architectural solutions relating to having a "database API" type of service, as yet. Does anyone know of, or can point me to, possible solutions to this? (ideally without needing to use third-party components)