ActiveRecord is the answer
In my last post I pondered how a ORM and Sprocs fit in, for me a database that is in at least 4nf and has 1 to many relations with the data is going to need joins in it's queries and its really the issue of the mechs of a decent TSQL sproc that was chewing me the most (queries with joins, in's etc).
When I mentioned this to a friend we started discussing Rails and ActiveRecord and it was with the edition of AR on-top of a ORM that it all become clear. With AR you have a high level of obstraction to define how your ORM mapped objects relate. The AR and ORM create the queries and joins for you, an example here and another example here (as a side here's an interesting post on why AR in Rails matches, or not, to CF). Going back to the .NET world the only AR\ORM implementation I know of is the mighty Castle project's ActiveRecord which maps a AR pattern to the NHiberinate ORM. Not sure if you can do the same sort of thing in terms of joins, in's etc with Castle as you can with Rails, prehaps the Castle guys could comment.