Arian Kulp's Blog

I’m in the middle of a fairly large sized project right now and as a team we’ve elected to use LINQ to SQL across the board.  LINQ to SQL (L2S) is great to work with.  The class generation using the DBML file is easy to use and creates objects that are pretty much identical to what I’d create myself for business objects.  I can change properties of an object, or create a new object attached to a table collection, and then when I submit the changes it magically gets sent to the database.  It’s really great to work with.  At least when you can use ad-hoc queries…

The problem is that in this situation, we aren’t able to send *any* queries to the database.  Permissions for reading and writing data are denied – all we can do is call stored procedures.  Of course we can do anything that we need with these stored procedures, but this causes headaches with the L2S objects.

If you do some reading, you’ll learn that L2S fully supports stored procedures.  It’s not all that uncommon for table writes to be restricted, so you can set Create, Update, and Delete stored procedures for every table.  You can also associate arbitrary stored procedures that look become methods on the DataContext.

All of this allows you to use standard L2S syntax for modifying object properties and submitting the changes, or calling Remove() on an object in a collection to delete the underlying row.  Unfortunately, all Select operations still result in queries being generated.  Even things as simple as getting a count send a “Select count(*)…” query to the server.  As I said, we can’t send queries of any type, so this has become a real challenge.

There are so many places where queries get sent.  Even if you use a stored procedure to get an object (i.e. a manager), it will issue a select query to get associated objects (i.e. manager’s direct reports).  The query itself is great!  Really, it does a wonderful job of this, but I have to wonder why this is the only way it can work.

There is no concept of a default Select operation.  In my mind, if I could specify a get-by-id stored procedure for every table, much of the problem would be gone.  Whether I need a single record myself, or associated queries for foreign key relationships, if L2S knew how to get a record by its ID using a stored procedure we’d be in great shape.

Unfortunately, this isn’t possible right now.  I don’t know if it’s even on the horizon, but for now it’s a very tedious procedure to make sure that you don’t use LINQ much at all, except on cached query executions from the stored procedures (using ToList() on the results).  This takes out much of the benefit of L2S since you are forced to think it through so much.

To make it worse, sometimes you can’t even tell that it’s using queries unless you log all queries or use SQL Profiler.  What would be great is if you could tell it to use no dynamic queries (EnableDynamicQueries = false).  Then if the query generator senses a need to do so to satisfy an expression tree it could throw an exception.  Clearly, complex searches are more complicated and possibly couldn’t be done by dynamically determining the stored procedure to use, but as it is now you are forced to use the stored procedure methods explicitly.  To make all of this worse, using these methods causes you to lose the benefits of object tracking.  This means that subsequent queries from the same DataContext result in cached objects.  Clearly not ideal!

I’ll be at the MVP Summit in February and I’m hoping to meet up with some of the LINQ people.  Nothing new to them I’m sure, but worth bringing up anyway!