With databases in many organisations, there’s a rule that’s carved in stone:
Thou shalt use Stored Procedures for all table access.
Regardless of your position on this, some clients just don’t give us any other option. The application is never given direct table access, and anything that doesn’t go through a stored procedure just generates errors. This could be because they’re enforcing access control on the data to a finer level than just table level, or because they need to ensure records are kept of all confidential information.
The picture for LINQ to SQL with this situation appears at first to be rather rosy, through two things:
- You can assign sprocs that handle inserts, updates, and deletes against your tables.
- You can directly execute sprocs as methods in your application. So, you can select data out of tables via this method.
Both of these things, are true. However, what doesn’t get mentioned is that LINQ to SQL will still perform reads (using dynamic SQL) from your database if you (for example) try to access children of a relationship, or insert/update rows**. That is: You can’t stop additional queries against the database.
So, as far as I can tell - this means LINQ to SQL, as it stands, cannot be used for these situations. Ofcourse, this doesn’t prevent me from using LINQ to XML, LINQ to Entities, etc - it just means that our ways of executing SQL still have not advanced.
There’s a possible way I think this could be fixed, but it really requires a bit of a rethink as to how LINQ to SQL works - perhaps this could be done as a third party LINQ provider, but really it should be done by Microsoft in LINQ to SQL v.Next.
The way I’d like to see it fixed, would be for someone to create an analysis tool that runs at publish time, that generates Stored Procedures for all the LINQ statements in your code, and then forces the compiled LINQ commands to map through these sprocs.
This would have the benefit of rapid prototyping/development, and being able to still enforce access through sprocs.
* = Well, there might be a way, but I’ve not yet found it.
** = It does select’s to get the updated column values from, for example, computed columns.
