Question about data access with LINQ and Stored Procs (NOT asking which is better!)

Question

First, I am NOT trying to spur yet another debate about LINQ vs. stored procedures.

Assume for this question (for right or wrong) that I'm going to use SQL server stored procedures and will access those stored procedures via LINQ. I am using stored procedures (again, for right or wrong) because I want to enforce security at the stored procedure level vs. on the underlying tables and views. I am using LINQ (once again, for right or wrong) because I want to learn it.

1. Given the above, my LINQ queries should be relatively simple SELECT statements (obviously just referring to reading data in this question) rather than LINQ queries that contain groupings or calculations or generally other more complex things. This assumption is based on my plan to put this logic in T-SQL. In other words, my LINQ queries will be relatively "dumb". In addition, given my desire to enforce security at the stored procedure level and not allow access to the base tables, I see this approach as being consistent with that goal.

   Are there any flaws in my logic in #1?

2. If I were to use LINQ directly against the base tables, I'd obviously have to enforce security directly on those base tables. This seems obvious but I wanted to confirm.

   Any flows in my logic in #2?

Answer 1

LINQ on its own is quite generic, as there is linq-to-objects, linq-to-xml, linq-to-sql, linq-to-EF etc. I guess you're likely going after the Linq-to-Sql functionality (as opposed to, say , Linq-to-EF). By doing that all your sets are provided by stored procedure and interrogate these sets in the application with Linq I would say that you will teach yourself a very skewed way of leveraging the power of Linq in general, and Linq-to-Sql in particular. You will miss much of the know-how that goes into understanding how Linq-to-Sql generates the SQL queries sent to the server, because you'll be able to do only the very basic 'dumb' queries as you say. Eg. you won't even be able to do a join in Linq-to-Sql. And you'll miss the opportunity to properly understand the ORM capabilities of Linq-to-Sql, the caching that goes into DataContexts and the ActiveRecord behavior that allows you to insert/update/delete items from sets.

Although I do not advocate against doing what you're doing (it is a very valid approach to leverage stored procedures and linq together), I would say that is a bad approach for learning Linq. Try getting your feet wet with a straight approach, the kind of data-modeling in Visual Studio with a .dbml file approach advocated by Linq evangelists in 2008. While I reckon that this approach is flawed for deploying large, viable, projects, it is though very good to teaching yourself Linq and Linq-to-Sql in particular. Once you understand how things work, you'll be able to understand how to properly leverage this with a stored procedures for separation of access control (an approach always praised by SQL Server evangelists) and how deal with the issues not solved by the .dbml modeling approach (specifically, the problem of database schema upgrades).

Some may say you should also perhaps keep an eye out for what Entity Framework has to offer, but if you are in the learning stage I whole heartily recommend Linq-to-Sql instead. It is less complex, it works, is sane, is well supported in the VS tool set, and you don't have to learn Entity-SQL...