2

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.

3

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.

3

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;
    }
}
  • I added some clarification to the question. Basically, I'm making one trip no matter what. I just can't decide the best way to determine what exactly that trip contains. – Michael Brandon Morris Oct 2 '18 at 4:01
  • 2
    You need more meaningful names for your aggregates than DivisionWithSportAndTeamTypeAndSkillLevels. That's the name of a query, not an aggregate. Surely that has an actual name corresponding to an actual business artifact. If it doesn't, will relaxing your rules so that you no longer have to make only one trip "no matter what" allow you to come up with more meaningful names for your aggregate objects? Note that this isn't merely semantics; the lack of meaningful names suggests deeper problems with your design. – Robert Harvey Oct 2 '18 at 4:53
  • 1
    should I use different code for these different queries, or just grab everything and discard/not use the excess? -- That is indeed the question you need to answer. One way you could possibly solve that is by delaying some queries until they are actually compelled: see my answer update. – Robert Harvey Oct 2 '18 at 4:59
  • Generally I take the resulting entities and pass them into models in my controllers. Would you advise having the repositories return the models themselves instead of entities? Currently, I have generic DisplayModels and EditModels for every view, so this approach would likely require replacing those with an entity-per-view. Is that a preferred approach? – Michael Brandon Morris Oct 2 '18 at 6:01
  • 1
    That depends on what you mean by "preferred." I wouldn't get too hung up on the terminology; the goal here is to package up your data bundle, whatever that is (an invoice, for example) as a collection of entities in a new class, and then include the logic for retrieving the necessary entities in that class itself. You can call that whatever you want, but I'm basically describing a View Model. – Robert Harvey Oct 2 '18 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.