I just started with ASP.NET MVC 1.0. I've read through some tutorials but I don't have any good ideas on how to build my model.
I've been experimenting with LINQ to SQL. Can't say I like it that much, but I'll give it a try. I prefer using SQL Stored Procedures, but it doesn't seem to work very good together with the optional parameters in my stored procedures.
The project is a prototype, but I would like to start with a good design right away.
The database will probably contain around 50 tables and stored procedures will be used for all queries and updates. Some procedures will also return multiple results. How would the best way to design this model?
All and any ideas are welcome. Should I even use LINQ to SQL? Should I design my stored procedures to only return one result? I feel a little bit lost.
You can use stored procedures with LINQ. You simply need to replace the autogenerated statements on your entities with your stored procs. Look at the properties for the entity (table) in the designer to change these. Your stored procedures won't be able to return multiple results as they need to map onto a collection of either one of your entities or an autogenerated entity, i.e, onto a collection of a single class.
Having said all that, I'd give it a go without using stored procedures. The LINQ code is fully parameterized so you already have this benefit. You'll find that LINQ allows you to easily build up queries alleviating your need for the optional parameters in your stored procs and, likely, resulting in more efficient queries as you don't need to build extra logic into your query to handle the optional parameters. Once you start using LINQ I think you'll find that the power and expressiveness in the code will make you forget about using your stored procedures.
You may still have a need for stored procs or table-valued functions for complex queries -- that's ok. If they map onto an existing entity, you can simply drag the proc/function onto the entity in the designer and you get a nice method on your data context that runs your SQL and returns collections of that entity. If not, I often create a view for the proc/function schema, use that as an entity, and attach the proc/function to it.