Martin Fowler's Patterns of Enterprise Application Architecture describes a number of ORM related paterns, including use of the Query Object, which is what I'd suggest.
Query objects let you follow the Single Responsibility principle, by separating the logic for each query into individually managed and maintained strategy objects. Either your controller can manage their use directly, or delegate that to a secondary controller or helper object.
Will you have a lot of them? Certainly. Can some be grouped into generic queries? Yes again.
Can you use dependency injection to create the objects from metadata? That's what most ORM tools do.
The best thing for you to do is just to start with the default MVC scheme - where controllers and actions are 'allocated' by URL convention, with the first part of the path determining the controller, and the rest of the URL (and the request type, e.g. GET or POST) determining the action that'll be called.
Whilst at first this seems like it must lead to a 'mess' the reality is that most websites don't actually require loads of controllers, and certainly don't require loads of actions.
Sometimes you might wish to do some clever stuff to split one controller into two, whilst keeping the URLs the same - e.g you might have:
~/Products
going to ProductsController.GetProducts()
~/Products/{id}
going to ProductsController.GetProduct(id)
But then, let's say you have reviews of products too, you might have
~/Products/{id}/Reviews
going to ProductReviewsController.GetReviews(id)
This could be legitimate since reviews functionality might require quite a lot of code, which isn't directly related to the products themselves.
That said, the motivation for splitting controllers shouldn't be to keep a code-file small (you can do that with partial classes), it should be to keep to a single-responsibility principle; that is - a controller should have responsiblity for single section of your site. The Users controller should not also be used for your homepage.
Don't spawn a new controller for everything you do, consider it based on the entity that the controller will operate on or provide details of (e.g. products, users etc). Thinking URL-first and then using that as the basis for new controllers is a very good starting point, because ultimately you want your site to be easily navigable by search engines as well as users.
The beauty of MVC, also, is that with the separation of controllers from the route patterns that trigger their actions, you can completely change the URL structure of all your existing code just by changing the routing. For that reason it's particularly important to remember always to use the UrlHelper
for generating URLs to other actions, and HtmlHelper
's ActionLink
and RouteLink
operations in views for generaing cross-links; these honour routing, and so take away a lot of the pain associated with re-structuring a website at the URL level.
Best Answer
I like the Ruby on Rails "RESTful routes" convention: each controller represents a resource (a noun), and there are 7 conventional actions:
Note that resources don't have to map directly to models/database tables - for e.g. password reset, you may "create" a password_reset resource to request a new password, and "update" it to set the new password, even though behind the scenes, it only modifies your user model.