There is definitely an interesting problem at play here; but I think you're looking at it the wrong way.
Your question is, at some points, dangerously close to becoming an XY problem, as you seem to be indirectly asserting that caching (Y) is the solution to loading referenced data (X).
Some short feedback on why I'm not a fan of your suggested solutions, mostly just pointing out the inherent bottleneck/problem.
Variant 1 - I can use the DB view that list all referenced data and use some kind of convention mapping to restore the referenced data.
Not quite sure I follow; but are you suggesting to load all related data from the database and then pick the data you actually need afterwards? Because that's a severe waste of bandwidth.
Variant 2 - Within customer repository - I can fetch the referenced IDs and then use other repositories (country, company, status, ...) in order to assemble the referenced data by fetching the data from those other repositories. Note that this approach would cause multiple DB hits.
As you mentioned, multiple DB hits are a big problem here.
On the plus side, this is the cleanest approach purely from a development perspective. If data retrieval was an instant operation with no performance bottlenecks, this would be the best way to structure your code. Sadly, due to real life considerations related to the data retrieval method, we can't do this because it creates a massive bottleneck.
I would only consider variant 2 if you need absolute freedom in storing your entities, e.g. the ability to store them all in different ways/locations. When your data is stored in different locations, multiple hits are a logical inevitability, so it's no longer a problem that can be avoided.
Variant 3 - Same as second, but now repositories implement some kind of caching strategy (which complicates implementation overall) so that multiple DB hits are minimized.
Caching introduces more problems to the scenario.
You say that the repositories are considered read-only; but I assume this data can be changed by someone at some point in time. Because if it's never changed, I would consider not using an external database and instead having a local hardcoded file to use - it will lower maintenance costs and improve readability.
So if it's possible that this data changes at some point, application A's cache can become invalid if application B changes the underlying data - how is application A going to find out if this is the case, if not by calling the database, the exact operation you seek to avoid by having the cache in the first place?
We also get into minor issues such as memory usage - though you didn't quite mention how much data you're talking about.
If you're already considering caching the data locally, why not simply copy the entire database locally in an easy-to-parse format, and only refresh the cache at particular intervals? But again, this strongly relies on factors such as the projected size of the data, how often it tends to change, and how quickly you want your users to be aware of data changes.
Variant 4 - Use some ORM framework that can do this with minimal manual intervention/mapping - likely doable, but I am also STRONGLY INTERESTED IN NO-ORM ALTERNATIVES here.
I would suggest Entity Framework here, or use it as an example, but I'll refrain from doing so since you want an ORM-free solution.
What you need here is pre-emptive loading (more commonly referred to as "eager loading"). In other words, when you do the initial fetch, you should already know what related data you want to retrieve. This was, you can ensure that you only have to call the database once, have all the needed data in memory, do not risk any issues with data being outdated, and avoid loading unnecessary data.
No ORM can do this job for you. It's impossible to get what you want unless you specify exactly what you want. Unless you tell it to load everything, but that's not good either. ORMs may simplify the code, but the leg work (deciding which data should be loaded) will always be up to you.
However, and this is something that I've had to learn the hard way, repositories are an antipattern when you want to load multiple related entities at once.
The problem is that repositories tend to encapsulate each entity into a neat little box of their own, which is the stellar opposite of looking at the entities as a web of relations.
This is a discussion trap, we can argue about pro's and cons all day. So let me cut that short and give you a fair summary of what I think most people will agree with:
- Repositories are good for single-entity operations, e.g. CRUD operations on a particular entity type.
- Repositories are not built for multi-entity operations, e.g. retrieving a complex object graph.
For the latter, which I often shorthandedly refer to as "reports", what you really want is to create a new repository altogether, one that is tailored to retrieve the data the way you want it. For example:
- If you introduce the idea of a "customer record", i.e. a customer and all their related data; then you can also introduce a
CustomerRecordRepository, which inherently loads all data for the customer.
- Similarly, you can introduce a "customer location", i.e. a customer and their address; and the subsequent
CustomerLocationRepository.
- ...
In short, you need a unique repository for every unique composition of loaded entities, if you want to be able to retrieve all data in a single efficient query (= no unnecessary data, guaranteed up to date).
Note
I still call it a "repository", because it will probably live alongside your existing repositories. However, you could also simply make it a "query object". It's the same thing but with a different name. When you look at the core implementation, a query object is effectively the same as a single repository method.
If you "report" repositories commonly end up having a single method, it would be better to call them a query object, though the rest of the implementation remains the same.
Note 2
When I say "a unique repository for every unique composition of loaded entities", that not a given. You could put these methods (each with a different combination of retrieved entities) in a single repository (e.g. ReportRepository), but unless there are only a handful of methods, this will quickly grow to unmaintainable proportions.
I just want to add a minor mention here that Entity Framework can actually help you in this regard. The Include() statement, which exists specifically to load related entities, can easily be parametrized and thus allow for easily loading additional data without needing to create a near-duplicate method every time you want to load an additional related entity.
However, since no exposure of the underlying relational model is allowed, this means (at best) that you can make your internal repository code more reusable; but you'll still have to abstract the include logic since you don't wan't callers to have direct access to/knowledge about the relational model.
