Repository with Multiple Entities Creating a DTO

Question

Doing some C# code review here, and noticed a developer doing a join with 5 additional tables, and an inline IQueryable projection into a DTO. The tables being joined in the repository are User, Appointment, CommunicationPreference, UserProduct, and Product. I didn't design the system, and i'm probably simplifying it somewhat, but let's just go with this as is for now. Using Entity Framework and Web API.

This is a little long so I apologize, but really was curious to hear others' thoughts on this matter.

So these tables relate in that each User has a list of upcoming service appointments to service their products. The UserProduct table shows the relationship between a product and a user, and the product table shows the kinds of products. CommunicationPreference deals with how they want to be contacted about things like when their service is due, if someone is coming to service the product, how they want to be contacted (phone email text etc).

So the developer joins these tables together in the repository itself, and then creates a DTO called "UserServiceOverviewDto" that is returned to the controller and then the front end and basically has information to display in a grid to the customer representative (who the user is, upcoming appointments, what products, etc. basically everything I just wrote above).

I understand why they did this - instead of making 5 calls to 5 different repositories, they did it all in one, and then instead of having to convert each one to a DTO, they did it all in one shot. But I understand that a repository shouldn't return a DTO, either. And ideally your repository should return something that logically is aggregated and representative of one entity that solves your problem.

As I was thinking about this, I stumbled upon this in SO What type to return when querying multiple entities in Repository layer? but it doesn't really help me enough and wanted to ask for more advice on what to do.

Looking at this from a DDD perspective, I'm wondering what new "thing" needs to be introduced. Maybe I need to create a new context/repository that is this new thing. If i'm trying to say that this is a common grouping and give it a name like the "X" does this mean I want to create a new database table to represent that? Probably not, because I already have the tables that store this information.

Does this mean that instead I want to create a new class called "X" without a corresponding database table? So how is this any different from a DTO? In fact, it almost suggests that by making this class, i'm trying to make my repository more pure by instantiating X instead of XDto and then casting X to a DTO in a service layer anyway (I haven't gained anything other than shifting stuff around and creating a very anemic entity).

So in summary, then, the question is:

If I have multiple entities that I'm grouping together for convenience on the front end, is it better to:

1. Have the service layer make 5 distinct database calls, and assemble a DTO in this layer to return to the controller? Isn't this bad? 5 calls just to make my code seem more "pure"? Isn't it better to do your joins in one shot and get back what you need from the database?
2. Create a new class in C# that represents the marriage of these 5 tables, and then implement a mapping between this class and the corresponding DTO. This class would not be persisted as I said above.
3. Have the repository perform its joins to create a new DTO as an inline projection (seems wrong b/c repositories should return an entity not a DTO)

Really looking forward to some feedback here! Thanks!

Answer 1

This question is not specific to CQRS and is also valid outside of it, so I'll answer it as such.

The situation you describe occurs often when implementing statistics functions. E.g. to answer questions such as "how many users ordered this product, grouped by day?" This is sometimes called a Use case optimal query.

In these situations the second approach you suggest is usually the best. Create a new type that models the result of such an operation. Aggregate all data in the repository and return an instance of that type.

Solution #1 is often very inefficient, because the service layer is not able to use aggregation and grouping functionality that the DB offers. Solution #3 is a violation of the single responsibility principle and will sooner or later lead to maintainability issues.

Design considerations for the new type

• The returned data is always read-only, so the type can be modeled as value object.
• If the type has a meaning in the domain, put it in the domain layer. If not, put it in the persistence layer.