Is it better to have a single query that returns all related entities, or separate queries for each case?

Question

Summary

When writing methods to query for related entities, is it better to have a single method for all related entities or separate methods for each combination?

Full Problem

I'm writing repository classes for a .NET MVC project where each repository is responsible for querying for a specific entity type. I'm using Dapper, so all of the queries are written by hand. The repositories all implement an IRepository<TEntity> interface, which has method TEntity FindById(id).

Fetching just the entity is obviously very straightforward, but I'm faced with a dilemma when it comes to fetching the entity's children and grandchildren and so forth. Often times, for an entity to be usable, it needs some or all of its descendants. I initially wrote FindById to return the entity with all descendants, which satisfies all use cases, but is also wasteful in many where I only need several descendants.

I wrote out individual methods for fetching various combinations. FindByIdWithChildA or FindByIdWithChildBAndGrandChildC and such. The problem here is that the method names keep getting longer and longer, which makes them a pain to read, and also wastes a lot of space in code.

My next idea was to accept various enum flag values designating which descendants to return. This worked fine for the immediate children, but by the time I got to grandchildren and beyond I had so many possible combinations to implement, and many of them didn't make sense or wouldn't be used.

By the time I have all this boilerplate written out, it almost feels like I'm writing a bad ORM instead of repositories. This has certainly slowed down development dramatically, and it has taken focus away from actually implementing new features.

The obvious wrong solution is to not use SQL joins and have the repositories splice together entities and collections of entities using LINQ. Using LINQ with EntityFramework could be an option, but I don't think that the solution is to switch to another library.

Should I just simplify and have the single method/query return everything? If not, what is the best way to concisely implement separate methods?

This answer indicated that a FindByIdWithChild type method is normal, but I can't imagine that being good practice for complex queries.

Clarification

So my entities are Sport, Division, SkillLevel, and TeamType. Each Sport has a list of Divisions, SkillLevels, and TeamTypes. Each Division has a single TeamType and a list of SkillLevels.

Suppose I want a Division with its parent Sport, its TeamType, and all of its SkillLevels. Should I do:

1. DivisionRepository.FindById(id) which returns all of these properties, ignoring any case where I want just a Division or a Division and Sport, Division and TeamType, etc.

2. DivisionRepository.FindById(id, BitFlagQueryScope) which has a big mess of an implementation to resolve all the possible flags, some of which might not be needed.

3. DivisionRepository.FindByIdWithSportAndTeamTypeAndSkillLevels(id) and have one of these methods for every combination I need.

In all three cases, I am making one trip to the database. It's just a matter of how I structure my code.

Additional example, suppose I want a Sport with just its TeamTypes. Elsewhere I will need a Sport with its SkillLevels, etc. Same questions as above. I'm only making one database trip, but should I use different code for these different queries, or just grab everything and discard/not use the excess?

Additional Clarification

In case there's something wrong with my design overall, here's how I'm using the entities:

Action(string id)
{
    var sport = SportRepository.FindById(id); // This needs to either return everything associated with the entity or be changed to allow specification
    var model = new SportDisplayModel(sport);
    return View(model);
}

I have two models, $Entity$DisplayModel, and $Entity$EditModel, and I use them for every view.

Answer 1

The way you reduce network latency is by "chunking" your queries; that is, by retrieving a larger chunk of data on each request. One way you can do that is by dealing with business domain aggregates instead of individual database records.

Instead of using generic method names like FindByIdWithChildBAndGrandChildC (which is just a more sophisticated form of a simple repository pattern), create business objects that have meaning to the business which chunk together individual records into an aggregate.

For example, instead of loading an InvoiceHeader, BillingAddress, ShippingAddress, List<LineItem> and so forth separately, write an Invoice object that can retrieves all of these records and expose the data as public properties. You can inject your needed IDBConnection into the object, and encapsulate all of the needed SQL and Dapper calls as required.

Finally, if lazy loading will help, you can simulate delayed queries (and caching) by doing this:

private MyEntity _myEntity;

public MyEntity MyEntity
{ 
    get
    {
        if (_myEntity == null)
           _myEntity = dbConnection.Query<MyEntity>(sql, parameters).FirstOrDefault();

        return _myEntity;
    }
}

Answer 2

If the server isn't located on the same physical machine as the application making the call, your design should try to minimize the total number of queries, especially small queries, because network latency can take up a disproportionate amount of time.

True story: I once turned a report-generating task that was taking over 24 hours to run into one that would run to completion in under 45 minutes and produce the same results, by doing nothing but cutting way down on the number of database hits. That's how significant the latency of database access can be.