I like the simplicity of the Simple Repository. This looks ideal for simple CRUD operations.
However, if I have a requirement for a complex query on top and ideally want my application to call a stored procedure, what is the recommended way to do this?
Does ActiveRecord cater for stored procedures?
I will be using this in an ASP.NET MVC application and am really looking for the easiest (to implement) solution that still offers me some flexibility/control in certain situations (e.g., use a stored procedure when I want/need to).
I am aware of LINQ to SQL, Entity Framework and NHibernate, but I would prefer SubSonic.
You should go with ActiveRecord for now as it's the most forgiving. We use it on Tekpub, and there isn't a reason it can't "scale" with you. Also, if you run into customization needs, you can change your templates, not our source (which was my design goal).
You can also use all the other SubSonic stuff, such as our simple query, batch query, and sugar library. They all play nice together, so you're not locking yourself off.
Have a read on our documentation site and if you have a question, you can mail us on our group (subsonicproject@googlegroups).