You can never know how users plan to use your code. In your proposal, if a caller first looks up
HasNotes and only later
Notes, you'll have one SQL call too many as well: first the COUNT, and then the entire SELECT to populate
If you do know that users will never call
Notes, then your implementation make sense, but in that case, I'd suggest going all the way in that direction.
For instance, instead of trying to be smart about queries by guessing usage patterns in a model, you could make an explicit data access layer that performs exactly the right queries that are needed, and populates the model classes. The model classes are then reduced to simple data-holder classes. They might contain domain logic and validation logic and all that, but certainly no knowledge of the DB.
This is really the ORM vs DAO debate. Fowler has a nice short discussion of this debate here.
In general, I'd choose a clear approach:
- Try to be as automatically-smart as possible in the model classes. This is the NHibernate way. It yields an easy-to-use model layer that generates just a few queries too many. In that case, leave the smart stuff up to NHibernate and don't try to optimize your model classes prematurely.
- Don't even try to be smart, but leave it to the "user" to call the right methods that populate the model.
I feel that the approach you suggest is somewhere in the middle ("It's an optimization, but only if you call things this and this way"). This will confuse other developers, and yourself in a few months. Better to take a clear stance.