This is the situation:
Say I have an application in which two entity types exist:
Person has a reference to
Person.employer, which denotes the company a person is employed at.
In my application I am using repositories to separate the database operations from my business-model related services: I have a
PersonRepository.findOne(id) method to retrieve a
Person entity and a
CompanyRepository.findOne(id) method to retrieve a
Company. So far so good.
This is the dilemma:
Now if I make a call to
PersonRepository.findOne(id) to fetch a
Person entity, I also need to have a fully resolved
Company included inline via the
Person.employer property â€“ and this is where I am facing the dilemma of having two implementation options that are both suboptimal:
Option A) Redundant queries throughout my repositories but less database round trips:
PersonRepository I can build a query which selects the user and also selects the company in a single query â€“ however, the
select expression for the company is difficult and includes some joins in order to assemble the company correctly. The
CompanyRepository already contains this logic to
select the company and rewriting it in the
UserRepository is redundant. Hence, ideally I only want the
CompanyRepository to take care of the company selection logic in order to avoid having to code the same query expression redundantly in two repositories.
Option B): Separation of concerns without query-code redundancy but at the price of additional db roundtrips and repo-dependencies:
PersonRepository I could reference the
CompanyRepository to take care of fetching the
Company object and then I would add this entity to the
Person.employer property in the
PersonRepository. This way, I kept the logic to query the company encapsulated inside the
CompanyRepository by which a clean separation of concerns is achieved. The downside of this is that I make additional round trips to the database as two separate queries are executed by two repositories.
So generally speaking, what is the preferred way to deal with this dilemma?
Also, what is the preferred way to handle this situation in ASP.NET Core and EF Core?
Edit: To avoid opinion based answers I want to stress: I am not looking for a pros and cons of the two options presented above but rather striving for a solution that integrates the good parts of both options â€“ because maybe I am just on the wrong track here with my two listed options. I am also fine with an answer that explains why there is no such integrative solution, so I can sleep better and move on.
In order to retrieve a company by ID you need to read
Person's data, and fetch company ID from it. Hence if you would like to keep company-querying logic in a single place, you would end up with two round-trips - one to get company ID (along with whatever other attributes a
Person has) and one more to get the company itself.
You could reuse the code that makes a company from
DbDataReader, but the person+company query would presumably require joining to "forward" person's
companyId to the
Company query, so the text of these queries would have to be different.
You could have it both ways (one roundtrip, no repeated queries) if you move querying logic into stored procedures. This way your
person_sp would execute
company_sp, and return you all the relevant data. If necessary, your C# code would be able to harvest multi-part result set using
reader.NextResult(). Now the "hand-off" of the company ID would happen on RDBMS side, eliminating the second round-trip. However, this approach would require maintaining stored procedures on RDBMS side, effectively shipping some repository logic out of your C# code base.